"Never" Means an Anti-Join

Concepts covered: Outer Joins

The word anti-join appears in no interview question. The question arrives as a sentence about something that did not happen: a customer who never ordered, a product nobody viewed, an employee with no training record. The table that would record the event holds a row for every event that did occur, so the thing the question asks about has left no row anywhere. The only evidence is a gap, and a gap can't be selected with a WHERE clause on the table that has it.

An anti-join is the query shape that finds the gap. It returns the rows of one table that have no matching row in another table, and only those. The output carries the first table's columns at the first table's grain, 1 row per customer and never more, and the second table is read only to decide which rows survive. An INNER JOIN returns a row for every pair that matches and discards the rest, so the anti-join is its complement: the inner join answers "who did", the anti-join answers "who did not". Both start from the same 2 tables and the same key.

The disguises it arrives in

The trigger words are "never", "without", "no", "missing" and "not in". Each sentence names 2 tables and a relationship between them, and asks for the side where the relationship is absent. Hearing the sentence as sets takes the guesswork out: one table supplies the candidates, the other table supplies the evidence, and the answer is every candidate with no evidence against it.

The 2 tables behind the question

The sample has 4 customers and 4 orders. Alice placed 2 orders, Bob and Dana placed 1 each, and no order carries customer_id 5, so Carol is the answer. Counting confirms the shape before any SQL runs: 4 customers, 3 distinct customer_ids present in orders, and 4 minus 3 leaves 1 row. Alice's 2 orders earn her 0 rows in the result rather than 2, which is the first fact to keep straight: how many times a customer matches only matters as zero or not zero.

The grain is the check you can do without running anything. The result has the grain of customers, so it can have at most 4 rows, and any anti-join that returns more rows than its left table has joined something wrong. In an interview, stating the bound aloud, "this returns at most one row per customer", shows that you know what each output row stands for, which is the thing join and GROUP BY questions keep testing from different angles.

The sentence to say before typing

Say one sentence that names the shape, the tool and the test: "This is an anti-join. I keep every customer with no matching row in orders, so I'll LEFT JOIN orders onto customers on customer_id and keep the rows where the orders side came back NULL." The interviewer now has your plan, and if the plan is wrong they can redirect you before you have written a line, which costs you far less than a wrong query does.

Moves that cost the offer
  • Typing SELECT * FROM customers and searching for a column that records whether an order exists
  • Writing an INNER JOIN and staring at a result that holds only the customers who did order
  • Going silent for a minute reading the tables instead of restating the question as 2 sets
  • Asking "do you want the ones with orders or without?" after the question said never
Moves that earn it
  • Restating the question as a kept table, an excluding table and a join column between them
  • Naming the join column before writing the join
  • Writing the 6-line LEFT JOIN ... WHERE ... IS NULL form without pausing
  • Tracing 1 matched customer and 1 unmatched customer through the query aloud

About this lesson

This section is part of the Anti-Join: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.