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