IntermediateSQL · 25 min

Anti-Join: Intermediate

Anti-joins are how you find what is not there. Users who never purchased. Products that have never been viewed. Sessions with no conversion. Every dashboard that surfaces 'missing' or 'inactive' entities is powered by an anti-join. The question is small and the SQL is short. The reason it ends up in interview loops is that there are three ways to write it, two of those ways have silent failure modes, and choosing between them tells the interviewer whether you have shipped reporting that finance has audited. This lesson is about making that choice cleanly.

Recognize anti-join triggers on first read and reach for the right shape immediately

Write the three canonical forms (LEFT JOIN WHERE NULL, NOT EXISTS, NOT IN) without hesitation

Articulate the NULL trap that makes NOT IN unsafe on real production data

Pick the form that matches the engine, the dialect, and the data shape, and defend the choice

NOT EXISTS vs NOT IN vs LEFT JOIN IS NULL

Daily Life

Interviews

Spot anti-join patterns: users who never purchased, products never viewed, events with no match.

Here is the shape that recurs in every reporting interview. The interviewer has a customers table and an orders table. They say: 'find customers who have never placed an order.' The query is one of three forms, each four to six lines. The interview signal is not whether you can write the query. It is whether you pick the form that holds up under the conditions of the real data.

You are being tested on an anti-join when you hear:

▸"users who have never X"
▸"products that have never been Y"
▸"customers without a Z"
▸"events with no matching ..."
▸"records present in A but not in B"
▸Any question framed as the absence of a relationship

Why the choice between forms matters

All three forms answer the same English question. They are not interchangeable. NOT IN has a NULL trap that returns zero rows on the wrong input. LEFT JOIN WHERE NULL is verbose but predictable. NOT EXISTS is the cleanest semantically but unfamiliar to candidates who learned SQL from join-heavy tutorials. The interviewer is not asking which one you like. They are asking which one you would write at three in the morning when the dashboard is on fire and the source table just learned how to produce NULLs.

Three forms, three failure modes:

▸NOT IN: ships zero rows when the subquery has a NULL
▸LEFT JOIN WHERE NULL: verbose, requires picking which column to check IS NULL
▸NOT EXISTS: NULL-safe, reads like the English question, default reach

Default to NOT EXISTS. The reason is that it handles NULLs in the subquery correctly without an extra clause, reads as 'this thing does not exist,' which matches the English question, and produces the same query plan as NOT IN on most modern engines without the NULL hazard. Saying 'I default to NOT EXISTS because it is NULL-safe and reads like the question' is the answer the interviewer is checking for.

What the interviewer is actually testing

This is the SQL screen that separates the candidate who has only built queries against perfectly clean tutorial data from the candidate who has had a dashboard go to zero in production because the source system started returning NULLs in a column that was never NULL before. The right form anticipates that reality. The wrong form does not. The interviewer is reading your answer for evidence that you have been on the wrong side of that incident.

•Answer that ships the bug

Writes NOT IN without checking whether the subquery can return NULL
Dashboard returns the right rows in dev (no NULLs) and zero rows in prod (one NULL)
Cannot explain the difference when on-call asks
Gets the rollback PR after the postmortem

✓Answer that holds up

Defaults to NOT EXISTS because the NULL handling is correct by construction
Explains the NOT IN trap unprompted
Defends the choice with the three-valued-logic argument
Has the LEFT JOIN WHERE NULL form ready as a fallback when the engine plans NOT EXISTS poorly

The NOT IN NULL Trap That Returns Zero Rows

Daily Life

Interviews

Write all three anti-join forms and know which the interviewer expects based on dialect and clarity.

Three forms produce the same answer on clean data. Knowing all three, and being able to deliver each one from memory, is the floor. Knowing which one to reach for is what the interviewer is scoring.

What the interviewer scores when you choose between them:

▸Naming all three forms unprompted: floor
▸Defaulting to NOT EXISTS with a correctness reason: pass
▸Anticipating the NOT IN NULL trap before being asked: strong-hire signal
▸Naming the plan equivalence on modern engines: depth signal

Form 1: LEFT JOIN WHERE NULL

Join the two tables with a LEFT JOIN, then filter for rows where the right side is NULL. The NULL appears for rows in the left table that did not match any row in the right table. This is the form most candidates default to because it is the most explicit: you can see the join, you can see the filter, and you can trace the row count by hand.

	/* Customers who have never placed an order */
	SELECT
	c.customer_id,
	c.name
	FROM customers AS c
	LEFT JOIN orders AS o
	ON o.customer_id = c.customer_id
	WHERE o.customer_id IS NULL

