Re: SQL Joins where join-condition column contains a NULL

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 7 Oct 2001 09:46:29 -0700
Message-ID: <c0d87ec0.0110070846.323af9b1_at_posting.google.com>

Let me just post that entire section from the SQL-92 specs:

--CELKO-- 7.5 <joined table>

Function

Specify a table derived from a Cartesian product, inner or outer join, or union join.

Format

<joined table> ::=

     <cross join>
   | <qualified join>
   | <left paren> <joined table> <right paren>

<cross join> ::=

<table reference> CROSS JOIN <table reference>

<qualified join> ::=

<table reference> [ NATURAL ] [ <join type> ] JOIN <table reference> [ <join specification> ]

<join specification> ::=

<named columns join> ::=

USING <left paren> <join column list> <right paren>

<join type> ::=

     INNER
   | <outer join type> [ OUTER ]
   | UNION
<outer join type> ::=

     LEFT
   | RIGHT
   | FULL
<join column list> ::= <column name list>

Syntax Rules

Let TR1 and TR2 be the first and second <table reference>s of the <joined table>, respectively. Let T1 and T2 be the tables identified by TR1 and TR2, respectively. Let TA and TB be the correlation names of TR1 and TR2, respectively. Let CP be:

SELECT * FROM TR1, TR2 2) If a <qualified join> is specified, then

Case:

If NATURAL is specified, then a <join specification> shall not be specified.
If UNION is specified, then neither NATURAL nor a <join specification> shall be specified.
Otherwise, a <join specification> shall be specified.
If a <qualified join> is specified and a <join type> is not specified, then INNER is implicit.
If a <qualified join> containing a <join condition> is specified, then;
Each <column reference> directly contained in the <search condition> shall unambiguously reference a column of T1 or T2 or be an outer reference.
If a <value expression> directly contained in the <search condition> is a <set function specification>, then the <joined table> shall be contained in a <having clause> or <select list> and the <set function specification> shall contain a <column reference> that is an outer reference.

Note: Outer reference is defined in Subclause 6.4, "<column reference>".

5) If neither NATURAL is specified nor a <join specification> simply containing a <named columns join> is specified, then the descriptors of the columns of the result of the <joined table> are the same as the descriptors of the columns of CP.

6) If NATURAL is specified or if a <join specification> simply containing a <named columns join> is specified, then:

If NATURAL is specified, then let common column name be a <column name> that is the <column name> of exactly one column of T1 and the <column name> of exactly one column of T2. T1 shall not have any duplicate common column names and T2 shall not have any duplicate common column names. Let corresponding join columns refer to all columns of T1 and T2 that have common column names, if any.
If a <named columns join> is specified, then every <column name> in the <join column list> shall be the <column name> of exactly one column of T1 and the <column name> of exactly one column of T2. Let common column name be the name of such a column. Let corresponding join columns refer to the columns of T1 and T2 identified in the <join column list>.
Let C1 and C2 be a pair of corresponding join columns contained in T1 and T2, respectively. C1 and C2 shall be comparable.
Let SLCC be a <select list> of <derived column>s of the form

COALESCE (TA.C, TB.C) AS C for every column C that is a corresponding join column, taken in order of their ordinal positions in T1.

e) Let SL1 be a <select list> of those <column name>s of T1 that are not corresponding join columns, taken in order of their ordinal positions in T1, and let SLT2 be a <select list> of those <column name>s of T2 that are not corresponding join columns, taken in order of their ordinal positions in T2.

f) The descriptors of the columns of the result of the <joined table> are the same as the descriptors of the columns of the result of

SELECT SLCC, SLT1, SLT2 FROM TR1, TR2 7) For every column CR of the result of the <joined table> that is not a corresponding join column and that corresponds to a column C1 of T1, CR is possibly nullable if any of the following conditions are true:

RIGHT, FULL, or UNION is specified, or
INNER, LEFT, or CROSS JOIN is specified or implicit and 1 is possibly nullable.
For every column CR of the result of the <joined table> that is not a corresponding join column and that corresponds to a column C2 of T2, CR is possibly nullable if any of the following conditions are true:
LEFT, FULL, or UNION is specified, or
INNER, RIGHT, or CROSS JOIN is specified or implicit and C is possibly nullable.
For every column CR of the result of the <joined table> that is a corresponding join column and that corresponds to a column C1 of T1 and C2 of T2, CR is possibly nullable if any of the following conditions are true:
RIGHT, FULL, or UNION is specified and 1 is possibly nullable, or
LEFT, FULL, or UNION is specified and 2 is possibly nullable.
The <joined table> is a read-only table.

