ANSI/ISO Join Syntax
In addition to performing joins using the approach described in the Joins section, ANSI syntax described in the 1992 ANSI/ISO SQL standard can be used. The ANSI syntax provides a more precise way of specifying joins that are otherwise identical to those produced from the traditional syntax. The ANSI syntax also allows the specification of outer joins.
An outer join returns not only the rows of the join sources that join together according to a specified search condition, but also rows from one or both sources that do not have a matching row in the other source. For rows included in the outer join that do not have a matching row from the other source, null values are returned in all columns of the other source.
An outer join is the union of two SELECT statements: the first query returns rows that fulfill the join condition and the second query returns nulls for rows that do not fulfill the join condition.
The ANSI syntax is specified in the FROM clause, as follows:
source join_type JOIN source ON search_condition
or
source join_type JOIN source USING (column {,column})
or
source CROSS JOIN source
where:
source
Specifies the table, view, or join where the data for the left or right side of the join originates.
join_type
Specifies the type of join as one of the following:
INNER
(Default) Specifies an inner join.
LEFT [OUTER]
Specifies a left outer join, which returns all values from the left source.
RIGHT [OUTER]
Specifies a right outer join, which returns all values from the right source.
FULL [OUTER]
Specifies a full outer join, which returns all values from both left and right sources.
Note: RIGHT and LEFT joins are the mirror image of each other: (table1 RIGHT JOIN table2) returns the same results as (table2 LEFT JOIN table1).
ON search_condition
Is a valid restriction, subject to the rules for the WHERE clause. The search_condition must not include aggregate functions or subselects. Matching pairs of rows in the join result are those that satisfy the search_condition.
USING (column {,column})
Is an alternate form of the search_condition. Each column in the USING clause must exist unambiguously in each join source. An ON search_condition is effectively generated in which the search_condition compares the columns of the USING clause from each join source.
CROSS JOIN
Is a cross product join of all rows of the join sources.
By default, joins are evaluated left to right. To override the default order of evaluation, use parentheses. A join source can itself be a join, and the results of joins can be joined with the results of other joins, as illustrated in the following pseudocode:
(A join B) join (C join D)
The placement of restrictions is important in obtaining correct results. For example:
A join B on cond1 and cond2
does not return the same results as:
A join B on cond1 where cond2
In the first example, the restriction determines which rows in the join result table are assigned null values; in the second example, the restriction determines which rows are omitted from the result table.
The following examples are identical and use an outer join in the FROM clause to display all employees along with the name of their department, if any. One uses the ON clause and the other uses an equivalent USING clause:
SELECT e.ename, d.dname FROM
(employees e LEFT JOIN departments d
ON e.dept = d.dept);
SELECT e.ename, d.dname FROM
(employees e LEFT JOIN departments d
USING (dept));
Cross Join Example
The following is an example of a cross join. A cross join returns a Cartesian product. Every row of one table is combined with every row of another table.
SELECT
e.last_name,
d.dept_name
FROM
emp e CROSS JOIN dept d;
last_name dept_name
Smith HR
Smith Sales
Smith Admin
Smith Support
Smith Services
Jones HR
Jones Sales
Jones Admin
Jones Support
Jones Services
Green HR
Green Sales
Green Admin
Green Support
Green Services
White HR
White Sales
White Admin
White Support
White Services
Mustard HR
Mustard Sales
Mustard Admin
Mustard Support
Mustard Services
Scarlet HR
Scarlet Sales
Scarlet Admin
Scarlet Support
Scarlet Services
The cross join is equivalent to joining tables and leaving off the WHERE clause.
SELECT
e.last_name,
d.dept_name
FROM
emp e,
dept d;