The trace: the LEFT JOIN keeps every customer, attaching their orders where they exist and NULLs where they do not. The WHERE filter keeps only the rows where the orders side is NULL, which is exactly the set of customers with no matching order. Clean shape, predictable plan, works on every SQL engine in production use.

Form 2: NOT EXISTS

Express the absence directly. For each customer, check whether any order exists for that customer; if not, return the customer. The subquery is correlated, but the engine optimizes it down to the same kind of hash anti-join that LEFT JOIN WHERE NULL produces.

	/* Customers who have never placed an order (NOT EXISTS form) */
	SELECT
	c.customer_id,
	c.name
	FROM customers AS c
	WHERE NOT EXISTS (
	SELECT
	1
	FROM orders AS o
	WHERE o.customer_id = c.customer_id
	)

The reading is direct: 'customers where no order exists with this customer_id.' That matches the English of the question, which makes the query easier to maintain. The other advantage: NULL handling. If the orders.customer_id column can contain NULL, the LEFT JOIN form still works correctly. The NOT EXISTS form also works correctly. The NOT IN form, as you are about to see, does not.

Form 3: NOT IN, and how to choose between the three

NOT IN pulls the list of customer_ids that appear in orders, and excludes them. This is the form most candidates write first because it reads the most like English. It is also the form that ships the silent bug.

	/* Customers who have never placed an order (NOT IN form , read on) */
	SELECT
	c.customer_id,
	c.name
	FROM customers AS c
	WHERE c.customer_id NOT IN (
	SELECT
	o.customer_id
	FROM orders AS o
	)

On clean data, this returns the right answer. On data where any row in orders has a NULL customer_id, this returns zero rows. The next section explains why. For now, mark NOT IN as 'works in development, fails in production.' Many engineers have learned this the hard way. If you remember one rule from this lesson, it is this: default to NOT EXISTS. The form reads correctly, the NULL behavior is safe, and the query plan is identical to the other forms on every modern optimizer. Use LEFT JOIN WHERE NULL when the team's house style favors it, or when you need to keep extra columns from the right side that the EXISTS subquery cannot expose. Never use NOT IN unless you have explicitly verified that the subquery cannot produce NULL, and you have written a comment saying so.

TIP

When the interviewer asks which form you would pick, do not say 'whichever is faster.' All three produce nearly identical plans on a modern engine. Say 'NOT EXISTS, because it handles NULLs correctly and reads like the question.' That answer combines a correctness argument with a maintainability argument. Picking on speed alone is the answer that signals you have not been bitten yet.

Choosing the Form the Interviewer Expects

Daily Life

Interviews

Explain why NOT IN returns zero rows when the subquery contains a NULL, and how NOT EXISTS avoids this.

This section is the reason this question lives in interview loops. NOT IN with a NULL in the subquery returns zero rows. Not 'some' rows. Not 'wrong' rows. Zero. The dashboard goes to zero. The on-call gets paged. The fix is one character: switch NOT IN to NOT EXISTS. The trap exists because of how SQL's three-valued logic interprets NOT IN.

Why NOT IN with a NULL returns zero

SQL has three values: TRUE, FALSE, and UNKNOWN. Comparing anything to NULL returns UNKNOWN, not FALSE. NOT IN expands to a chain of inequalities: 'x NOT IN (1, 2, NULL)' becomes 'x != 1 AND x != 2 AND x != NULL.' That last comparison is UNKNOWN. UNKNOWN AND TRUE is UNKNOWN. UNKNOWN is not TRUE, so the row is excluded from the result. Every row is excluded because the chain always contains an UNKNOWN.

How NOT IN evaluates with a NULL in the subquery

▸x NOT IN (1, 2, NULL) expands to x != 1 AND x != 2 AND x != NULL
▸x != NULL is UNKNOWN, not TRUE or FALSE
▸TRUE AND TRUE AND UNKNOWN is UNKNOWN
▸UNKNOWN is not TRUE, so the row is excluded
▸Every row is excluded by the same logic. Result set is empty.

The bug, demonstrated

	INSERT INTO orders(customer_id) VALUES(NULL) ;



	SELECT
	customer_id
	FROM customers
	WHERE customer_id NOT IN(SELECT customer_id FROM orders) ;

