Database Management System

SQL JOINS

Join clause is used to combine rows from two or more tables based on common field(column) between them.

SQL JOINS are broadly categorized in two categories:

1. SQL EQUI JOIN
   • SQL OUTER JOIN
   • SQL INNER JOIN
2. SQL NON-EQUI JOIN

SQL EQUI JOIN

It is a simple SQL join that uses equal sign(=) as comparison operator for the condition(WHERE Clause). This joining operation returns the same result when we use the JOIN keyword with the ON clause and then specifying the column names and their associated tables.

Equi-Join Syntax :

    SELECT column_name (s)  
    FROM table_name1, table_name2, ...., table_nameN  
    WHERE table_name1.column_name = table_name2.column_name;  
OR
    SELECT (column_list | *)  
    FROM table_name1   
    JOIN table_name2   
    ON table_name1.column_name = table_name2.column_name;  

Points to remember for Equi-Join:

• There is no need to be the same column names.
• The resultant result can have repeated column names.
• We can also perform an equi-join operation on more than two tables.

NATURAL JOIN

The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only. It is similar to the INNER or LEFT JOIN, but we cannot use the ON or USING clause with natural join as we used in them.

Natural-Join Syntax :

SELECT * FROM table1 NATURAL JOIN table2;

Points to remember for Natural Join:

• There is no need to specify the column names to join.
• The resultant table always contains unique columns.
• It is possible to perform a natural join on more than two tables.
• Do not use the ON clause.
• The associated tables have one or more pairs of identically named columns.

SQL NON-EQUI JOIN

It is a SQL join that uses comparison operator other than equal sign(=) such as >, <, >=,<= for the condition(WHERE clause).

Cross Join

Can you join two unrelated tables in SQL? Yes, with Cross Join

CROSS join, which produces a cartesian product of two tables.(i.e 3 rows of table1x4rows of table2=12 rows returned).

Way to define the cross join

SELECT t1.column_1, t1.column_2, t2.column_1 FROM TABLE t1 CROSS JOIN TABLE t2

Differences

Natural Join vS Equi Join vS Inner Join