Geek Answers Handbook

Using CASE statements in LEFT OUTER JOIN in SQL

I have a scenario in which I want to include two different tables in an outer join. This happens 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 does not work. Any suggestions?

+5
source share
3 answers

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
+4
source

Here is another possibility, although I have not tried it in 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
+3
source

EMP DEPT SPECIAL_OPS, 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 turned on the filter in EMP.DEPTNO in the ON clauses. This may not be necessary if the data in the tables are exclusive (i.e., DEPTNO = 30 can only join DEPT, and DEPTNO = 50 can only join SPECIAL_OPS). However, if an identifier can appear in both tables, it must also be explicit. In addition, making our intent clear is always good practice. Among other things, we cannot be sure of the future state of the data.

+1
source

More articles:


All Articles