Técnica
Anti-join
Las filas de una tabla que no tienen correspondencia en otra: los clientes que nunca han hecho un pedido.
SELECT c.*
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
Mismo resultado con LEFT JOIN … WHERE o.customer_id IS NULL. Evite NOT IN (subquery): basta un NULL en la
subconsulta para que no devuelva nada.
SELECT c.customer_id, c.first_name, c.last_name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id) ORDER BY c.customer_id LIMIT 5;
| customer_id | first_name | last_name |
|---|---|---|
| 4 | Elsa | Richard |
| 5 | Louis | Martinez |
| 7 | Tom | Dupont |
| 9 | Jade | Meier |
| 12 | Sacha | Dumont |