Walk through a customer who has never ordered. Their customer_id is 5. The subquery returns (1, 2, 3, NULL). The NOT IN evaluates: 5 != 1 (TRUE), 5 != 2 (TRUE), 5 != 3 (TRUE), 5 != NULL (UNKNOWN). The conjunction is TRUE AND TRUE AND TRUE AND UNKNOWN, which is UNKNOWN. UNKNOWN is not TRUE, so customer 5 is excluded. Customer 5 has never ordered, but the query excludes them anyway. Every customer gets excluded by the same logic.

Why NOT EXISTS avoids the trap

NOT EXISTS does not iterate through values; it asks whether the correlated subquery returns any row. If the subquery returns a NULL row, EXISTS still returns TRUE for that NULL row, and NOT EXISTS returns FALSE for that customer specifically. The NULL row does not contaminate the entire result. Other customers' NOT EXISTS evaluations are independent: each is asked individually, 'does any order exist with this customer_id?'

•NOT IN behavior with NULL

Single NULL in the subquery returns zero rows total
The bug is silent: no error, no warning, just an empty result
Reproduces only on the data that has the NULL
Standard interview trap; standard production incident

✓NOT EXISTS behavior with NULL

NULL in the subquery is treated row-by-row, not as a poison value
Correctly excludes only the customers who have a matching order
Predictable on every data shape
The reason it should be your default

The reason this trap exists in the language is that SQL was designed when 'unknown' was treated as a third truth value with explicit semantics. Every other reasonable interpretation would either silently corrupt data (treating NULL as a real value) or break backwards compatibility. Knowing this history is not required, but mentioning that NOT IN's behavior is a consequence of three-valued logic, not a bug, signals depth.

The fix when you cannot avoid NOT IN

If house style or a dialect quirk forces NOT IN, filter the subquery to exclude NULLs explicitly. 'NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL)' is correct. The cost is that the team's code review now relies on every author remembering to add that filter. NOT EXISTS does not require the filter. Pick the form that removes the human error.

TIP

When the interviewer asks 'what happens if there is a NULL in orders.customer_id?', the answer that gets the credit is: 'NOT IN returns zero rows because of three-valued logic. NOT EXISTS is unaffected. This is the main reason I default to NOT EXISTS in production.' Three sentences. Each one is a correctness argument.

At Shopify in 2021, the 'merchants who have never processed a refund' dashboard suddenly read zero on a Wednesday morning. The query was a NOT IN against the refunds table. A new ingestion path for a refund-source experiment had landed earlier that day and inserted a single test row with a NULL merchant_id; the dashboard's NOT IN evaluated to UNKNOWN for every merchant after that, and the result was zero rows. The on-call rolled the ingestion back and the dashboard recovered within twenty minutes. The postmortem rule was 'no NOT IN in any analytics query against a production table; use NOT EXISTS with an explicit join condition, and the CI lint blocks NOT IN against any table that isn't a fixed enum.' The rubric for the Shopify SQL screen has, since that incident, included the question 'what happens if there's a NULL in the subquery' as the first follow-up after the candidate writes a NOT IN; candidates who default to NOT EXISTS skip that follow-up entirely.

Situation	Phrasing that flatlines	Phrasing that lands
You see 'customers who never X'	"I'll use NOT IN."	"This is an anti-join. I default to NOT EXISTS because it handles NULLs in the subquery correctly and reads like the English question; NOT IN with a single NULL in the inner result returns zero rows."
The interviewer asks 'why not NOT IN'	"It's slower."	"Three-valued logic. x NOT IN (..., NULL) expands to a chain of inequalities; the comparison to NULL is UNKNOWN, UNKNOWN AND TRUE is UNKNOWN, every row is excluded. The bug is silent: empty result, no error."
The interviewer asks 'when LEFT JOIN WHERE NULL'	"When I feel like it."	"When the team's house style prefers it, or when I need columns from the right side that EXISTS can't expose. Otherwise NOT EXISTS reads closer to the question and avoids the IS-NULL-on-which-column question."
The interviewer asks 'how does it scale'	"It's fast."	"All three forms compile to the same hash anti-join on modern optimizers. The cost lever is whether orders.customer_id is indexed; without an index, the plan degrades to a scan. For a dashboard, I'd materialize the anti-join result on the orders pipeline cadence rather than recompute every refresh."
The interviewer adds a temporal window	"I'll add BETWEEN."	"BETWEEN prevents a pure hash anti-join; the optimizer falls back to nested-loop or merge. On a billion-row source I'd pre-aggregate page-B visits into a daily-window table first so the anti-join becomes an equi-join again."

