Recipe3.5.Retrieving Rows from One Table That Do Not Correspond to Rows in Another

Recipe 3.5. Retrieving Rows from One Table That Do Not Correspond to Rows in Another

Problem

You want to find rows that are in one table that do not have a match in another table, for two tables that have common keys. For example, you want to find which departments have no employees. The result set should be the following:

       DEPTNO  DNAME           LOC   ----------  --------------  -------------           40  OPERATIONS      BOSTON 

Finding the department each employee works in requires an equi-join on DEPTNO from EMP to DEPT. The DEPTNO column represents the common value between tables. Unfortunately, an equi-join will not show you which department has no employees. That's because by equi-joining EMP and DEPT you are returning all rows that satisfy the join condition. Instead you want only those rows from DEPT that do not satisfy the join condition.

This is a subtly different problem than in the preceding recipe, though at first glance they may seem the same. The difference is that the preceding recipe yields only a list of department numbers not represented in table EMP. Using this recipe, however, you can easily return other columns from the DEPT table; you can return more than just department numbers.

Solution

Return all rows from one table along with rows from another that may or may not have a match on the common column. Then, keep only those rows with no match.

DB2, MySQL, PostgreSQL, SQL Server

Use an outer join and filter for NULLs (keyword OUTER is optional):

 1 select d.* 2   from dept d left outer join emp e 3     on (d.deptno = e.deptno) 4  where e.deptno is null 

Oracle

For users on Oracle9i Database and later, the preceding solution will work. Alternatively, you can use the proprietary Oracle outer-join syntax:

 1 select d.* 2   from dept d, emp e 3  where d.deptno = e.deptno (+) 4    and e.deptno is null 

This proprietary syntax (note the use of the "+" in parens) is the only outer-join syntax available in Oracle8i Database and earlier.

Discussion

This solution works by outer joining and then keeping only rows that have no match. This sort of operation is sometimes called an anti-join. To get a better idea of how an anti-join works, first examine the result set without filtering for NULLs:

  select e.ename, e.deptno as emp_deptno, d.*   from dept d left join emp e     on (d.deptno = e.deptno) ENAME       EMP_DEPTNO      DEPTNO DNAME          LOC ----------  ----------  ---------- -------------- ------------- SMITH               20          20 RESEARCH       DALLAS ALLEN               30          30 SALES          CHICAGO WARD                30          30 SALES          CHICAGO JONES               20          20 RESEARCH       DALLAS MARTIN              30          30 SALES          CHICAGO BLAKE               30          30 SALES          CHICAGO CLARK               10          10 ACCOUNTING     NEW YORK SCOTT               20          20 RESEARCH       DALLAS KING                10          10 ACCOUNTING     NEW YORK TURNER              30          30 SALES          CHICAGO ADAMS               20          20 RESEARCH       DALLAS JAMES               30          30 SALES          CHICAGO FORD                20          20 RESEARCH       DALLAS MILLER              10          10 ACCOUNTING     NEW YORK                                 40 OPERATIONS     BOSTON 

Notice, the last row has a NULL value for EMP.ENAME and EMP_DEPTNO. That's because no employees work in department 40. The solution uses the WHERE clause to keep only rows where EMP_DEPTNO is NULL (thus keeping only rows from DEPT that have no match in EMP).