Anti-pattern: NOT IN against NULLs
A single NULL in the subquery and NOT IN silently returns zero rows (SQL three-valued logic). NOT EXISTS is NULL-safe and usually planned better.
Prerequisites
SQL standard
SQL
-- Anti-pattern : 0 ligne si orders.customer_id contient UN NULL
SELECT *
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
-- Correctif : NOT EXISTS, insensible aux NULL
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);Result
-- NOT IN avec un customer_id NULL dans orders : id | name ----+------ (0 rows) <- résultat vide TROMPEUR -- NOT EXISTS : id | name | email ------+-------------+---------------------- 4099 | Petit SAS | contact@petit-sas.fr 4150 | Moreau SARL | info@moreau.fr (2 rows) <- les vrais clients sans commande
SQLNOT INNOT EXISTSAnti-pattern