SQL Joins

1. Two basic joins: Equijoins & Nonequijoins

Nonequijoins:

SELECT table1.column, table2.column
FROM table1
[JOIN table2 ON (table1.expr1< table2.expr2)]|
[JOIN table2 ON (table1.expr1 > table2.expr2)]|
[JOIN table2 ON (table1.expr1 <= table2.expr2)]|
[JOIN table2 ON (table1.expr1 >= table2.expr2)]|
[JOIN table2 ON (table1.expr1 BETWEEN table2.expr2 AND table2.expr3)]|
[JOIN table2 ON (table1.expr1 LIKE table2. expr2)]

2. Natural Joins

The natural join is implemented using three possible join clauses that use the following keywords in different combinations: NATURAL JOIN, USING, and ON.

3. Cross Joins

If the source and target tables have three and four rows, respectively, a cross join between them results in (3 × 4 = 12) rows being returned.

4. The Syntax

SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 ON (table1.column_name = table2.column_name)] |
[LEFT | RIGHT | FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)] |
[CROSS JOIN table2];

5. Inner Versus Outer Joins

6. Left Outer Joins

SELECT table1.column, table2.column
FROM table1
LEFT OUTER JOIN table2
ON (table1.column = table2.column);

7. Right Outer Joins

SELECT table1.column, table2.column
FROM table1
RIGHT OUTER JOIN table2
ON (table1.column = table2.column);

8. Full Outer Joins

SELECT table1.column, table2.column
FROM table1
FULL OUTER JOIN table2
ON (table1.column = table2.column);