Access Rules

None.

General Rules

Case:
If <join type> is UNION, then let T be the empty set.
If a <cross join> is specified, then let T be the multiset of rows of CP.
If a <join condition> is specified, then let T be the multiset of rows of CP for which the specified <search condition> is true.
If NATURAL is specified or <named columns join> is specified, then

Case:

i) If there are corresponding join columns, then let T be the

      multiset of rows of CP for which the corresponding join
      columns have equal values.

ii) Otherwise, let T be the multiset of rows of CP.

2) Let P1 be the multiset of rows of T1 for which there exists in T some row that is the concatenation of some row R1 of T1 and some row R2 of T2. Let P2 be the multiset of rows of T2 for which there exists in T some row that is the concatenation of some row R1 of T1 and some row R2 of T2.

3) Let U1 be those rows of T1 that are not in P1 and let U2 be those rows of T2 that are not in P2.

4) Let D1 and D2 be the degree of T1 and T2, respectively. Let X1 be U1 extended on the right with D2 columns containing the null value. Let X2 be U2 extended on the left with D1 columns containing the null value.

5) Let XN1 and XN2 be effective distinct names for X1 and X2, respectively. Let TN be an effective name for T.

Case:

If INNER or <cross join> is specified, then let S be the multiset of rows of T.
If LEFT is specified, then let S be the multiset of rows resulting from:

      SELECT * FROM TN
      UNION ALL
      SELECT * FROM XN1

c) If RIGHT is specified, then let S be the multiset of rows resulting from:

      SELECT * FROM TN
      UNION ALL
      SELECT * FROM XN2

d) If FULL is specified, then let S be the multiset of rows resulting from:

      SELECT * FROM TN
      UNION ALL
      SELECT * FROM XN1
      UNION ALL
      SELECT * FROM XN2

e) If UNION is specified, then let S be the multiset of rows resulting from:

      SELECT * FROM XN1
      UNION ALL
      SELECT * FROM XN2

6) Let SN be an effective name of S.

Case:

If NATURAL is specified or a <named columns join> is specified, then the result of the <joined table> is the multiset of rows resulting from:

SELECT SLCC, SLT1, SLT2 FROM SN b) Otherwise, the result of the <joined table> is S.

Leveling Rules

The following restrictions apply for Intermediate SQL:
Conforming Intermediate SQL language shall contain no <cross join>.
Conforming Intermediate SQL language shall not specify UNION JOIN.
The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions:
Conforming Entry SQL language shall not contain any <joined table>.

7.5 <joined table>

Function

Specify a table derived from a Cartesian product, inner or outer join, or union join.

Format

<joined table> ::=

     <cross join>
   | <qualified join>
   | <left paren> <joined table> <right paren>

<cross join> ::=

<table reference> CROSS JOIN <table reference>

<qualified join> ::=

<table reference> [ NATURAL ] [ <join type> ] JOIN <table reference> [ <join specification> ]

<join specification> ::=

<named columns join> ::=

USING <left paren> <join column list> <right paren>

<join type> ::=

     INNER
   | <outer join type> [ OUTER ]
   | UNION
<outer join type> ::=

     LEFT
   | RIGHT
   | FULL
<join column list> ::= <column name list>

Syntax Rules

Let TR1 and TR2 be the first and second <table reference>s of the <joined table>, respectively. Let T1 and T2 be the tables identified by TR1 and TR2, respectively. Let TA and TB be the correlation names of TR1 and TR2, respectively. Let CP be:

SELECT * FROM TR1, TR2 2) If a <qualified join> is specified, then

Case:

If NATURAL is specified, then a <join specification> shall not be specified.
If UNION is specified, then neither NATURAL nor a <join specification> shall be specified.
Otherwise, a <join specification> shall be specified.
If a <qualified join> is specified and a <join type> is not specified, then INNER is implicit.
If a <qualified join> containing a <join condition> is specified, then;
Each <column reference> directly contained in the <search condition> shall unambiguously reference a column of T1 or T2 or be an outer reference.
If a <value expression> directly contained in the <search condition> is a <set function specification>, then the <joined table> shall be contained in a <having clause> or <select list> and the <set function specification> shall contain a <column reference> that is an outer reference.

Note: Outer reference is defined in Subclause 6.4, "<column reference>".

