Why does NOT IN return no rows when the subquery contains NULL, and what are the alternatives?
Why does NOT IN return no rows when the subquery contains NULL, and what are the alternatives?
NOT IN is shorthand for a chain of <> comparisons joined with AND. If any value in the list is NULL, one comparison becomes UNKNOWN, and the whole condition can never be TRUE.
-- customers: ids 1, 2, 3 orders.customer_id: 1, 2, NULL
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
-- expected: 3 actual: no rows
Why: for id = 3 the test is 3 <> 1 AND 3 <> 2 AND 3 <> NULL. The last part is UNKNOWN, so the result is UNKNOWN, and WHERE discards it. The query is correct SQL and silently returns the wrong business answer.
-- 1. NOT EXISTS (usually the best choice)
SELECT c.id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- 2. LEFT JOIN ... IS NULL (anti-join)
SELECT c.id FROM customers c
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.