BeginnerSQL · 25 min

Anti-Join: Beginner

Anti-join is the SQL shape that answers questions about absence. Customers who never placed an order. Products that have never been viewed. Sessions that did not convert. Every reporting query about "who is missing" or "what did not happen" lives here. The pattern is small. The reason it earns a spot in entry-level interview loops is that there are three ways to write it and one of them has a silent failure mode on real data. This lesson teaches you the three forms, the one that you should default to, and the one that you should never write in production.

Spot an anti-join question the moment the interviewer says "never" or "without"

Write the three canonical forms (LEFT JOIN WHERE NULL, NOT EXISTS, NOT IN) from memory

Default to NOT EXISTS for new code and articulate why in one sentence

Recognize the NOT IN NULL trap before the interviewer mentions it

"Users Who Never..." Is Always an Anti-Join

Daily Life

Interviews

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

Here is the question you will see in your first SQL interview that involves two tables: 'find customers who have never placed an order.' Two minutes later the interviewer has a sense of how you parse the prompt. The candidate who reads the word 'never' and immediately says 'this is an anti-join, I'll use NOT EXISTS' has cleared the first hurdle. The candidate who writes a LEFT JOIN and then has to figure out where to put the IS NULL filter is mid-stream when the interviewer asks the next question. Naming the shape before writing SQL is the move that pays off for the rest of the interview.

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"
▸"records present in A but not in B"
▸"events with no matching ..."
▸Any question framed as the absence of a relationship

What 'anti-join' actually means

A regular join (INNER JOIN) returns rows where the two tables match on the join condition. An anti-join returns rows from the left side that have no matching row in the right side. The output has the same columns as the left table; the right table is consulted only to decide which rows to keep. Customers who never placed an order means: every customer row whose customer_id does not appear in the orders table. The shape of the output is 'left rows where nothing matches.'

Spotting it in the first ten seconds

Read the question. If the question is about the absence of a relationship, the answer is an anti-join. Trigger words: 'never,' 'without,' 'no matching,' 'not in,' 'missing.' Say one sentence before you touch the keyboard: 'This is an anti-join. I'll use NOT EXISTS, with the subquery checking that no matching row exists in the other table.' That sentence covers the shape and the tool. Saying it tells the interviewer you parsed the prompt before reaching for SQL.

The ten-second sentence to say before writing SQL:

▸"This is an anti-join."
▸"I'll use NOT EXISTS, with the subquery checking that no matching row exists."
▸"The form is NULL-safe and reads close to the question."

•Weak opening

Writes a LEFT JOIN immediately, then fumbles for the IS NULL filter
Tries NOT IN without thinking about NULL handling
Cannot articulate why one form is preferred
Loses time recovering from a misread of the question

✓Strong opening

Says "this is an anti-join, I'll use NOT EXISTS" before writing SQL
Writes the canonical NOT EXISTS query in six lines
Names the NULL safety of NOT EXISTS as the reason for the default
Has the LEFT JOIN WHERE NULL form ready as a fallback if the interviewer asks

Why companies care

Anti-joins power every dashboard about inactive entities. Customers who never purchased. Products never viewed. Subscribers who never opened an email. Sessions that did not convert. Employees who have not completed onboarding. If you cannot write this query, an entire family of business questions about missing behavior is closed to you. That is why this is a common second-question SQL screen, right after a basic SELECT and JOIN.

LEFT JOIN ... WHERE Right Side IS NULL

Daily Life

Interviews

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

The same English question maps to three SQL forms. All three return the same rows on clean data. You should be able to write each of them in six lines without thinking. Knowing all three is the floor. Knowing which one to default to is what the interviewer is reading you for.

join the tables; keep rows where the right side is NULL. Explicit and easy to trace, but verbose.
subquery returns no row → keep the outer row. Reads like the English; NULL-safe by construction. Default reach.
exclude IDs that appear in the subquery. Reads like English, but ships zero rows when the subquery has a NULL.

Form 1: LEFT JOIN WHERE NULL

Join the two tables with a LEFT JOIN. The LEFT JOIN keeps every row from the left side, attaching matching rows from the right side where they exist and NULLs where they do not. Filter the result to keep only the rows where the right side is NULL. Those are the left rows that did not match.

	/* Customers who have never placed an order, LEFT JOIN form */
	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

