NOT IN with Subqueries
You'll learn: How to exclude rows by checking if a column's value does not appear in a subquery's result set—and why NULLs can cause surprising problems.
What is NOT IN with a Subquery?
Just as IN lets you include rows where a column matches any value from a subquery, NOT IN does the opposite: it excludes rows where the column matches any subquery result.
Think of it like a guest list for an exclusive event. IN checks "Are you on the list?" while NOT IN asks "Are you not on the list?"—anyone whose name appears gets turned away.
Basic Syntax
SELECT product_name, category_id
FROM products
WHERE category_id NOT IN (
SELECT category_id
FROM discontinued_categories
);
This returns products whose category_id is not among the discontinued categories.
The NULL Pitfall
Here's where NOT IN gets tricky: if the subquery returns any NULL value, the entire NOT IN expression returns NULL, and no rows will match.
Why? SQL uses three-valued logic (TRUE, FALSE, NULL). When comparing a value against a list containing NULL, SQL can't definitively say "this value is not in the list" because NULL means "unknown."
-- ⚠️ Returns ZERO rows if any order has NULL customer_id
SELECT customer_id
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);
Solution: Filter out NULLs in the subquery:
WHERE customer_id NOT IN (
SELECT customer_id
FROM orders
WHERE customer_id IS NOT NULL
);
Key Takeaway: NOT IN excludes rows matching subquery results but fails silently when NULLs are present—always filter NULLs in the subquery or use NOT EXISTS as a safer alternative.