Explain anti-joins and how to implement them
This question tests knowledge of exclusionary joins. It evaluates finding non-matching records.
Why Interviewers Ask This
Finding 'A but not B' is a common requirement. Interviewers check if you know the LEFT JOIN...IS NULL pattern or NOT EXISTS alternative.
How to Answer This Question
Define anti-join as returning rows from one table with no matches in another. Show implementation via LEFT JOIN with NULL check or NOT EXISTS.
Key Points to Cover
- Returns non-matching rows
- LEFT JOIN + NULL check
- NOT EXISTS alternative
- Inverted logic
Sample Answer
An anti-join returns rows from one table that have no matching rows in another. It is commonly expressed as a LEFT JOIN followed by WHERE right_table.pk IS NULL. Alternatively, you can use NOT EXISTS to achieve the same result efficiently.
Common Mistakes to Avoid
- Using INNER JOIN by mistake
- Forgetting the NULL check
- Confusing with semi-joins
Practice This Question with AI
Answer this question orally or via text and get instant AI-powered feedback on your response quality, structure, and delivery.
Related Interview Questions
Describe a PRIMARY KEY and how it differs from a UNIQUE key
Medium
What is ER model in the DBMS?
Medium
FlipkartWhat is the difference between LIKE and equals operators in SQL?
Easy
TCSWhat is the difference between UNION and UNION ALL?
Easy
What is Join and explain its types?
Medium
FlipkartWhat is the ER model in DBMS?
Easy
Flipkart