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);