Multi-Column Anti-Joins and Composite Keys

Daily Life

Interviews

Handle composite key anti-joins (e.g., users who visited page A but not page B on the same day).

The escalation: 'find users who visited page A but never page B on the same day.' One condition is no longer enough; the anti-match has to consider multiple columns at once. The form choice matters again, and the same NULL trap reappears in a slightly different shape.

The multi-column version

Express the absence with NOT EXISTS, joining the subquery on every column that defines the match.

All three forms work. NOT EXISTS is the safe default; LEFT JOIN WHERE NULL is the verbose fallback.
NOT EXISTS scales cleanly. NOT IN multi-column requires tuple syntax and still hits the NULL trap on every column.
NOT EXISTS is the only clean option. The BETWEEN clause turns the anti-join into a non-equi join, killing the hash plan.

	/* Users who visited page A but never page B on the same day */
	SELECT DISTINCT
	a.user_id,
	a.visit_date
	FROM page_visits AS a
	WHERE a.page = 'A'
	AND NOT EXISTS (
	SELECT
	1
	FROM page_visits AS b
	WHERE b.user_id = a.user_id
	AND b.visit_date = a.visit_date
	AND b.page = 'B'
	)

The subquery joins on three columns: user_id, visit_date, and page. The visit_date join is what enforces 'on the same day.' Without it, a user who visited page A today and page B last month would be excluded, which is wrong. The interviewer will sometimes ask you to walk through a test case with one user who visited both pages on different days; the multi-column join is what handles that correctly.

The LEFT JOIN form scales awkwardly here

	/* LEFT JOIN form: works but reads heavier */
	SELECT DISTINCT
	a.user_id,
	a.visit_date
	FROM page_visits AS a
	LEFT JOIN page_visits AS b
	ON b.user_id = a.user_id
	AND b.visit_date = a.visit_date
	AND b.page = 'B'
	WHERE a.page = 'A'
	AND b.user_id IS NULL

The LEFT JOIN form needs four conditions and a WHERE clause filter, and the IS NULL check requires picking a non-nullable column from the right side. NOT EXISTS is shorter, reads closer to the English, and avoids the 'which column do I check for IS NULL' question entirely. For multi-column anti-joins, NOT EXISTS pulls further ahead of the alternatives.

The NOT IN form does not generalize

NOT IN compares one column at a time. Multi-column NOT IN would need 'WHERE (user_id, visit_date) NOT IN (SELECT user_id, visit_date FROM ...)' which only some engines support, and which still carries the NULL trap on every column in the tuple. For multi-column anti-joins, NOT IN is effectively off the table.

The variant interviewers like: anti-join with a temporal window

'Find users who visited page A but did not visit page B within seven days.' The multi-column join becomes a join with a date-range condition.

	/* Users who visited A but no B within seven days after */
	SELECT
	a.user_id,
	a.visit_date AS a_visit
	FROM page_visits AS a
	WHERE a.page = 'A'
	AND NOT EXISTS (
	SELECT
	1
	FROM page_visits AS b
	WHERE b.user_id = a.user_id
	AND b.page = 'B'
	AND b.visit_date BETWEEN a.visit_date
	AND a.visit_date + INTERVAL '7 days'
	)

The subquery now has a BETWEEN range, which makes the anti-join non-equi. The engine cannot use a pure hash anti-join plan here; it falls back to a nested loop or merge anti-join. Performance shifts noticeably on large tables. Mention this constraint when the interviewer asks about scale: 'the BETWEEN clause prevents a hash anti-join, so on a billion-row visits table I would pre-aggregate page B's visits into a daily-window table first.'

TIP

Whenever the anti-join condition becomes range-based or multi-column, NOT EXISTS is almost certainly the right reach. The form was designed for arbitrary subquery predicates, while NOT IN was designed for the simpler 'value in a list' case. The interviewer is watching for whether you scale your choice with the complexity of the predicate.

When the "Missing" Set Is Itself Filtered

Daily Life

Interviews

Discuss query plan differences between the three forms and when the optimizer treats them identically.

After correctness, the interviewer escalates to performance. The follow-up: 'how does the query planner execute this on a billion-row table?' All three forms can produce the same physical plan on a modern optimizer; they can also fall back to nested loops on an older one. Knowing when each plan applies, and what to do when the optimizer picks the slow one, is the depth this question rewards at the mid-and-above level.

The three execution plans you should know

