What is the difference between NOT IN and NOT EXISTS when NULLs are involved?

Q: What is the difference between NOT IN and NOT EXISTS when NULLs are involved?

**Target Audience:** Anyone who has written "find the rows that have no match" and been surprised by an empty result; beginners meeting NULL logic for the first time. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page; MySQL notes cite its manual. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and plan below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A shop owner asks an assistant: *"Which of our five regular customers have never bought anything?"* The assistant gets the pile of receipts. Most receipts have a customer name on them. One receipt is smudged: the name is unreadable. A careful assistant who takes the question literally says: *"I

The problem

Question presented to candidate: "We need the customers who have never ordered. A colleague wrote WHERE id NOT IN (SELECT customer_id FROM orders) and it returns no rows at all, although Dara and Eli clearly have no orders. NOT EXISTS works. Explain exactly why, and how you would write it."

What a strong answer should cover:

SQL uses three-valued logic: comparing with NULL gives UNKNOWN, and WHERE keeps only TRUE
x NOT IN (a, b, NULL) means x <> a AND x <> b AND x <> NULL; the last term is UNKNOWN, so the whole predicate can never be TRUE and the query returns nothing
NOT EXISTS only asks whether a matching row exists; a NULL never matches, so it is simply ignored
The outer side matters too: a NULL outer value is excluded by NOT IN but kept by NOT EXISTS
Fixes: prefer NOT EXISTS; or filter WHERE customer_id IS NOT NULL in the subquery; or LEFT JOIN ... IS NULL; declare the column NOT NULL if it should be
Performance: PostgreSQL turns NOT EXISTS into an anti join but keeps NOT IN as a filter with a subplan, which degrades badly when the subquery does not fit in memory

Code / implementation expected: Yes — show NOT IN returning nothing and NOT EXISTS returning the right customers on data with one NULL.

Schema used by the examples:

null
anti-join

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Anyone who has written "find the rows that have no match" and been surprised by an empty result; beginners meeting NULL logic for the first time. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page; MySQL notes cite its manual.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and plan below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A shop owner asks an assistant: "Which of our five regular customers have never bought anything?" The assistant gets the pile of receipts. Most receipts have a customer name on them. One receipt

The solution is hidden so you can try first

12 min read · 12 sections

Run the code

Edit the query and run it against the tables for this question.

SQLThe bug in one row: NOT IN finds 0 customers without orders, NOT EXISTS finds 2

SQLThree-valued logic: IN and NOT IN against a list that contains NULL

SQLThe other direction: an order with a NULL customer_id is dropped by NOT IN but kept by NOT EXISTS

PostgreSQL: with a large subquery and little work_mem, NOT IN falls back to a per-row SubPlan

CREATE TABLE big_orders AS
  SELECT g AS id, g % 50000 AS customer_id
  FROM generate_series(1, 200000) AS g;
ANALYZE big_orders;
SET work_mem = '64kB';

EXPLAIN (COSTS OFF)
SELECT id, name FROM customers
WHERE id NOT IN (SELECT customer_id FROM big_orders);

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.