6) If NATURAL is specified or if a <join specification> simply containing a <named columns join> is specified, then:

If NATURAL is specified, then let common column name be a <column name> that is the <column name> of exactly one column of T1 and the <column name> of exactly one column of T2. T1 shall not have any duplicate common column names and T2 shall not have any duplicate common column names. Let corresponding join columns refer to all columns of T1 and T2 that have common column names, if any.
If a <named columns join> is specified, then every <column name> in the <join column list> shall be the <column name> of exactly one column of T1 and the <column name> of exactly one column of T2. Let common column name be the name of such a column. Let corresponding join columns refer to the columns of T1 and T2 identified in the <join column list>.
Let C1 and C2 be a pair of corresponding join columns contained in T1 and T2, respectively. C1 and C2 shall be comparable.
Let SLCC be a <select list> of <derived column>s of the form

COALESCE (TA.C, TB.C) AS C for every column C that is a corresponding join column, taken in order of their ordinal positions in T1.

f) The descriptors of the columns of the result of the <joined table> are the same as the descriptors of the columns of the result of

RIGHT, FULL, or UNION is specified, or
INNER, LEFT, or CROSS JOIN is specified or implicit and 1 is possibly nullable.
For every column CR of the result of the <joined table> that is not a corresponding join column and that corresponds to a column C2 of T2, CR is possibly nullable if any of the following conditions are true:
LEFT, FULL, or UNION is specified, or
INNER, RIGHT, or CROSS JOIN is specified or implicit and C is possibly nullable.
For every column CR of the result of the <joined table> that is a corresponding join column and that corresponds to a column C1 of T1 and C2 of T2, CR is possibly nullable if any of the following conditions are true:
RIGHT, FULL, or UNION is specified and 1 is possibly nullable, or
LEFT, FULL, or UNION is specified and 2 is possibly nullable.
The <joined table> is a read-only table.

Access Rules

None.

General Rules

Case:
If <join type> is UNION, then let T be the empty set.
If a <cross join> is specified, then let T be the multiset of rows of CP.
If a <join condition> is specified, then let T be the multiset of rows of CP for which the specified <search condition> is true.
If NATURAL is specified or <named columns join> is specified, then

Case:

i) If there are corresponding join columns, then let T be the

      multiset of rows of CP for which the corresponding join
      columns have equal values.

ii) Otherwise, let T be the multiset of rows of CP.

3) Let U1 be those rows of T1 that are not in P1 and let U2 be those rows of T2 that are not in P2.

5) Let XN1 and XN2 be effective distinct names for X1 and X2, respectively. Let TN be an effective name for T.

Case:

If INNER or <cross join> is specified, then let S be the multiset of rows of T.
If LEFT is specified, then let S be the multiset of rows resulting from:

      SELECT * FROM TN
      UNION ALL
      SELECT * FROM XN1

c) If RIGHT is specified, then let S be the multiset of rows resulting from:

      SELECT * FROM TN
      UNION ALL
      SELECT * FROM XN2

d) If FULL is specified, then let S be the multiset of rows resulting from:

      SELECT * FROM TN
      UNION ALL
      SELECT * FROM XN1
      UNION ALL
      SELECT * FROM XN2

e) If UNION is specified, then let S be the multiset of rows resulting from:

      SELECT * FROM XN1
      UNION ALL
      SELECT * FROM XN2

6) Let SN be an effective name of S.

Case:

If NATURAL is specified or a <named columns join> is specified, then the result of the <joined table> is the multiset of rows resulting from:

SELECT SLCC, SLT1, SLT2 FROM SN b) Otherwise, the result of the <joined table> is S.

Leveling Rules

The following restrictions apply for Intermediate SQL:
Conforming Intermediate SQL language shall contain no <cross join>.
Conforming Intermediate SQL language shall not specify UNION JOIN.
The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions:
Conforming Entry SQL language shall not contain any <joined table>.

Received on Sun Oct 07 2001 - 18:46:29 CEST

This message: [ Message body ]
Next message: Jeff Zucker: "Re: SQL Joins where join-condition column contains a NULL"
Previous message: Christoffer Vig: "How to prevent updates on foreign key default values"
In reply to: Jeff Zucker: "SQL Joins where join-condition column contains a NULL"
Next in thread: Jeff Zucker: "Re: SQL Joins where join-condition column contains a NULL"
Reply: Jeff Zucker: "Re: SQL Joins where join-condition column contains a NULL"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message