How to retrieve rows from one table that do not correspond to rows in another in SQL?

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 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

Finding the department each employee works in requires an equi-join on deptno from Staff to Department. 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 Staff and Department you are returning all rows that satisfy the join condition. Instead, you want only those rows from Department that do not satisfy the join condition.

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, and SQL Server

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

SELECT d.*
FROM Department d left outer join Staff s
ON (d.deptno = s.deptno)
WHERE s.deptno is null

Output

deptno dname loc
40 OPERATIONS BOSTON

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 s.ename, s.deptno as staff_deptno, d.*
FROM Department d left join Staff s
ON (d.deptno = s.deptno)
ename staff_deptno deptno dname loc
CLARK 10 10 ACCOUNTING NEW YORK
MILLER 10 10 ACCOUNTING NEW YORK
RAJ 10 10 ACCOUNTING NEW YORK
ADAM 20 20 RESEARCH DALLAS
ALI 20 20 RESEARCH DALLAS
ARHAM 20 20 RESEARCH DALLAS
JONES 20 20 RESEARCH DALLAS
RAHUL 20 20 RESEARCH DALLAS
ALLEN 30 30 SALES CHICAGO
BLAKE 30 30 SALES CHICAGO
JAMES 30 30 SALES CHICAGO
MARTIN 30 30 SALES CHICAGO
TURNER 30 30 SALES CHICAGO
WARD 30 30 SALES CHICAGO
40 OPERATIONS BOSTON

Notice, the last row has a NULL value for staff.ename and staff_deptno. That’s
because no employees work in department 40. The solution uses the WHERE clause
to keep only rows where staff_deptno is NULL (thus keeping only rows from
Department that have no match in Staff).