Recommended Free Tools
A single NULL returned by a NOT IN subquery can make otherwise nonmatching rows fail the WHERE filter. SQL comparisons with NULL can be UNKNOWN, and WHERE keeps only rows whose condition is TRUE. Filter unknown values out of the comparison set, or use NOT EXISTS when the rule you mean is “no matching row exists.”
How one NULL can make NOT IN return no rows
Consider a customers table and an orders table. If even one orders.customer_id is NULL, this query can return no customers, even if many customer IDs have no matching order:
SELECT c.customer_id
FROM customers AS c
WHERE c.customer_id NOT IN (
SELECT o.customer_id
FROM orders AS o
);x NOT IN (SELECT y ...) means that x must be unequal to every value the subquery returns. If one returned value is NULL, the comparison against it can be UNKNOWN, not TRUE. A nonmatching customer therefore does not satisfy the predicate as true, so the WHERE clause removes that row. An actual equal value makes the exclusion false; a right-side NULL is especially troublesome for values that have no equal match.
PostgreSQL 18 documents that NOT IN yields null when the left expression is null, or when there is no equal right-side value and at least one right-side row is null: PostgreSQL: Subquery Expressions. Microsoft likewise explains that comparisons involving null can return UNKNOWN, and recommends IS NULL or IS NOT NULL to test for nullness: Microsoft Learn: NULL and UNKNOWN (Transact-SQL).
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →#1 Best Overall
Choose a fix based on what NULL means
Filter NULLs out of the exclusion set
Use this when the intended comparison set is made up only of known customer IDs. An unknown order customer ID is not a known ID to exclude:
SELECT c.customer_id
FROM customers AS c
WHERE c.customer_id NOT IN (
SELECT o.customer_id
FROM orders AS o
WHERE o.customer_id IS NOT NULL
);This preserves NOT IN while ensuring the subquery does not supply a null comparison value. It does not by itself decide what to do with a null c.customer_id; handle that outer-side case explicitly if such rows are possible.
Use NOT EXISTS when the rule is absence of a match
If the business question is whether any order row has the same known customer ID, write that rule directly as a correlated subquery:
SELECT c.customer_id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);An unrelated null in orders.customer_id does not poison this predicate: it does not make the equality true, so it does not count as a matching row. But if c.customer_id is null, the equality is not true for any order row; NOT EXISTS can therefore include that customer. Add AND c.customer_id IS NOT NULL if unknown customer IDs should be excluded. If they should be included or reported separately, express that policy deliberately instead.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Check both sides of the comparison
A null in the subquery result and a null in the outer key are distinct cases. Filtering nulls on the right prevents them from making nonmatching NOT IN comparisons unknown. It does not make a null outer key into a known value. With a nonempty right-hand set, NULL NOT IN (...) is not true; a NOT EXISTS equality check, by contrast, may find no true match and include that outer row.
Decide how unknown keys should be handled before choosing the query:
- Exclude unknown outer keys: add an explicit
IS NOT NULLcondition for the outer key. - Include them as having no known match: use the correlated
NOT EXISTSlogic, understanding that null keys do not match through ordinary equality. - Report them separately: query them with
IS NULLrather than treating an unknown key as an ordinary unmatched ID.
Account for empty sets and SQL dialect differences
Do not assume every database handles every edge case or syntax identically. SQLite’s expression documentation gives an IN/NOT IN result matrix and notes that NOT IN against an empty right-hand set is true even when the left expression is null: SQLite: Expressions. Empty-list syntax and other dialect details can also vary. Name and test against the database engine and version you actually use.
Compare the two patterns against your intended rule
| Question | NOT IN with a NULL filter | Correlated NOT EXISTS |
|---|---|---|
| Can a null on the subquery side affect the result? | Not after the subquery filters it with IS NOT NULL. |
An unrelated null does not count as a matching row under ordinary equality. |
| What happens if the outer key is null? | With a nonempty comparison set, the predicate is not true; add explicit handling if needed. | The equality is not true for any row, so the outer row can be included. |
| Best fit for the intended rule | Exclude values absent from a set of known, non-null keys. | Keep rows for which no row satisfying the match condition exists. |
| Engine behavior | Verify dialect syntax and empty-set behavior. | Verify dialect syntax and the intended null-key policy. |
Neither form is a universal drop-in replacement for the other when null keys matter. If performance is also a concern, inspect the execution plan on the target database rather than assuming one pattern is faster.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
