Using CASE Statements in LEFT OUTER JOIN in SQL

Use (Oracle 9i+):

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

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

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 SALESPADFIELD   SALESMAN          30 SALESBILLINGTON SALESMAN          30 SALESSPENCER    MANAGER           30 SALESCAVE       SALESMAN          30 SALESHALL       CLERK             30 SALESVERREYNNE  PLUMBER           50 SKUNKWORKSFEUERSTEIN PLUMBER           50 SKUNKWORKS8 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.