ANSI/ISO Join Syntax

In addition to performing joins using the approach described in the Joins section, new syntax introduced with the 1992 ANSI/ISO SQL standard can be used. The new syntax provides a more precise way of specifying joins that are otherwise identical to those produced from the old syntax. The new syntax also allows the specification of outer joins.

An outer join can be described as the union of two SELECT statements, where the first SELECT returns the rows of the join sources that join together according to a specified search condition; the second SELECT returns the rows from one or both sources that do not have a matching row in the other source. For rows that do not have a matching row from the other source, null values are returned in all columns of the other source.

The new 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

    Specifies an inner join.

  • LEFT

    Specifies a left outer join, which returns all values from the left source.

  • RIGHT

    Specifies a right outer join, which returns all values from the right source.

  • FULL

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

    Default: INNER

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

Permissions

You can select from tables in schemas owned by the effective user, group, and role of the session. To select rows from tables in schemas owned by other users, groups, and roles:

• The schema parameter must be specified.
• The effective user, group, or role of the session must have select permission.