Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: NATURAL JOIN without join columbs

Re: NATURAL JOIN without join columbs

From: -CELKO- <jcelko212_at_earthlink.net>
Date: 14 Mar 2005 08:28:31 -0800
Message-ID: <1110817711.669667.324930@f14g2000cwb.googlegroups.com>


You go Cartesian. Let me quote that part of SQL-92:

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> ::=
<join condition>

 | <named columns join>

<join condition> ::= ON <search condition>

<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

  1. 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:

  1. If NATURAL is specified, then a <join specification> shall not be specified.
  2. If UNION is specified, then neither NATURAL nor a <join specification> shall be specified.
  3. Otherwise, a <join specification> shall be specified.
  4. If a <qualified join> is specified and a <join type> is not specified, then INNER is implicit.
  5. If a <qualified join> containing a <join condition> is specified, then;
  6. Each <column reference> directly contained in the <search condition> shall unambiguously reference a column of T1 or T2 or be an outer reference.
  7. 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:

  1. 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.
  2. 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>.
  3. Let 1 and C2 be a pair of corresponding join columns contained in T1 and T2, respectively. C1 and C2 shall be comparable.
  4. 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:

  1. RIGHT, FULL, or UNION is specified, or
  2. INNER, LEFT, or CROSS JOIN is specified or implicit and 1 is possibly nullable.
  3. 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:
  4. LEFT, FULL, or UNION is specified, or
  5. INNER, RIGHT, or CROSS JOIN is specified or implicit and C is possibly nullable.
  6. 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:
  7. RIGHT, FULL, or UNION is specified and 1 is possibly nullable, or
  8. LEFT, FULL, or UNION is specified and 2 is possibly nullable.
  9. The <joined table> is a read-only table.

 Access Rules

 None.

 General Rules

  1. Case:
  2. If <join type> is UNION, then let T be the empty set.
  3. If a <cross join> is specified, then let T be the multiset of rows of CP.
  4. If a <join condition> is specified, then let T be the multiset of rows of CP for which the specified <search condition> is true.
  5. 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:

  1. If INNER or <cross join> is specified, then let S be the multiset of rows of T.
  2. 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:
 .. Received on Mon Mar 14 2005 - 10:28:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US