SQL / SUBQUERIES AND CTES
EXISTS versus IN and the NULL that breaks IN
Predict and fix the silent empty result NOT IN gives when its subquery yields NULL, and choose between IN and EXISTS by what each one actually tests.
What you will learn
- Expand NOT IN into x <> a AND x <> b to predict what a NULL in the set will do
- Recognize a silent empty result as the signature of NOT IN over a nullable column
- Rewrite NOT IN as NOT EXISTS, or filter NULLs inside the subquery, to fix it
- Know that a NULL outer value makes NOT EXISTS keep a row that NOT IN would drop
Understanding EXISTS versus IN and the NULL that breaks IN
IN takes a value and compares it against every element of a set, so it is a value comparison and inherits SQL's three-valued logic: the answer can be true, false, or unknown. EXISTS takes a subquery and reports whether it produced at least one row, which is a counting question with only two possible answers. Because a row count is either zero or not zero, EXISTS and NOT EXISTS never return unknown. That difference in what each construct returns is the source of every behavioural difference between them.
NOT IN is defined as a chain of inequalities: x NOT IN (a, b) means x <> a AND x <> b. If b is NULL, the second comparison is unknown, and TRUE AND UNKNOWN is UNKNOWN, so the predicate is unknown for every x that differs from a, and FALSE for x = a. WHERE keeps only rows where the predicate is TRUE, so a set holding a single NULL makes NOT IN return nothing at all, whatever the rest of the data looks like. Plain IN survives the same NULL because it expands with OR instead: TRUE OR UNKNOWN is TRUE, so a genuine match still passes.
NOT EXISTS escapes this because the NULL comparison happens inside the subquery, where m.emp_id = e.id with a NULL emp_id is simply not true, so that row is never returned and contributes nothing. Zero returned rows is a definite answer, so NOT EXISTS is TRUE and the outer row survives. The mirror case is worth knowing: when the outer value is NULL, every comparison in the subquery is unknown, no rows come back, and NOT EXISTS keeps the row, while NULL NOT IN (...) is unknown and drops it. Treat the two forms as interchangeable only when both sides are known to be non-null.
The practical rule is to decide which question you are asking. If you want membership in a small literal list of known values, IN reads better. If you want to test whether related rows exist, especially in the negative, write EXISTS or NOT EXISTS and keep the comparison inside the subquery where NULLs can only fail to match rather than poison a conjunction.
-- employees and who manages
CREATE TABLE employees (id INTEGER, name TEXT);
CREATE TABLE managers (emp_id INTEGER);
INSERT INTO employees VALUES (1, 'Ada'), (2, 'Bree'), (3, 'Cyd');
INSERT INTO managers VALUES (1), (NULL); -- one manager id is unknown
-- Intended: employees who are not managers
SELECT name FROM employees
WHERE id NOT IN (SELECT emp_id FROM managers);
-- Same intent, asked as a question about row existence
SELECT name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM managers m WHERE m.emp_id = e.id);IN compares values under three-valued logic while EXISTS tests row presence under two-valued logic, so one NULL in the set makes NOT IN unprovable and therefore empty.
Worked examples
Printing the third truth value
Makes the unknown result of IN and NOT IN visible instead of letting it hide as an empty cell.
SELECT
CASE WHEN 2 IN (1, NULL) THEN 'TRUE'
WHEN NOT (2 IN (1, NULL)) THEN 'FALSE'
ELSE 'UNKNOWN' END AS in_2,
CASE WHEN 2 NOT IN (1, NULL) THEN 'TRUE'
WHEN NOT (2 NOT IN (1, NULL)) THEN 'FALSE'
ELSE 'UNKNOWN' END AS not_in_2,
CASE WHEN 1 NOT IN (1, NULL) THEN 'TRUE'
WHEN NOT (1 NOT IN (1, NULL)) THEN 'FALSE'
ELSE 'UNKNOWN' END AS not_in_1;Example explained
Line 12 IN (1, NULL) expands to 2 = 1 OR 2 = NULL, and FALSE OR UNKNOWN is UNKNOWN.
Line 22 NOT IN (1, NULL) expands to 2 <> 1 AND 2 <> NULL, and TRUE AND UNKNOWN is UNKNOWN, so a WHERE clause would discard that row.
Line 31 NOT IN (1, NULL) is a definite FALSE, because 1 <> 1 is FALSE and FALSE AND UNKNOWN is FALSE.
Line 4CASE is needed to label the result, since an unknown boolean renders as a blank cell in most clients.
Putting the NULL guard in the right place
Shows that filtering NULLs inside the subquery repairs NOT IN, while filtering the outer column changes nothing.
CREATE TABLE employees (id INTEGER, name TEXT);
CREATE TABLE managers (emp_id INTEGER);
INSERT INTO employees VALUES (1, 'Ada'), (2, 'Bree'), (3, 'Cyd');
INSERT INTO managers VALUES (1), (NULL);
-- guard inside the subquery
SELECT name FROM employees
WHERE id NOT IN (SELECT emp_id FROM managers WHERE emp_id IS NOT NULL);
-- guard on the outer column instead
SELECT name FROM employees
WHERE id IS NOT NULL
AND id NOT IN (SELECT emp_id FROM managers);Example explained
Line 1WHERE emp_id IS NOT NULL runs inside the subquery, so the set handed to NOT IN is just (1).
Line 2With no NULL in that set, id <> 1 is TRUE or FALSE for every employee, and Bree and Cyd come back.
Line 3The second query filters id, which was never NULL, so the set still contains NULL and the result is still empty.
Line 4The guard only helps where the NULL actually lives, which is the subquery's output.
When the NULL is on the outer side
Demonstrates that NOT IN and NOT EXISTS disagree when the compared value itself is NULL, even with a NULL-free subquery.
CREATE TABLE people (id INTEGER);
CREATE TABLE tasks (id INTEGER, owner_id INTEGER);
INSERT INTO people VALUES (10), (11);
INSERT INTO tasks VALUES (1, 10), (2, 99), (3, NULL);
SELECT id, 'not in' AS via
FROM tasks
WHERE owner_id NOT IN (SELECT id FROM people)
UNION ALL
SELECT id, 'not exists'
FROM tasks t
WHERE NOT EXISTS (SELECT 1 FROM people p WHERE p.id = t.owner_id)
ORDER BY via, id;Example explained
Line 1people holds no NULLs, so the NOT IN branch is not affected by the problem from the main example.
Line 2Task 2 shows up in both branches, because 99 is genuinely absent and both forms agree on non-null values.
Line 3Task 3 has owner_id NULL, so NULL <> 10 AND NULL <> 11 is unknown and NOT IN drops it.
Line 4For that same row the subquery finds nothing, since p.id = NULL is never true, so NOT EXISTS is TRUE and task 3 is kept.
Important notes
NOT IN is shorthand for <> ALL and IN for = ANY, so this behaviour comes from the comparison operator and three-valued logic, not from the IN keyword itself.
Most optimizers cannot turn NOT IN over a nullable column into an anti-join, so NOT EXISTS is often faster as well as more predictable; when the column is declared NOT NULL the two usually plan identically.
Common mistakes
Reacting to an empty NOT IN result by rechecking the join keys and the data: nothing is wrong with either, no error is raised, and the real cause is a single NULL in the subquery's output.
Adding IS NOT NULL to the outer query instead of inside the subquery, which leaves the NULL in the compared set and keeps returning zero rows.
Converting to NOT EXISTS but forgetting the correlation, as in NOT EXISTS (SELECT 1 FROM managers): that subquery returns rows for every outer row, so the predicate is FALSE everywhere and the result is empty again.
Try it yourself
Change, predict, then run
Create workers(id) holding 1, 2, 3 and shifts(worker_id) holding 3 and NULL, then write the NOT IN query for workers with no shift and confirm it returns zero rows. Fix it twice, once with NOT EXISTS and once by filtering NULLs inside the subquery, and check both return 1 and 2.
Open the SQL workspaceCheck your understanding
A subquery returns the two values 5 and NULL. For which outer values of x can x NOT IN (subquery) evaluate to TRUE?
- None, because with a NULL in the set the predicate can only be FALSE or unknown
- Any x other than 5, since 5 is the only real value in the set
- Only x = 5, since that is the value the comparison can resolve
- Any x that is itself NULL, since NULL matches NULL
Show answer
The predicate becomes x <> 5 AND x <> NULL. The second conjunct is always unknown, so the result is FALSE when x = 5 and unknown otherwise, never TRUE, and WHERE keeps only TRUE. Option 1 is tempting because that is exactly what NOT IN means when the set has no NULL, but one NULL removes the ability to prove non-membership.