Explain anti-joins in SQL
This tests knowledge of finding non-matching records. It is a specific pattern for data exclusion.
Why Interviewers Ask This
Finding 'in A but not B' is a common requirement. Interviewers ask this to see if candidates know the standard implementation techniques like LEFT JOIN...IS NULL or NOT EXISTS.
How to Answer This Question
Define anti-join as returning rows from one table with no matches in another. Explain the two common implementations: LEFT JOIN with NULL check or NOT EXISTS predicate. Mention it acts as a semi-join style.
Key Points to Cover
- Returns non-matching rows
- Implemented via LEFT JOIN...NULL
- Alternative via NOT EXISTS
- Used for exclusion logic
Sample Answer
An anti-join returns rows from one table that have no matching rows in another table based on a join condition. It is commonly expressed as a LEFT JOIN followed by WHERE right.key IS NULL. Alternatively, it can be writte…
Common Mistakes to Avoid
- Confusing with INNER JOIN
- Using incorrect NULL checks
- Not knowing NOT EXISTS alternative
Sound confident on this question in 5 minutes
Answer once and get a 30-second AI critique of your structure, content, and delivery. First attempt is free — no signup needed.