Detailed explanation
To find records in table A that are missing in table B, identify rows in A for which no row in B satisfies a matching condition. This is called an anti-join: it returns unmatched left-side rows rather than matched pairs. For example, to find customers who have never placed an order, match customers.id with orders.customer_id. The comparison key matters: you are checking whether a related row exists, not necessarily whether every column of two records is identical.
One common solution is SELECT c.* FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;. A LEFT JOIN logically preserves every customer, attaching matching order data where available and supplying NULLs for right-side columns when no match exists. The WHERE clause then keeps only unmatched customers. Here, orders.id should be guaranteed non-null for real order rows, typically because it is a primary key. Testing a nullable right-side attribute, such as delivery_date, could incorrectly include customers whose orders have that attribute missing.
A more direct expression is SELECT c.* FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);. The correlated subquery describes a matching order, and NOT EXISTS accepts a customer only when no such order exists. SELECT 1 is conventional; the selected value does not affect the existence test. Database optimisers can often transform either formulation into an anti-join execution plan, so avoid claiming that one is universally faster. An index on orders.customer_id can help, but performance also depends on table sizes, statistics, and the chosen plan.
Be careful with NOT IN: if its subquery returns even one NULL, nonmatching comparisons can evaluate to UNKNOWN and be excluded by WHERE. NOT EXISTS avoids this particular trap, although ordinary equality still does not treat NULL as equal to NULL. Interviewers also check filter placement: for customers with no paid orders, place o.status = 'PAID' inside the JOIN's ON clause or the NOT EXISTS subquery. That defines the matches to exclude without accidentally discarding the unmatched customers.
Key points to remember
- An anti-join returns rows from A with no matching row in B under the specified condition.
- Use LEFT JOIN followed by IS NULL on a right-side column guaranteed non-null for matched rows.
- NOT EXISTS directly expresses the absence of matching rows and is safe from NULLs elsewhere in the subquery result.
- Place conditions defining relevant B rows inside ON or inside the NOT EXISTS subquery.
- Compare execution plans and consider an index on B's matching key rather than assuming one syntax is faster.
Common mistakes candidates make
- Using NOT IN without considering NULL values in both the subquery output and the outer comparison key.
- Checking a nullable right-side attribute instead of a column that reliably identifies an unmatched row.
- Putting a right-side status filter in WHERE alongside the unmatched-row check, thereby eliminating the rows being sought.
Likely follow-up questions
Do duplicate matching rows in B cause duplicate output?
NOT EXISTS only tests whether a match exists, so duplicates in B do not change its result. A correctly written LEFT JOIN anti-join also excludes all matched rows; however, neither approach removes duplicates already present in A.
How do NULL matching keys behave with NOT EXISTS?
With ordinary equality, a NULL key in A does not match a NULL key in B, so that A row is returned. If the requirement treats two NULL keys as equal, use your database's null-safe comparison or explicitly handle both keys being NULL.
Can EXCEPT solve this problem?
Yes: SELECT id FROM A EXCEPT SELECT id FROM B returns distinct keys present only in A where EXCEPT is supported. Unlike an anti-join selecting A.*, it compares the projected values and normally removes duplicates, so its result semantics may differ.
Interview tip: State NOT EXISTS or LEFT JOIN with IS NULL, explain the matching key using the customer-order example, and finish with the NULL caveat.