Trace it. Suppose customers has Alice, Bob, Carol. Orders has rows for Alice and Bob, none for Carol. The LEFT JOIN produces three rows: Alice paired with her order, Bob paired with his order, Carol paired with NULL (because no order matches). The WHERE keeps only the row where o.customer_id is NULL: Carol. That is the customer who never ordered. Output: one row, Carol.

Form 2: NOT EXISTS

Ask the question directly: for each customer, does any order exist with this customer_id? If no such order exists, keep the customer. The NOT EXISTS form puts the question in the WHERE clause as a correlated subquery.

	/* 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
	)

Read this as English. 'Select customers where no order exists with this customer_id.' The subquery does not return data; it answers a yes-or-no question per outer row. NOT EXISTS is TRUE when the subquery returns no rows. The shape reads close to the prompt: 'customers who have never placed an order' becomes 'customers where no order exists.'

Form 3: NOT IN

Pull the list of customer_ids that appear in the orders table, and exclude them. This is the form most candidates write first because it reads like English. It is also the form that ships the silent bug. Section 2 explains why.

	/* 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 walks through exactly why. For now, mark NOT IN as 'works in dev, fails in prod.'

All three produce the same answer on clean data

Run any of the three forms against the same Alice-Bob-Carol example and you get the same result: Carol. That is the floor: each form is a valid way to express the question. The difference is what happens when the data is not clean. The next section pulls that difference apart.

TIP

When you write all three forms, narrate the trade you are making. 'NOT EXISTS reads closest to the question; LEFT JOIN WHERE NULL is explicit about the join; NOT IN reads like English but has a NULL trap.' That narration is what tells the interviewer you have made the choice deliberately.

Reading the NULL Row as "No Match Found"

Daily Life

Interviews

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

This is the section the interview question exists to test. NOT IN with a NULL in the subquery returns zero rows. Not 'some' rows. Not 'wrong' rows. Zero. The dashboard reads zero. The on-call gets paged. The candidate who has not seen this learns it the hard way. The candidate who has seen it defaults to NOT EXISTS and avoids the trap entirely.

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 one customer. Carol has customer_id = 5 and has never ordered. The subquery returns the customer_ids that appear in orders: (1, 2, 3, NULL). NOT IN expands into a chain of inequalities: 5 != 1 AND 5 != 2 AND 5 != 3 AND 5 != NULL. The first three are TRUE. The last one is UNKNOWN, because comparing anything to NULL is UNKNOWN in SQL. TRUE AND TRUE AND TRUE AND UNKNOWN is UNKNOWN. UNKNOWN is not TRUE, so Carol is excluded. Every customer in the table gets excluded by the same logic. Result: empty.

Why this is the language's behavior, not a bug

SQL has three truth values: TRUE, FALSE, and UNKNOWN. Comparing any value to NULL returns UNKNOWN. The behavior is consistent with how NULL works everywhere else: 5 = NULL is UNKNOWN, 5 != NULL is UNKNOWN, NULL = NULL is UNKNOWN. NOT IN is built on top of !=, so it inherits the UNKNOWN behavior. The language is doing what it says it does. The trap is that this behavior is rarely what the author of the query intended.

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 every row is excluded
▸Result: empty. No error, no warning, just zero rows.

Why NOT EXISTS does not hit the trap

NOT EXISTS does not chain inequalities. It asks the subquery whether any row matches the outer row's customer_id. For Carol, the subquery looks for an order where o.customer_id = 5. The NULL row in orders has customer_id = NULL, which does not equal 5 (the comparison is UNKNOWN, which is not TRUE), so it is not a match. The subquery returns no rows. NOT EXISTS is TRUE. Carol is kept. The NULL row in orders does not contaminate the result for Carol.

•NOT IN with a NULL in the subquery

A single NULL anywhere in the subquery returns zero rows total
The bug is silent: no error, no warning, no log line
Reproduces only on the data that has the NULL
Classic interview trap; classic production incident

✓NOT EXISTS with a NULL in the subquery

NULL rows are evaluated per outer row, not as a poison value
Correctly excludes only customers who have a matching order
Behavior is predictable on every data shape
The reason this should be your default for anti-joins

If you must use NOT IN for some reason (legacy code, dialect restriction), filter the subquery to exclude NULLs explicitly: 'NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL).' The filter restores correctness but adds a fragile dependency: every future author of this code has to remember to add the same filter. NOT EXISTS removes the human-error surface entirely.

The one-sentence rule

Default to NOT EXISTS for anti-join questions. The form is NULL-safe by construction, reads close to the English of the question, and produces the same query plan as LEFT JOIN WHERE NULL on every modern engine. Use LEFT JOIN WHERE NULL when team house style favors it or when you need columns from the right side that the subquery cannot expose. Use NOT IN only when you have explicitly verified that the subquery cannot return NULL, and even then, prefer NOT EXISTS. That is the rule. The interviewer is reading you for whether you reach for it before being told.

TIP

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

At Datadog in 2020, a marketing-ops dashboard tracking 'free-tier accounts that have never converted' reported zero accounts for two consecutive days after a backend release. The query used NOT IN against the conversions table. The release had added a feature-flag field, and one row in conversions came back with a NULL account_id from a misconfigured backfill job. NOT IN evaluated to UNKNOWN for every free-tier account, the result was empty, and the marketing team thought their attribution had broken. The on-call rolled back the backfill and the dashboard recovered. The runbook line was 'no analytics query against an unfixed-cardinality table uses NOT IN; the CI lint enforces NOT EXISTS for any subquery against a non-enum source.' Candidates who default to NOT EXISTS skip the whole story; the interviewer reads that default as production experience.

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's NULL-safe by construction and reads close to the English question."
The interviewer asks 'why not NOT IN'	"It's slower."	"Three-valued logic. A single NULL in the subquery makes the chain x != ... AND x != NULL evaluate to UNKNOWN, which is not TRUE, so every row is excluded. The bug is silent: empty result, no error."
The interviewer asks 'why not LEFT JOIN WHERE NULL'	"It's verbose."	"Both work and plan the same on modern engines. NOT EXISTS reads closer to the question; LEFT JOIN wins when I need columns from the right side or when the team standardizes on join syntax."
You realize the question needs columns from orders	"I'll switch forms."	"NOT EXISTS hides the right table from the outer SELECT. For 'customers and their lifetime-canceled-order count, including customers with zero,' I'd switch to LEFT JOIN with COALESCE(SUM(o.amount), 0) so the right-side columns are in scope."
The interviewer asks 'how does it scale'	"It's fast."	"On modern engines all three forms compile to a hash anti-join. The lever is the index on the right side's join column; without it, the anti-join scans the full orders table per customer."

Anti-Join vs Plain Filter: What's the Difference

Daily Life

Interviews

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

NOT EXISTS is the default. LEFT JOIN WHERE NULL is the fallback for two specific cases. Knowing both forms cold means you can switch when the situation calls for it without rewriting from scratch. The interviewer at this level may ask you to write all three; being smooth between them is the signal.

Case 1: you need columns from the right side

NOT EXISTS hides the right table from the outer SELECT. The subquery is a yes-or-no check; you cannot return o.amount or o.order_date from the outer query because the right table is not in scope. LEFT JOIN keeps the right table available, even when you are filtering for rows where it did not match. If the question is 'customers who have never placed an order, along with their last_login_date from the customers table only,' NOT EXISTS works fine. If the question is 'customers and their lifetime-canceled-order count, including customers with zero canceled orders,' you need the LEFT JOIN form because the SELECT references columns from both sides.

When to fall back to LEFT JOIN WHERE NULL:

▸You need columns from the right side in the SELECT
▸The team has a house-style convention for join syntax
▸The query is going to be extended with matching rows later
▸Otherwise: stick with NOT EXISTS as the default

	/* LEFT JOIN form when you need columns from both sides */
	SELECT
	c.customer_id,
	c.name,
	c.last_login_date,
	COALESCE(SUM(o.amount), 0) AS lifetime_revenue
	FROM customers AS c
	LEFT JOIN orders AS o
	ON o.customer_id = c.customer_id
	GROUP BY c.customer_id, c.name, c.last_login_date

