Who's on First?

It's important to know which SQL statements happen before others. Hopefully, this list will make it obvious why you can't use an alias in a WHERE statement, and why analytic functions can only be a SELECT:

1. From / Join
2. Connect By
3. Where
4. Group By
5. Having
6. Select / Analytic Functions
7. Distinct
8. Order By
9. Fetch / Offset

Outer Join with Where

I may be the last one to figure this out, but a WHERE statement causes grief with an outer JOIN. Here I’m trying to join on a code table to get a description, but I only want those descriptions used for ‘Status,’ and not all records in Store have a status value.

Doesn't Work

Don't use ON key and WHERE condition.

SELECT client_id                       AS "Client#"
     , cs.status                       AS "Status"
     , dc.cd_meaning                   AS "Description"
     , TO_CHAR(cs.dcnt, '999,999,999') AS "Rec Cnt"
  FROM (
        SELECT client_id
             , status
             , COUNT(*)        AS dcnt
          FROM Store
         GROUP BY client_id, status
       ) cs
  LEFT
  JOIN def_codes dc
    ON cs.status = dc.cd
 WHERE column_name = 'STATUS';

Works

Use ON (key and AND condition).

SELECT client_id                       AS "Client#"
     , cs.status                       AS "Status"
     , dc.cd_meaning                   AS "Description"
     , TO_CHAR(cs.dcnt, '999,999,999') AS "Rec Cnt"
  FROM (
        SELECT client_id
             , status
             , COUNT(*)        AS dcnt
          FROM Store
         GROUP BY client_id, status
       ) cs
  LEFT
  JOIN def_codes dc
    ON ( cs.status = dc.cd
         AND
        column_name = 'STATUS'
       );

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.

An Example of Ansi Joins with Multiple Tables

These tables are from the HR schema. I'm showing two examples, one uses 'Using' and the other with the 'On.'

         Select r.Region_Name
              , c.Country_Name
              , l.State_Province
              , d.Department_Name
              , e.Last_Name 
              , e.Job_Id
           From Regions     R
           Join Countries   C On r.Region_Id     = c.Region_Id
Full Outer Join Locations   L On c.Country_Id    = l.Country_Id
Full Outer Join Departments D On l.Location_Id   = d.Location_Id
Full Outer Join Employees   E On d.Department_Id = e.Department_Id
          Where r.Region_Name  =  'Americas'
            And c.Country_Name <> 'United States of America';
         Select r.Region_Name
              , c.Country_Name
              , l.State_Province
              , d.Department_Name
              , e.Last_Name 
              , e.Job_Id
           From Regions     R
           Join Countries   C using ( Region_Id )
Full Outer Join Locations   L using ( Country_Id )
Full Outer Join Departments D using ( Location_Id )
Full Outer Join Employees   E using ( Department_Id )
          Where r.Region_Name  =  'Americas'
            And c.Country_Name <> 'United States of America';

Correlated Subquery

Most subqueries are resolved before Oracle moves on to handle its parent query. Sometimes, that's not possible since the subquery if based on one of the columns on the parent query.

An example is shown below. We want to find who makes the least in each department. So, in the subquery, determining the mininum salary is done based on the department number of the employee in the parent query.

Select Empno, Ename, DeptNo, Sal
  From Emp e1
 Where Sal = (
              Select Min(Sal)
                From Emp E2
               Where e2.DeptNo = e1.DeptNo
             )
 Order By DeptNo;

Self-Join

The employee records have two different employee numbers -- one for the individual and one for their boss. The people in department 10 are listed below. As shown, Clark reports to 7839, who we can see is King. Miller reports to 7782, who is Clark.


To do this in SQL, you need to join the table to itself. It is defined twice in the query, and given different aliases by which we can address each version of the table.

Select e1.EmpNo,
       e1.EName,
       e1.Mgr  ,
       e2.EName As Manager
  From Emp e1
  Join Emp E2
    On e1.Mgr = e2.EmpNo
 Where e1.DeptNo = 10
 Order By EmpNo;

 EMPNO ENAME             MGR MANAGER
------ ---------- ---------- ---------
  7782 CLARK            7839 KING
  7934 MILLER           7782 CLARK

Outer Join

Column Key_L  Format 999999 Heading Key-L
Column Key_R  Format 999999 Heading Key-R
Column Join_type Format A20 Heading " "
Break On Join_type

Drop Table Table_L;
Drop Table Table_R;

Create Table Table_L (Key_L Number(1));
Create Table Table_R (Key_R Number(1));

Insert Into Table_L Values (1);
Insert Into Table_L Values (2);

Insert Into Table_R Values (1);
Insert Into Table_R Values (3);

Select * from Table_L;
Select * from Table_R;

Prompt =========================================================
Prompt = Non-Ansi Joins
Prompt =========================================================

Define Join_type = "'Key_L = Key_R'"

Select &Join_Type as Join_type, KEY_L, KEY_R
  From Table_L L 
     , Table_R R
 Where L.Key_L = R.Key_R;

Define Join_type = "'Key_L(+) = Key_R'"
Select &Join_Type as Join_type, KEY_L,   KEY_R
  From Table_L L
     , Table_R R
 Where L.Key_L(+) = R.Key_R;

Prompt
Prompt =========================================================
Prompt = Ansi-Compliant Joins
Prompt =========================================================

Define Join_type = "'Join'"
Select &Join_Type as Join_type, KEY_L,   KEY_R
  From Table_L 
  Join Table_R 
   On ( Key_L = Key_R);

Define Join_type = "'Right Outer Join'"
Select &Join_Type as Join_type, KEY_L,   KEY_R
  From Table_L Right Outer 
  Join Table_R 
   On ( Key_L = Key_R);

Define Join_type = "'Full Outer Join'"
Select &Join_Type as Join_type, KEY_L,   KEY_R
  From Table_L Full Outer 
  Join Table_R 
   On ( Key_L = Key_R);

Outer Join Problem

Two tables need to be joined. They have the following columns.

Tbl1          Tbl2
----          ----
FieldA (key)  FieldA (key)
FieldB        FieldD
FieldC   

Tbl1 data:    Tbl2 data:
----------    ----------
1             1
5
6             6
              7

We want to see everything on Tbl2 whether or not there is a record on Tbl1.

Even though we specify '(+)' to indicate an outer join, this will not work correctly since there is another condition involved on that table.

Select tbl2.FieldA, tbl2.FieldD, tbl1.FieldB 
  From tbl1, tbl2 
 where tbl1.FieldA(+) = tbl2.FieldA         ** DOESN'T WORK **
   And tbl1.FieldC = '1';

Fix #1:

Select tbl2.FieldA, tbl2.FieldD, tbl1.FieldB 
  From tbl1, tbl2 
 where tbl1.FieldA(+) = tbl2.FieldA         
   And tbl1.FieldC(+) = '1';

Fix #2:

Select tbl2.FieldA, tbl2.FieldD, tbl1.FieldB 
  From (
        Select *
          From tbl1
         Where FieldC(+) = '1'  -- the condition is moved to a subquery
       ) tbl1,
       tbl2 
 where tbl1.FieldA(+) = tbl2.FieldA;