|The following join types are available:
[ INNNER ] JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
FULL [ OUTER ] JOIN
OUTER join types, a join condition must be specified. In other words, the join condition is one of
ON join_condition, or
USING (join_column [, ...] ). The following paragraphs describe the join types. For
CROSS JOIN, none of these clauses appear.
JOIN clause combines two
FROM items. You can use parentheses to determine the order of nesting. In the absence of parentheses,
JOIN clauses nest from left to right. The
JOIN clause binds tighter than the commas separating
CROSS JOIN and
INNER JOIN produce a simple Cartesian product. The result is the same as that of listing the two tables at the top level of
FROM, but is restricted by the join condition.
CROSS JOIN is equivalent to
INNER JOIN ON (TRUE). No rows are removed by qualification. The listed join types are for notational convenience. You can use the
WHERE clauses to perform all operations that you can perform by using join types.
LEFT OUTER JOIN returns all rows in the qualified Cartesian product. The qualified Cartesian product contain all combined rows that pass the join condition. LEFT OUTER JOIN also returns the left-side rows that do not have a matching right-side row. Each left-side row that does not have a matching right-side row is extended to the full width of the joined table by inserting null values for the right-side columns. Note that only the condition of the
JOIN clause is considered when whether rows have matches is decided. Then, outer conditions are applied.
RIGHT OUTER JOIN returns all the matching rows and the right-side rows that do not have a matching left-side row. Each right-side row is extended with null values on the left. This is a notational convenience. You can convert it to a
LEFT OUTER JOIN by switching the left and right inputs.
FULL OUTER JOIN returns all the matching rows, one row for each unmatched left-side row, and one row for each unmatched right-side row. The left-side row is extended with null values on the right. The right-side row is extended with null values on the left.