This is not strictly an anti-join (we are keeping all customers, not just the ones who never ordered), but it uses the same LEFT JOIN shape. The COALESCE converts NULL lifetime revenue (for customers with no orders) to zero. The form generalizes: anywhere you need 'all rows from the left side plus optional matching rows from the right side,' LEFT JOIN is the tool. NOT EXISTS cannot express that shape.

Case 2: team house style

Some codebases standardize on LEFT JOIN WHERE NULL for anti-joins, often because the team learned SQL from join-heavy tutorials and the form reads as 'a join with a filter' rather than 'a subquery.' Both forms produce equivalent query plans on modern engines, so the choice is stylistic. When you join a team with that convention, write what the team writes. The interviewer in a team-context question may explicitly ask you to use the LEFT JOIN form; pivot smoothly.

•Use NOT EXISTS when

The question is purely about absence (no columns from the right side)
The team has no established convention
The subquery condition might involve multiple columns
Defaulting to NULL safety matters more than syntactic preference

✓Use LEFT JOIN WHERE NULL when

The query needs columns from the right side (totals, latest values)
The team's house style favors join syntax
The query is going to be extended later to include matching rows
An optimizer's plan for LEFT JOIN is easier for the team to reason about

The mistake to avoid

Do not write LEFT JOIN WHERE NULL and then add an extra ON condition that filters the join. Conditions on the right side belong in the ON clause, not the WHERE. Putting them in the WHERE clause turns a LEFT JOIN into an INNER JOIN logically, because the WHERE filters out the NULL-padded rows that the LEFT JOIN was supposed to keep. The classic bug: 'LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = "completed" AND o.customer_id IS NULL' returns zero rows, because o.status = 'completed' is never TRUE on a NULL-padded row. Move conditions that should affect the match into the ON clause: 'ON o.customer_id = c.customer_id AND o.status = "completed"'. The interviewer will sometimes hand you this exact scenario; spotting it is the move.

