SQL NOT IN vs NOT EXISTS

"Which rows have no match over there?" is one of the most common questions in SQL: customers with no orders, products nobody bought, employees who manage nobody. It is called an anti-join, and there are three ways to write it. The one most people reach for first, NOT IN, has a trap that Lesson 3.3 · IN and NOT IN warned about. This lesson switches to Employee Directory, a small company's staff list where each row names the employee's manager, and there you can't avoid it.

One NULL, no rows

Every employee's manager_id names their manager, except Dana Whitfield's: she is the CEO, and her manager_id is NULL. So the list of manager ids holds eight real ids and one NULL, and the starter below, which asks for employees not in that list, returns nothing at all.

The reason is that x not in (a, b, null) means x <> a and x <> b and x <> null. Comparing anything with NULL gives NULL (unknown), so the whole condition can be false or unknown but never true, and WHERE keeps only rows where it is true:

select 3 in (1, 2, null) as in_result,
       3 not in (1, 2, null) as not_in_result,
       1 not in (1, null) as found_result

That returns NULL, NULL, and false. A value that is in the list is reliably found; a value that is not in it is never reliably not-found. That is why plain IN is safe here (it finds all eight managers) while NOT IN breaks. No error tells you: the query runs and looks like an honest empty answer.

Three ways to write the anti-join

NOT EXISTS, the exercise below, asks for each employee whether any row names them as a manager. It never compares against the NULL, so it can't be fooled by one. Lesson 10.3 · EXISTS and NOT EXISTS introduced it.

Filtering the NULL out repairs NOT IN, as long as you remember every time:

select employee_id, first_name, last_name
from employees
where employee_id not in (
  select manager_id
  from employees
  where manager_id is not null
)
order by employee_id

A LEFT JOIN that keeps the misses joins each employee to their reports and keeps the rows where no report was found:

select e.employee_id, e.first_name, e.last_name
from employees as e
left join employees as r on r.manager_id = e.employee_id
where r.employee_id is null
order by e.employee_id

All three return the same ten employees. Test a column on the right side that can't be NULL in a real match, such as its key (r.employee_id); otherwise a matched row with a NULL in that column looks like a miss. Without the WHERE, the join returns 27 rows: one per report for each manager, plus one for each of the ten who have none.

Of the three, NOT EXISTS is the one to make a habit: it says what you mean, it is safe whether or not the column allows NULL today, and a column that has none today can gain one tomorrow. The NOT IN with a NULL mistake page runs the wrong and right versions side by side on Garden Shop.