The JOIN syntax is left-to-right, but parentheses can be
used to force an alternate evaluation. Each join combines two
relations (queryExpressions) to produce a third relation,
according to some plan.
The joinSpec identifies the plan
- natural joins
- [NATURAL] INNER JOIN
- outer joins
- [NATURAL] LEFT [OUTER] JOIN
- [NATURAL] RIGHT [OUTER] JOIN
- [NATURAL] FULL [OUTER] JOIN
- [NATURAL] UNION JOIN
- CROSS JOIN
natural JOINs
natural joins form a relationship based on a set of common
columns, as implied by the column names themselves, or as specified
in an ON or USING statement.
CROSS JOIN
This join plan produces the cartesian product of the two relations.
Each row of relation A is combined with each row of relation B. The
resulting size of the JOIN is size(A) * size(B).
select * from A cross join B is equivalent to
select a.*, b.* from a, b;
INNER JOIN
The INNER JOIN uses the "common columns" between the
two joined relation to select matching rows from each table for each
column value. Only matching rows (with non-null values in the
candidate columns) are returned in the result
LEFT and RIGHT joins
The LEFT JOIN includes all rows from the left relation,
together with the matching row from the right relation, if it exists
(as in an INNER JOIN), or nulls in the right-hand columns,
otherwise.
The RIGHT JOIN includes all rows from the right relation,
together with the matching row from the left relation, if it exists
(as in an INNER JOIN), or nulls in the left-hand columns,
otherwise.
FULL OUTER JOIN
The FULL OUTER JOIN is a combination of the
LEFT and RIGHT joins.
UNION JOIN
This join essentially performs a set operation, where we start
with the rows that would result from a FULL OUTER JOIN
of the relations, then subtract the rows from an INNER JOIN
of the same relations.
