NOT EXISTS

The Negation of EXISTS

NOT EXISTS (subquery) is TRUE exactly when the subquery returns zero rows — the mirror image of EXISTS. Like EXISTS, it only cares about row presence, never about the actual returned values, and it never produces NULL/UNKNOWN — always a clean TRUE or FALSE.

Sample data used throughout this chapter:

employees

departments

Example: Departments With No Employees

sql
SELECT d.name
FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.department = d.name
);

Engineering, Sales, and Marketing all have at least one employee, so NOT EXISTS is FALSE for them. HR has zero matching employees, so NOT EXISTS is TRUE.

Result:

Why NOT EXISTS Is Safer Than NOT IN

This is one of the most important gotchas in SQL, carried over from Chapter 7's discussion of NOT IN and NULLs. Suppose employees.department could contain a NULL (say, an unassigned new hire):

sql
-- Dangerous if the subquery can return a NULL:
SELECT d.name
FROM departments d
WHERE d.name NOT IN (SELECT department FROM employees);

If even one row in employees.department is NULL, then for every candidate d.name, the comparison d.name = NULL evaluates to UNKNOWN rather than FALSE. Because NOT IN is internally a chain of <> ALL, a single UNKNOWN anywhere in that chain poisons the whole condition to UNKNOWN — so the entire query silently returns zero rows, even though the "obviously correct" answer (departments with no matching employee) is non-empty. No error, no warning — just a wrong, empty result set.

NOT EXISTS sidesteps this completely:

sql
SELECT d.name
FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.department = d.name
);

Here, a NULL e.department on some unrelated employee row simply fails to match e.department = d.name for that one comparison, but it doesn't corrupt the entire subquery's TRUE/FALSE outcome the way NOT IN's implicit <> ALL chain does. NOT EXISTS only asks "did any row match at all?" — a NULL just fails to be a match, like any other non-match.

Rule of Thumb

Whenever there is any chance the subquery's column can contain NULL — and in real schemas, that's often — prefer NOT EXISTS over NOT IN. If you must use NOT IN, guard it explicitly: ... NOT IN (SELECT department FROM employees WHERE department IS NOT NULL).

Edge Cases

• NOT EXISTS on a subquery with no outer correlation just asks "is this table (or filtered subset) definitely empty?"
• Double negatives (NOT EXISTS wrapping a subquery that itself uses NOT EXISTS) implement "for all" logic — a classic, if less common, relational-division-style pattern.

Key Takeaways / Q&A

Q: Is NOT EXISTS ever affected by NULLs in the subquery's columns the way NOT IN is? A: No — that's precisely its advantage; it only checks row existence, not value equality against a list that could include NULL.

Q: When should you default to NOT EXISTS over NOT IN? A: Essentially always, unless you can 100% guarantee the subquery's column is NOT NULL — and it costs nothing to just always prefer NOT EXISTS for this pattern.