Recipe11.3.Incorporating OR Logic when Using Outer Joins

Recipe 11.3. Incorporating OR Logic when Using Outer Joins

Problem

You want to return the name and department information for all employees in departments 10 and 20 along with department information for departments 30 and 40 (but no employee information). Your first attempt looks like this:

  select e.ename, d.deptno, d.dname, d.loc   from dept d, emp e  where d.deptno = e.deptno    and (e.deptno = 10 or e.deptno = 20)  order by 2 ENAME       DEPTNO DNAME          LOC ------- ---------- -------------- ----------- CLARK           10 ACCOUNTING     NEW YORK KING            10 ACCOUNTING     NEW YORK MILLER          10 ACCOUNTING     NEW YORK SMITH           20 RESEARCH       DALLAS ADAMS           20 RESEARCH       DALLAS FORD            20 RESEARCH       DALLAS SCOTT           20 RESEARCH       DALLAS JONES           20 RESEARCH       DALLAS 

Because the join in this query is an inner join, the result set does not include department information for DEPTNOs 30 and 40.

You attempt to outer join EMP to DEPT with the following query, but you still do not get the correct results:

  select e.ename, d.deptno, d.dname, d.loc   from dept d left join emp e     on (d.deptno = e.deptno)  where e.deptno = 10     or e.deptno = 20  order by 2 ENAME       DEPTNO DNAME        LOC ------- ---------- ------------ ----------- CLARK           10 ACCOUNTING   NEW YORK KING            10 ACCOUNTING   NEW YORK MILLER          10 ACCOUNTING   NEW YORK SMITH           20 RESEARCH     DALLAS ADAMS           20 RESEARCH     DALLAS FORD            20 RESEARCH     DALLAS SCOTT           20 RESEARCH     DALLAS JONES           20 RESEARCH     DALLAS 

Ultimately, you would like the result set to be:

 ENAME       DEPTNO DNAME        LOC ------- ---------- ------------ --------- CLARK           10 ACCOUNTING    NEW YORK KING            10 ACCOUNTING    NEW YORK MILLER          10 ACCOUNTING    NEW YORK SMITH           20 RESEARCH      DALLAS JONES           20 RESEARCH      DALLAS SCOTT           20 RESEARCH      DALLAS ADAMS           20 RESEARCH      DALLAS FORD            20 RESEARCH      DALLAS                 30 SALES         CHICAGO                 40 OPERATIONS    BOSTON 

Solution

DB2, MySQL, PostgreSQL, and SQL Server

Move the OR condition into the JOIN clause:

 1  select e.ename, d.deptno, d.dname, d.loc 2    from dept d left join emp e 3      on (d.deptno = e.deptno 4         and (e.deptno=10 or e.deptno=20)) 5   order by 2 

Alternatively, you can filter on EMP.DEPTNO first in an inline view and then outer join:

 1  select e.ename, d.deptno, d.dname, d.loc 2    from dept d 3    left join 4         (select ename, deptno 5            from emp 6           where deptno in ( 10, 20 ) 7         ) e on ( e.deptno = d.deptno ) 8  order by 2 

Oracle

If you are on Oracle9i Database or later, you can use either of the solutions for the other products. Otherwise, you need to use CASE or DECODE in a workaround. Following is a solution using CASE:

 select e.ename, d.deptno, d.dname, d.loc   from dept d, emp e  where d.deptno = e.deptno (+)    and d.deptno = case when e.deptno(+) = 10 then e.deptno(+)                        when e.deptno(+) = 20 then e.deptno(+)                   end    order by 2 

And next is the same solution, but this time using DECODE:

 select e.ename, d.deptno, d.dname, d.loc   from dept d, emp e  where d.deptno = e.deptno (+)    and d.deptno = decode(e.deptno(+),10,e.deptno(+),                                      20,e.deptno(+))  order by 2 

When using the proprietary Oracle outer join syntax (+) along with an IN or OR predicate on an outer joined column, the query will return an error. The solution is to move the IN or OR predicate to an inline view:

 select e.ename, d.deptno, d.dname, d.loc   from dept d,       ( select ename, deptno           from emp          where deptno in ( 10, 20 )       ) e  where d.deptno = e.deptno (+)  order by 2 

Discussion

DB2, MySQL, PostgreSQL, and SQL Server

Two solutions are given for these products. The first moves the OR condition into the JOIN clause, making it part of the join condition. By doing that, you can filter the rows returned from EMP without losing DEPTNOs 30 and 40 from DEPT.

The second solution moves the filtering into an inline view. Inline view E filters on EMP.DEPTNO and returns EMP rows of interest. These are then outer joined to DEPT. Because DEPT is the anchor table in the outer join, all departments, including 30 and 40, are returned.

Oracle

Use the CASE and DECODE functions as a workaround for what seems to be a bug in the older outer-join syntax. The solution using inline view E works by first finding the rows of interest in table EMP, and then outer joining to DEPT.