Anti-JOIN Pattern — Concept Check
Questions on Anti-JOIN Pattern. An anti-join returns rows from one table that have no match in another; the three implementations (NOT EXISTS, NOT IN, LEFT JOIN IS NULL) have different semantics and performance profiles.
Questions in this quiz
8 questions on Anti-JOIN Pattern — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A database query using NOT IN is returning zero rows unexpectedly. What could be the cause?
- AThe subquery returns NULL values.
- BThe main query has a syntax error.
- CThe main query table is empty.
- DThe subquery returns too many rows.
2.Which anti-join implementation is generally safest when dealing with NULL values in the subquery?
- AINNER JOIN
- BNOT EXISTS
- CLEFT JOIN IS NULL
- DNOT IN
3.A developer uses LEFT JOIN IS NULL for an anti-join on large tables, but it performs poorly. What is a better alternative?
- AUse NOT IN for better performance.
- BAdd more indexes to the tables.
- CUse NOT EXISTS for better performance.
- DUse INNER JOIN instead.
4.Why might a database planner rewrite a NOT EXISTS query to use a LEFT JOIN IS NULL?
- ABoth can be rewritten to the same plan if performance is similar.
- BNOT EXISTS is incompatible with certain databases.
- CLEFT JOIN IS NULL is always faster.
- DThe planner prefers LEFT JOIN IS NULL for readability.
5.A query using NOT EXISTS is unexpectedly slow. What might be the reason?
- ANOT EXISTS is inherently slow.
- BThe subquery lacks proper indexing.
- CThe subquery returns too few rows.
- DThe main table is too small.
6.Which implementation should be avoided if the subquery can return NULLs?
- ALEFT JOIN IS NULL
- BNOT IN
- CNOT EXISTS
- DINNER JOIN
7.What is the primary advantage of using NOT EXISTS over LEFT JOIN IS NULL?
- ANOT EXISTS always uses less memory.
- BNOT EXISTS is more readable.
- CNOT EXISTS is compatible with all SQL dialects.
- DNOT EXISTS can short-circuit after finding a match.
8.You notice a query using NOT IN is running slower than expected. What could improve its performance?
- AAdd more complex conditions to the subquery.
- BIncrease the size of the main table.
- CSwitch to LEFT JOIN IS NULL.
- DEnsure the subquery column is NOT NULL.