Problem Statement
You need products with zero sales in 2025. Show two correct patterns and explain their trade-offs.
Explanation
A LEFT JOIN with WHERE right.id IS NULL is a classic anti-join. It is readable and can use indexes on the join keys. Be sure the join predicate covers the date range so matched rows are only from 2025.
NOT EXISTS with a correlated subquery is also correct. It stops on first match and handles NULLs cleanly. Engines often produce similar plans; prefer the pattern your team uses consistently.
Code Solution
SolutionRead Only
SELECT p.product_id
FROM products p
LEFT JOIN sales s
ON s.product_id = p.product_id
AND s.sale_date >= '2025-01-01' AND s.sale_date < '2026-01-01'
WHERE s.product_id IS NULL;
-- or
SELECT p.product_id
FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM sales s
WHERE s.product_id=p.product_id
AND s.sale_date >= '2025-01-01' AND s.sale_date < '2026-01-01'
);Practice Sets
This question appears in the following practice sets:
