What is join in SQL ?

Joins are used to combine rows from two or more tables, based on a related column between them.

Types of Joins:

• INNER JOIN − Returns rows when there is a match in both tables.

• LEFT JOIN − Returns all rows from the left table, even if there are no matches in the right table.

• RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the left table.

• FULL OUTER JOIN − Returns rows when there is a match in one of the tables.

• SELF JOIN − Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

• CARTESIAN JOIN (CROSS JOIN) − Returns the Cartesian product of the sets of records from the two or more joined tables.

INNER JOIN:

The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate.

SYNTAX :

SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
INNER JOIN table2
ON table1.commonfield = table2.commonfield;

LEFT JOIN:

The LEFT JOIN returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

SYNTAX :

SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
LEFT JOIN table2
ON table1.commonfield = table2.commonfield;

RIGHT JOIN:

The RIGHT JOIN returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

SYNTAX :

SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
RIGHT JOIN table2
ON table1.commonfield = table2.commonfield;

FULL OUTER JOIN:

The FULL OUTER JOIN combines the results of both left and right outer joins. The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.

SYNTAX :

SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
Left JOIN table2
ON table1.commonfield = table2.commonfield;
Union
SELECT table1.col1, table2.col2,…, table1.coln
FROM table1
Right JOIN table2
ON table1.commonfield = table2.commonfield;

SELF JOIN:

The SELF JOIN joins a table to itself; temporarily renaming at least one table in the SQL statement.

SYNTAX:

SELECT a.col1, b.col2,..., a.coln
FROM table1 a, table1 b
WHERE a.commonfield = b.commonfield;