Hash anti-join: build a hash table of the right side, scan the left side, emit each left row that does not have a hash match. O(N + M), memory bounded by the size of the right side. Merge anti-join: sort both inputs on the join key, walk them in tandem, emit left rows that do not match. O(N log N + M log M), good when inputs are already sorted. Nested-loop anti-join: for each left row, scan the right side looking for a match. O(N * M), the catastrophic plan.

•Hash anti-join

Fastest plan for equi-joins with no range conditions
Memory cost is the size of the right side's hash table
Modern engines default to this when both sides fit in memory
Produced by all three anti-join forms when the optimizer is well-tuned

✓Nested-loop anti-join

Fallback when the right side is small or no usable index exists
Catastrophic on large tables because cost is multiplicative
Produced by older engines that do not rewrite correlated subqueries
Sign you need to rewrite the query or add an index

When the optimizer rewrites NOT IN, LEFT JOIN, and NOT EXISTS to the same plan

On Postgres 12+, Snowflake, BigQuery, and SQL Server, the planner recognizes that all three forms express an anti-join and emits the same physical operator. The choice between forms then becomes a question of readability, NULL safety, and dialect, not performance. On MySQL 5.7 and older, the planner does not always rewrite; NOT IN may force a nested loop, NOT EXISTS may use a semi-join hint, and LEFT JOIN WHERE NULL may behave differently again. If the interview is on a specific engine, ask which version; the answer changes the recommendation.

What to say when the plan is bad

Three moves. First: confirm the plan with EXPLAIN. Saying 'I would EXPLAIN this before optimizing' is the move that proves you do not guess. Second: ensure the join column has an index on the right side. If orders.customer_id has no index, every anti-join form pays the full table scan cost. Third: if the right side is huge and indexes do not help (because the engine still needs to scan it), pre-aggregate into a smaller derived table and anti-join against the derived table instead. 'I would build a one-column DISTINCT customer_id table from orders, with an index, and anti-join against that.'

Scale playbook for anti-joins at billion-row volume:

▸Confirm the plan with EXPLAIN before any optimization
▸Ensure the right-side join column has an index
▸Precompute the anti-join result if it powers a dashboard with high refresh cadence
▸For multi-column or range-based anti-joins, NOT EXISTS is the only clean reach

The scale conversation, condensed

'On a billion-row orders table, what changes?' The answer is layered. First, the right side may not fit in memory for a hash anti-join; the engine spills to disk, which is acceptable but slower. Second, the index on customer_id becomes mandatory; without it, the plan degrades to a nested loop. Third, if the anti-join is multi-column or range-based, no single index can support it cleanly; consider materializing the right-side pre-aggregate as its own table. Fourth, if this query powers a dashboard that refreshes hourly, the right answer is to precompute the anti-join result into a cached table and serve from that, not to recompute the anti-join every refresh.

Mention precomputation unprompted when the interviewer asks about scale at the dashboard level. 'For a dashboard that refreshes hourly, I would materialize the customers-without-orders set as its own table, refresh it on the orders pipeline cadence, and serve the dashboard from the materialized table.' This sentence is the architectural beat that earns the credit at this level.

The closing summary

Close with a four-sentence wrap. 'This is an anti-join. I default to NOT EXISTS because it handles NULLs correctly and reads like the English question. NOT IN ships zero rows when the subquery contains a NULL, which is the classic production bug; LEFT JOIN WHERE NULL is the verbose fallback for teams that prefer it. For scale, I would confirm the plan with EXPLAIN, ensure the join column is indexed on the right side, and consider materializing the result when this powers a dashboard.' Four sentences. Pattern, default tool, NULL trap, scale. The shape generalizes to every anti-join question.

❯❯❯PUTTING IT ALL TOGETHER

> You are in an Apple data engineering interview. The interviewer asks: 'Find customers who have never placed an order. The orders table sometimes has NULL customer_id from a bad ingestion run.'

You say: 'This is an anti-join. I default to NOT EXISTS because it handles NULLs correctly and reads like the question.'

You write the query: SELECT FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id).

You explain the NOT IN trap: 'NOT IN with a NULL in the subquery returns zero rows because of three-valued logic. The chain x != 1 AND x != 2 AND x != NULL evaluates to UNKNOWN, which is not TRUE, so every row is excluded. NOT EXISTS is unaffected because it evaluates row by row.'

Follow-up: 'Same question, but find customers who visited but never purchased on the same day.' You say: 'Multi-column anti-join. NOT EXISTS with the join condition on user_id and visit_date. NOT IN does not generalize cleanly to multi-column.'

