On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Wednesday, July 13, 2011

Oracle Proprietary vs. ANSI-99 Joins


 Oracle Proprietary JoinsANSI-99 SQL Joins
To display only rows with matching Keys
  • "Equi-Join" aka "Simple Join" aka "Inner Join"
  • The WHERE clause states how to join the tables
  • Use AND to specify other conditions (i.e., AND City = 'DC')
Select L.Key, NameL, NameR     
  From TableL L
     , TableR R
 WHERE L.Key = R.Key;
  • Natural Join
  • Can't use aliases
1) Key Name and Type are the same
Select Key, NameL, NameR
  From TableL 
NATURAL
  JOIN TableR;
2) Key Name Same, Different Type
Select Key, NameL, NameR
  From TableL
  JOIN TableR
 USING (Key);
3) Different Key Names
Select Key, Key3, NameL, Name3
  From TableL
  JOIN Table3
    ON (Key = Key3);
"Non-Equi-Join": Keys are >, <, or BETWEEN
Select L.Key, R.Key
     , NameL, NameR
  From TableL L
     , TableR R
 Where L.Key > R.Key;
Select L.Key, R.Key, NameL, NameR     
  From TableL L
  JOIN TableR R
    ON (l.Key > r.Key); 
Joining More Than 2 tables
  • There is always one less WHERE/AND clause than there are tables to join. 
(3 tables = 2 clauses, etc)
    Select Key, NameL, NameR, Name3
      From TableL l
         , TableR r
         , Table3 t
     Where l.Key = r.Key
       And l.Key = t.Key
    • USING when names are the same.
    • ON when names are different
    Select Key, NameL, NameR, Name3
      From TableL L
      -----------
      Join TableR 
     Using (Key)
      -----------
      Join Table3 
        On (l.Key = Key3); 
    To see unmatched rows “Right Outer Joins”
    Select L.Key, NameL, NameR
      From TableL L
         , TableR R
     Where L.Key(+) = R.Key  ;
    (Think of "Add rows to the left side so we see a full set of records on the right.")
    Select Key, NameL, NameR
      From TableL 
    RIGHT OUTER
      JOIN TableR 
     Using (Key);

    To see unmatched rows: “Left Outer Join”
    Select L.Key, NameL, NameR     
      From TableL L
         , TableR R
     Where L.Key = R.Key(+);
    (Think of "Add rows to the right side so we see a full set of records on the Left.")
    Select Key, NameL, NameR
      From TableL 
    LEFT OUTER
      JOIN TableR
     Using (Key);
    To see all unmatched rows "Full Outer Join"
    Oracle doesn’t have a full outer join.
    Select l.Key, r.Key, NameL, NameR
      From TableL L  
    FULL OUTER
      JOIN TableR R
      on (l.Key = r.Key);

    CROSS JOIN is the ANSI name for Oracle's Cartesian Product -- Don't do this no matter what you call it.

    No comments:

    Post a Comment