How to retrieve values from one table that do not exist in another?

You want to find those values in one table, call it the source table, that do not also exist in some target table. For example, you want to find which departments (if any) in table Department do not exist in table Staff. In the example data, deptno 40 from table Department does not exist in table Staff.

Staff Table

empno ename job mgr_no hiredate sal commission deptno
7369 ALI CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 RAHUL ANALYST 7566 1982-12-09 3000.00 NULL 20
7839 RAJ PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAM CLERK 7788 1983-01-12 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 ARHAM ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10

Department Table

deptno dname loc
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Example

Having functions that perform set difference is particularly useful for this problem. DB2, PostgreSQL, SQL Server, and Oracle all support set difference operations. If your DBMS does not support a set difference function, use a subquery as shown for MySQL.

DB2, PostgreSQL, and SQL Server

Use the set operation EXCEPT:

SELECT deptno FROM Department
EXCEPT
SELECT deptno FROM Staff

Output

deptno
40

Oracle

Use the set operation MINUS:

SELECT deptno FROM Department
MINUS
SELECT deptno FROM Staff

Output

deptno
40

MySQL

Use a subquery to return all deptno from table Staff into an outer query that
searches table Department for rows that are not among the rows returned from the
subquery:

SELECT deptno
FROM Department
WHERE deptno NOT IN (SELECT deptno from Staff)

Output

deptno
40

Discussion

DB2, PostgreSQL, and SQL Server

Set difference functions make this operation easy. The EXCEPT operator takes the first result set and removes from it all rows found in the second result set. The operation is very much like a subtraction. There are restrictions on the use of set operators, including EXCEPT. Data types and number of values to compare must match in both SELECT lists. Additionally,EXCEPT will not return duplicates and, unlike a subquery using NOT IN, NULLs do not present a problem (see the discussion for MySQL). The EXCEPT operator will return rows from the upper query (the query before the EXCEPT) that do not exist in the lower query (the query after the EXCEPT).

Oracle

The Oracle solution is identical to the solution using the EXCEPT operator; however, Oracle calls its set difference operator MINUS instead of EXCEPT. Otherwise, the preceding explanation applies to Oracle as well.

MySQL

The subquery will return all deptnos from table Staff. The outer query returns all
deptnos from table Department that are “not in” or “not included in” the result set
returned from the subquery.