SQL: Joins

JOIN is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used. There are various types of join which can be used to retrieve data and it depends on the relationship between tables.

• Inner Join
• Outer Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Self Join
• Cross Join

Inner Join:

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

Syntax:

1 2 3

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

or

1 2 3

SELECT * FROM table1 JOIN table2 ON table1.column_name=table2.column_name;

Right Join:

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

Syntax:

1 2 3

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;

or

1 2 3

SELECT column_name(s) FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name=table2.column_name;

Left Join:

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

Syntax:

1 2 3

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

or

1 2 3

SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name;

Full Join:

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Syntax:

1 2 3

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

Self Join:

A SELF JOIN is a join in which a table is joined with itself (which is also called Unary relationships), specially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.

Syntax:

[Here the table names have been given an aliases since they are basically the same table used twice]

1 2 3

SELECT a.column_name, b.column_name... FROM table a, table b WHERE a.common_filed = b.common_field;

Cross Join:

CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product. If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

Syntax:

1 2	SELECT * FROM table1 CROSS JOIN table2;

or

1 2	SELECT table1.column1, table2.column2... FROM table1, table2 [, table3 ]

Graphical representation of JOINs