ORIGINAL TECHNICAL PRACTICE
Handle NULL in an anti-join
You need customers with no completed orders. Why might NOT IN behave unexpectedly, and what is a safer formulation?
Written for independent practice. The model answer presents one approach; clarify constraints and explain alternatives.
A REASONED APPROACH
Model answer.
SQL uses three-valued logic. If the subquery feeding NOT IN contains NULL, comparisons can become unknown and filter out rows the author expected to retain. A correlated NOT EXISTS avoids that particular trap.
Work through it
- State which order states count as completed and how missing customer IDs are handled. Keep that completion filter inside the existence test.
- Use NOT EXISTS with a correlation on the customer key, or a carefully constructed left anti-join that tests a non-nullable key from the matching side.
- Test a customer with no orders, only incomplete orders, multiple completed orders and an order with a NULL customer ID. Decide the contract for a NULL customer key on the outer side.
Tradeoffs to discuss
- Equivalent anti-join forms can have different execution plans, so inspect the actual database plan and indexes rather than assuming one syntax is always faster.
Common pitfalls
- Testing a nullable descriptive column after a LEFT JOIN can mistake a matching row for no match. NOT EXISTS still needs a correct correlation predicate.