The rule is: conditions on the left side go in WHERE; conditions on the right side go in ON. Mixing them up is the most common LEFT-JOIN-WHERE-NULL bug. When in doubt, ask: 'should this condition affect whether the row matches, or whether the matched row is included?' Match conditions go in ON; result filters go in WHERE.

Drawing the Two Sets Before You Write SQL

Daily Life

Interviews

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

Past correctness, the interviewer will ask about performance. The answer does not need to be deep at this stage, but it needs to name two things: the supporting index on the right side, and the choice between checking the plan versus rewriting the query. Two beats are enough here; the deeper conversation about hash anti-joins and shuffles belongs in the advanced lesson.

The supporting index

Anti-joins on a large table are fast when the join column on the right side has an index. NOT EXISTS, LEFT JOIN WHERE NULL, and NOT IN all benefit from an index on orders.customer_id. Without that index, the engine has to scan the entire orders table to check each customer, which becomes catastrophic on a billion-row source. State the index unprompted: 'I would make sure orders.customer_id is indexed before deploying this query. Without the index, the anti-join scans the full orders table for every customer.'

	'CREATE INDEX idx_orders_customer_id ON orders (customer_id)'
	/* The index that makes the anti-join fast */

Why all three forms have the same plan on modern engines

On Postgres 12+, BigQuery, Snowflake, and SQL Server, the query planner recognizes all three forms as anti-joins and emits the same physical plan: a hash anti-join. The engine builds a hash table of the right side's join column, scans the left side, and emits each left row that does not match the hash. Cost is O(N + M), with memory bounded by the size of the right side. Picking between the three forms is a correctness and readability choice, not a performance choice.

When the plan is bad

If the query is slow, two checks. First: EXPLAIN the query and confirm the plan is a hash anti-join, not a nested loop. If the plan is a nested loop and the right side is large, the cost is O(N × M), which is the slow case. Second: confirm the join column on the right side has an index. Without the index, even the right plan reads more data than necessary. State both checks: 'I would EXPLAIN the query to confirm the hash anti-join plan, then check that orders.customer_id is indexed.' That sentence proves you treat performance as something to verify, not assume.

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 close to the question. NOT IN ships zero rows when the subquery contains a NULL, which is the classic production bug; LEFT JOIN WHERE NULL is the fallback when I need columns from the right side. For performance, I would EXPLAIN the query and make sure the join column on the right side is indexed.' Four sentences. Pattern, default tool, NULL trap, performance. The shape generalizes to every anti-join question at this level.

❯❯❯PUTTING IT ALL TOGETHER

> You are in an Oracle data engineering phone screen. The interviewer asks: 'Find customers who have never placed an order.'

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

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

