Using CASE Statements in LEFT OUTER JOIN in SQL

Question

I've got a scenario where I want to switch on two different tables in an outer join. It goes something like this:-

         select mytable.id, 
                yourtable.id
           from mytable
left outer join (case
                    when mytable.id = 2 then table2 
                      yourtable on table1.id = table2.id
                    else
                      table3 yourtable on table1.id = table3.id
                 end)

...but it doesn't work. Any suggestions?

Answer 1

Use (Oracle 9i+):

   SELECT mt.id, 
          COALESCE(yt1.id, yt2.id)
     FROM MYTABLE mt
LEFT JOIN YOURTABLE yt1 ON yt1.id = mt.id
                       AND yt.id = 2
LEFT JOIN YOURTABLE yt2 ON yt2.id = mt.id

Answer 2

This query joins records from the EMP table to either the DEPT table or the SPECIAL_OPS table, depending on the value of EMP.DEPTNO ...

SQL> select e.ename
  2         , e.job
  3         , e.deptno
  4         , coalesce(d.dname, s.dname) as dname
  5  from  emp e
  6        left outer join dept d
  7             on ( e.deptno = 30
  8                  and e.deptno = d.deptno )
  9        left outer join special_ops s
 10             on ( e.deptno != 30
 11                  and e.deptno = s.deptno )
 12  where e.deptno in (30,50)
 13  order by e.deptno, e.empno
 14  /

ENAME      JOB           DEPTNO DNAME
---------- --------- ---------- --------------
VAN WIJK   SALESMAN          30 SALES
PADFIELD   SALESMAN          30 SALES
BILLINGTON SALESMAN          30 SALES
SPENCER    MANAGER           30 SALES
CAVE       SALESMAN          30 SALES
HALL       CLERK             30 SALES
VERREYNNE  PLUMBER           50 SKUNKWORKS
FEUERSTEIN PLUMBER           50 SKUNKWORKS

8 rows selected.

SQL>

I have included the filter on EMP.DEPTNO in the ON clauses. This might be unnecessary if the data in the tables is exclusive (i.e. DEPTNO = 30 could only join to DEPT and DEPTNO = 50 could only join to SPECIAL_OPS). However, if the identifier can appear in both tables it is as well to be explicit. Besides, making our intent clear is always good practice. Apart from anything else, we cannot be sure about the future state of the data.

Answer 3

Here's another possibility, although I haven't tried it on Oracle:

select mytable.id,  
       yourtable.id 
from table1 as mytable left outer join 
    (SELECT 2 AS tableid, *
     FROM table2
     UNION ALL
     SELECT 1, *
     FROM table3) as yourtable
    ON mytable.id = yourtable.id
    AND tableid = CASE WHEN mytable.id = 2 THEN 2 ELSE 1 END