Closing: 'On a billion-row orders table, I would confirm the plan with EXPLAIN, ensure the index on customer_id exists on the right side, and consider materializing the anti-join result if this powers a dashboard with high refresh cadence.'

KEY TAKEAWAYS

Default to NOT EXISTS for anti-joins: it is NULL-safe without an extra clause, it reads like the English question, and modern optimizers compile it to the same anti-join operator as the other two forms.

NOT IN returns zero rows, not wrong rows, when the subquery contains a single NULL. Comparing 5 to NULL yields UNKNOWN, the conjunction collapses to UNKNOWN, and every candidate row is excluded. This is three-valued logic working as designed, not an engine bug.

If house style forces NOT IN, the fix is SELECT customer_id FROM orders WHERE customer_id IS NOT NULL inside the subquery. That correctness now depends on every future author remembering the filter, which is why NOT EXISTS is the safer default.

For multi-column anti-joins, NOT EXISTS is the only form that scales cleanly. Join the subquery on every column that defines the match, including b.visit_date = a.visit_date to enforce same-day semantics, and skip the question of which right-side column to test for IS NULL.

A range predicate such as b.visit_date BETWEEN a.visit_date AND a.visit_date + INTERVAL '7 days' makes the anti-join non-equi, so the engine cannot use a hash anti-join and falls back to nested loop or merge. Pre-aggregate the right side into a daily-window table when the source is large.

For scale, confirm the plan with EXPLAIN before optimizing, index the join column on the right side to avoid the O(N * M) nested loop, and materialize the anti-join result into its own table when it powers a dashboard that refreshes on a fixed cadence.

Finding what is NOT there is harder than finding what is

Category: SQL
Difficulty: intermediate
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: NOT EXISTS vs NOT IN vs LEFT JOIN IS NULL, The NOT IN NULL Trap That Returns Zero Rows, Choosing the Form the Interviewer Expects, Multi-Column Anti-Joins and Composite Keys, When the "Missing" Set Is Itself Filtered

Lesson Sections

NOT EXISTS vs NOT IN vs LEFT JOIN IS NULL (concepts: sqlNullInJoins)
Here is the shape that recurs in every reporting interview. The interviewer has a customers table and an orders table. They say: 'find customers who have never placed an order.' The query is one of three forms, each four to six lines. The interview signal is not whether you can write the query. It is whether you pick the form that holds up under the conditions of the real data. Why the choice between forms matters Default to NOT EXISTS. The reason is that it handles NULLs in the subquery correct
The NOT IN NULL Trap That Returns Zero Rows (concepts: sqlNullInJoins)
Three forms produce the same answer on clean data. Knowing all three, and being able to deliver each one from memory, is the floor. Knowing which one to reach for is what the interviewer is scoring. Form 1: LEFT JOIN WHERE NULL Join the two tables with a LEFT JOIN, then filter for rows where the right side is NULL. The NULL appears for rows in the left table that did not match any row in the right table. This is the form most candidates default to because it is the most explicit: you can see the
Choosing the Form the Interviewer Expects (concepts: sqlThreeValuedLogic)
This section is the reason this question lives in interview loops. NOT IN with a NULL in the subquery returns zero rows. Not 'some' rows. Not 'wrong' rows. Zero. The dashboard goes to zero. The on-call gets paged. The fix is one character: switch NOT IN to NOT EXISTS. The trap exists because of how SQL's three-valued logic interprets NOT IN. Why NOT IN with a NULL returns zero The bug, demonstrated Walk through a customer who has never ordered. Their customer_id is 5. The subquery returns (1, 2,
Multi-Column Anti-Joins and Composite Keys (concepts: sqlExists)
The escalation: 'find users who visited page A but never page B on the same day.' One condition is no longer enough; the anti-match has to consider multiple columns at once. The form choice matters again, and the same NULL trap reappears in a slightly different shape. The multi-column version Express the absence with NOT EXISTS, joining the subquery on every column that defines the match. The subquery joins on three columns: user_id, visit_date, and page. The visit_date join is what enforces 'on
When the "Missing" Set Is Itself Filtered (concepts: sqlExists)
After correctness, the interviewer escalates to performance. The follow-up: 'how does the query planner execute this on a billion-row table?' All three forms can produce the same physical plan on a modern optimizer; they can also fall back to nested loops on an older one. Knowing when each plan applies, and what to do when the optimizer picks the slow one, is the depth this question rewards at the mid-and-above level. The three execution plans you should know Hash anti-join: build a hash table o