Trace one customer: Carol has never ordered. The subquery looks for an order with Carol's customer_id, finds nothing, NOT EXISTS is TRUE, Carol appears in the result.

Follow-up: 'What if orders has a NULL customer_id from a bad ingestion run?' You say: 'NOT IN would return zero rows because of three-valued logic. NOT EXISTS is unaffected because it evaluates row by row.'

Closing: 'For performance I would make sure orders.customer_id is indexed; without the index the anti-join scans the full orders table for every customer.'

KEY TAKEAWAYS

A question about the absence of a relationship is an anti-join: 'never,' 'without,' 'no matching,' and 'missing' all map to left rows kept only when nothing matches on the right.

Default to NOT EXISTS. It is NULL safe by construction, reads close to the English of the prompt, and the planner gives it the same hash anti-join plan as the LEFT JOIN form.

NOT IN returns zero rows, not wrong rows, the moment a single NULL lands in the subquery, because value != NULL is UNKNOWN and never TRUE. This is the production bug the question exists to test.

Fall back to LEFT JOIN with WHERE right.key IS NULL for two reasons only: you need columns from the right side in the output, or the team's house style calls for it.

In the LEFT JOIN form, conditions on the right table belong in ON, not WHERE. A right-side predicate in WHERE kills the NULL-padded rows and silently turns the anti-join into an inner join.

Name the index unprompted: an index on the right side's join column keeps the anti-join off a full scan, and EXPLAIN confirms you got a hash anti-join instead of a nested loop.

Finding what is NOT there is harder than finding what is

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

Topics covered: "Users Who Never..." Is Always an Anti-Join, LEFT JOIN ... WHERE Right Side IS NULL, Reading the NULL Row as "No Match Found", Anti-Join vs Plain Filter: What's the Difference, Drawing the Two Sets Before You Write SQL

Lesson Sections

"Users Who Never..." Is Always an Anti-Join (concepts: sqlExists)
What 'anti-join' actually means A regular join (INNER JOIN) returns rows where the two tables match on the join condition. An anti-join returns rows from the left side that have no matching row in the right side. The output has the same columns as the left table; the right table is consulted only to decide which rows to keep. Customers who never placed an order means: every customer row whose customer_id does not appear in the orders table. The shape of the output is 'left rows where nothing mat
LEFT JOIN ... WHERE Right Side IS NULL (concepts: sqlNullInJoins)
The same English question maps to three SQL forms. All three return the same rows on clean data. You should be able to write each of them in six lines without thinking. Knowing all three is the floor. Knowing which one to default to is what the interviewer is reading you for. Form 1: LEFT JOIN WHERE NULL Join the two tables with a LEFT JOIN. The LEFT JOIN keeps every row from the left side, attaching matching rows from the right side where they exist and NULLs where they do not. Filter the resul
Reading the NULL Row as "No Match Found" (concepts: sqlNullInJoins)
This is the section the interview question exists to test. NOT IN with a NULL in the subquery returns zero rows. Not 'some' rows. Not 'wrong' rows. Zero. The dashboard reads zero. The on-call gets paged. The candidate who has not seen this learns it the hard way. The candidate who has seen it defaults to NOT EXISTS and avoids the trap entirely. The bug, demonstrated Why this is the language's behavior, not a bug SQL has three truth values: TRUE, FALSE, and UNKNOWN. Comparing any value to NULL re
Anti-Join vs Plain Filter: What's the Difference (concepts: sqlExists)
NOT EXISTS is the default. LEFT JOIN WHERE NULL is the fallback for two specific cases. Knowing both forms cold means you can switch when the situation calls for it without rewriting from scratch. The interviewer at this level may ask you to write all three; being smooth between them is the signal. Case 1: you need columns from the right side NOT EXISTS hides the right table from the outer SELECT. The subquery is a yes-or-no check; you cannot return o.amount or o.order_date from the outer query
Drawing the Two Sets Before You Write SQL (concepts: sqlExists)
Past correctness, the interviewer will ask about performance. The answer does not need to be deep at this stage, but it needs to name two things: the supporting index on the right side, and the choice between checking the plan versus rewriting the query. Two beats are enough here; the deeper conversation about hash anti-joins and shuffles belongs in the advanced lesson. The supporting index Anti-joins on a large table are fast when the join column on the right side has an index. NOT EXISTS, LEFT