joined-table

The following list summarizes the result of the join operations:

• The result of T1 CROSS JOIN T2 consists of all possible pairings of their rows.
• The result of T1 INNER JOIN T2 consists of their paired rows where the join-condition is true.
• The result of T1 LEFT OUTER JOIN T2 consists of their paired rows where the join-condition is true and, for each unpaired row of T1, the concatenation of that row with the null row of T2. Null values are allowed in all columns derived from T2.
• The result of T1 RIGHT OUTER JOIN T2 consists of their paired rows where the join-condition is true and, for each unpaired row of T2, the concatenation of that row with the null row of T1. Null values are allowed in all columns derived from T1.
• The result of T1 FULL OUTER JOIN T2 consists of their paired rows and, for each unpaired row of T2, the concatenation of that row with the null row of T1 and, for each unpaired row of T1, the concatenation of that row with the null row of T2. Null values are allowed in all columns derived from T1 and T2.

A join condition is similar to a search condition, except that:

• It cannot include any dereference operations or the DEREF function, where the reference value is other than the object identifier column
• Any column referenced in an expression of the join-condition must be a column of one of the operand tables of the associated join (in the scope of the same joined-table clause)
• Any function referenced in an expression of the join-condition of a full outer join must be deterministic and have no external action
• It cannot include an XMLQUERY or XMLEXISTS expression

A join condition must comply with these rules (SQLSTATE 42972).

You influence which columns are included in the join result by specifying one of the following clauses:

ON <join-condition>

This clause can specify any qualification expression that involves the two tables that are to be joined. For example:

SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;

The join result contains all columns of t1 followed by all columns of t2.

USING <column-list>

This clause joins the tables on the specified columns. Each column exists in both of the tables to be joined. The tables are joined where the value in a column is the same in both tables. For example:

SELECT * FROM t1 JOIN t2 USING (c1, c2);

The columns of the join result depend on the join type:

For an inner join or left outer join

The join result contains the join columns from the t1, followed by the non-join columns from t1, followed by the non-join columns from t2.

For a right outer join

The join result contains the join columns from t2, followed by the non-join columns from t1, followed by the non-join columns from t2.

For a full outer join

The join result has a non-null value from the join columns followed by the non-join columns from t1, followed by the non-join columns from t2.

Note that the join columns appear in the join result only once.

Unless a projection column list is explicitly specified, the order of the columns in the output is the same as in the join result.

Any subsequent, unqualified reference to a join column of a USING clause by another clause (such as a WHERE, ON, ORDER BY, GROUP BY, or HAVING clause) resolves to the column from the join result. For example:

CREATE TABLE t1 (c1 int, c2 varchar(10), c3 numeric(4,2));
CREATE TABLE t2 (c1 bigint, c2 char(8), c4 numeric(6,3));
CREATE TABLE t3 (c3 bigint, c5 int, c6 numeric(6,3));

SELECT * FROM t1 FULL JOIN t2 USING (c1, c2) JOIN t3 ON (c1 = t3.c3);
Column projections: 
  CASE WHEN (t1.c1 IS NOT NULL) THEN t1.c1 ELSE t2.c1 END AS c1
  CASE WHEN (t1.c2 IS NOT NULL) THEN t1.c2 ELSE t2.c2 END AS c2
  t1.c3
  t2.c4
  t3.c3
  t3.c5
  t3.c6

The reference to column c1 in the ON clause resolves to the CASE expression that represents the join column c1 from the full outer join. So, the ON clause is transformed to:

ON ((CASE WHEN (t1.c1 IS NOT NULL) THEN t1.c1 ELSE t2.c1 END) = t3.c3)

The following restrictions apply:

• The ON and USING clauses are mutually exclusive for a particular join operation, that is, only one of these clauses can be specified when joining two tables. However, a single SQL statement can contain several join operations, and each of these can use either clause.
• An ON or USING clause cannot be used if a plus symbol (+) is used as the outer join operator.

Assume that the following three tables have been created:

CREATE TABLE t1 (c1 int, c2 varchar(10), c3 numeric(4,2));
CREATE TABLE t2 (c1 bigint, c2 char(8), c4 numeric(6,3));
CREATE TABLE t3 (c3 bigint, c5 int, c6 numeric(6,3));

• For a join with an ON clause:

  SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;
  Column projections: t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c4

  SELECT * FROM t1 FULL JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2;
  Column projections: t1.c1, t1.c2, t1.c3, t2.c1, t2.c2, t2.c4

• For an inner join or left outer join with a USING clause:

  SELECT * FROM t1 INNER JOIN t2 USING (c1, c2);
  Column projections: t1.c1, t1.c2, t1.c3, t2.c4

  SELECT * FROM t1 LEFT JOIN t2 USING (c1, c2);
  Column projections: t1.c1, t1.c2, t1.c3, t2.c4

• For a right outer join with a USING clause:

  SELECT * FROM t1 RIGHT JOIN t2 USING (c1, c2);
  Column projections: t2.c1, t2.c2, t1.c3, t2.c4

• For a full outer join with a USING clause:

  SELECT * FROM t1 FULL JOIN t2 USING (c1, c2);
  Column projections: 
    CASE WHEN (t1.c1 IS NOT NULL) THEN t1.c1 ELSE t2.c1 END AS c1
    CASE WHEN (t1.c2 IS NOT NULL) THEN t1.c2 ELSE t2.c2 END AS c2
    t1.c3
    t2.c4