Recipe3.11.Returning Missing Data from Multiple Tables

Recipe 3.11. Returning Missing Data from Multiple Tables

Problem

You want to return missing data from multiple tables simultaneously. Returning rows from table DEPT that do not exist in table EMP (any departments that have no employees) requires an outer join. Consider the following query, which returns all DEPTNOs and DNAMEs from DEPT along with the names of all the employees in each department (if there is an employee in a particular department):

  select d.deptno,d.dname,e.ename   from dept d left outer join emp e     on (d.deptno=e.deptno)    DEPTNO DNAME          ENAME --------- -------------- ----------        20 RESEARCH       SMITH        30 SALES          ALLEN        30 SALES          WARD        20 RESEARCH       JONES        30 SALES          MARTIN        30 SALES          BLAKE        10 ACCOUNTING     CLARK        20 RESEARCH       SCOTT        10 ACCOUNTING     KING        30 SALES          TURNER        20 RESEARCH       ADAMS        30 SALES          JAMES        20 RESEARCH       FORD        10 ACCOUNTING     MILLER        40 OPERATIONS 

The last row, the OPERATIONS department, is returned despite that department not having any employees, because table EMP was outer joined to table DEPT. Now, suppose there was an employee without a department. How would you return the above result set along with a row for the employee having no department? In other words, you want to outer join to both table EMP and table DEPT, and in the same query. After creating the new employee, a first attempt may look like this:

 insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) select 1111,'YODA','JEDI',null,hiredate,sal,comm,null   from emp  where ename = 'KING'  select d.deptno,d.dname,e.ename   from dept d right outer join emp e     on (d.deptno=e.deptno)     DEPTNO DNAME        ENAME ---------- ------------ ----------         10 ACCOUNTING   MILLER         10 ACCOUNTING   KING         10 ACCOUNTING   CLARK         20 RESEARCH     FORD         20 RESEARCH     ADAMS         20 RESEARCH     SCOTT         20 RESEARCH     JONES         20 RESEARCH     SMITH         30 SALES        JAMES         30 SALES        TURNER         30 SALES        BLAKE         30 SALES        MARTIN         30 SALES        WARD         30 SALES        ALLEN                         YODA 

This outer join manages to return the new employee but lost the OPERATIONS department from the original result set. The final result set should return a row for YODA as well as OPERATIONS, such as the following:

     DEPTNO DNAME        ENAME ---------- ------------ --------         10 ACCOUNTING   CLARK         10 ACCOUNTING   KING         10 ACCOUNTING   MILLER         20 RESEARCH     ADAMS         20 RESEARCH     FORD         20 RESEARCH     JONES         20 RESEARCH     SCOTT         20 RESEARCH     SMITH         30 SALES        ALLEN         30 SALES        BLAKE         30 SALES        JAMES         30 SALES        MARTIN         30 SALES        TURNER         30 SALES        WARD         40 OPERATIONS                          YODA 

Solution

Use a full outer join to return missing data from both tables based on a common value.

DB2, MySQL, PostgreSQL, SQL Server

Use the explicit FULL OUTER JOIN command to return missing rows from both tables along with matching rows:

 1 select d.deptno,d.dname,e.ename 2   from dept d full outer join emp e 3     on (d.deptno=e.deptno) 

Alternatively, union the results of two different outer joins:

 1 select d.deptno,d.dname,e.ename 2   from dept d right outer join emp e 3     on (d.deptno=e.deptno) 4  union 5 select d.deptno,d.dname,e.ename 6   from dept d left outer join emp e 7     on (d.deptno=e.deptno) 

Oracle

If you are on Oracle9i Database or later, you can use either of the preceding solutions. Alternatively, you can use Oracle's proprietary outer join syntax, which is the only choice for users on Oracle8i Database and earlier:

 1 select d.deptno,d.dname,e.ename     2   from dept d, emp e 3  where d.deptno = e.deptno(+) 4  union 5 select d.deptno,d.dname,e.ename 6   from dept d, emp e 7  where d.deptno(+) = e.deptno 

Discussion

The full outer join is simply the combination of outer joins on both tables. To see how a full outer join works "under the covers," simply run each outer join, then union the results. The following query returns rows from table DEPT and any matching rows from table EMP (if any).

  select d.deptno,d.dname,e.ename   from dept d left outer join emp e     on (d.deptno = e.deptno)   DEPTNO DNAME          ENAME   ------ -------------- ----------       20 RESEARCH       SMITH       30 SALES          ALLEN       30 SALES          WARD       20 RESEARCH       JONES       30 SALES          MARTIN       30 SALES          BLAKE       10 ACCOUNTING     CLARK       20 RESEARCH       SCOTT       10 ACCOUNTING     KING       30 SALES          TURNER       20 RESEARCH       ADAMS       30 SALES          JAMES       20 RESEARCH       FORD       10 ACCOUNTING     MILLER       40 OPERATIONS 

This next query returns rows from table EMP and any matching rows from table DEPT (if any):

  select d.deptno,d.dname,e.ename   from dept d right outer join emp e     on (d.deptno = e.deptno)  DEPTNO DNAME          ENAME  ------ -------------- ----------      10 ACCOUNTING     MILLER      10 ACCOUNTING     KING      10 ACCOUNTING     CLARK      20 RESEARCH       FORD      20 RESEARCH       ADAMS      20 RESEARCH       SCOTT      20 RESEARCH       JONES      20 RESEARCH       SMITH      30 SALES          JAMES      30 SALES          TURNER      30 SALES          BLAKE      30 SALES          MARTIN      30 SALES          WARD      30 SALES          ALLEN                            YODA 

The results from these two queries are unioned to provide the final result set.