Returning Missing Data from Multiple Tables : SQL Server

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.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table