Oracle Proprietary vs. ANSI-99 Joins

Oracle Proprietary Joins	ANSI-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.