A JOIN is a method of creating a result set that combines rows from two or
more tables (relations). When comparing the contents of two tables the following
conditions may occur:
- Every row in one relation has a match in the other relation.
- Relation R1 contains rows that have no match in relation R2.
- Relation R2 contains rows that have no match in relation R1.
INNER joins contain only matches. OUTER joins may contain mismatches as well.
This is sometimes known as a simple join. It returns all rows from
both tables where there is a match. If there are rows in R1 which do not have
matches in R2, those rows will not be listed. There are two possible ways
of specifying this type of join:
SELECT * FROM R1, R2 WHERE R1.r1_field = R2.r2_field;
SELECT * FROM R1 INNER JOIN R2 ON R1.field = R2.r2_field
If the fields to be matched have the same names in both tables then the
ON condition, as in:
ON R1.fieldname = R2.fieldname
ON (R1.field1 = R2.field1 AND R1.field2 = R2.field2)
can be replaced by the shorter USING condition, as in:
USING fieldname
USING (field1, field2)
A natural join is based on all columns in the two tables that have the same
name. It is semantically equivalent to an INNER JOIN or a LEFT JOIN with a
USING clause that names all columns that exist in both tables.
SELECT * FROM R1 NATURAL JOIN R2
The alternative is a keyed join which includes an ON or
USING condition.
Returns all the rows from R1 even if there are no matches in R2. If there are
no matches in R2 then the R2 values will be shown as null.
SELECT * FROM R1 LEFT [OUTER] JOIN R2 ON R1.field = R2.field
Returns all the rows from R2 even if there are no matches in R1. If there are
no matches in R1 then the R1 values will be shown as null.
SELECT * FROM R1 RIGHT [OUTER] JOIN R2 ON R1.field = R2.field
Returns all the rows from both tables even if there are no matches in one of
the tables. If there are no matches in one of the tables then its values will be
shown as null.
SELECT * FROM R1 FULL [OUTER] JOIN R2 ON R1.field = R2.field
This joins a table to itself. This table appears twice in the FROM clause and
is followed by table aliases that qualify column names in the join condition.
SELECT a.field1, b.field2 FROM R1 a, R1 b WHERE a.field = b.field
This type of join is rarely used as it does not have a join condition, so
every row of R1 is joined to every row of R2. For example, if both tables
contain 100 rows the result will be 10,000 rows. This is sometimes known as a
cartesian product and can be specified in either one of the following ways:
SELECT * FROM R1 CROSS JOIN R2
SELECT * FROM R1, R2
| 
