Customers With No Orders (NOT EXISTS)
sqlSQLbeginnerinterview-prep
An anti-join with NOT EXISTS: find rows in one table that have no match in another, safely, even when the other table contains NULLs.
-- Customers who have never placed an order.
-- NOT EXISTS is safe when orders.customer_id contains NULL. NOT IN would return no rows in that case.
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);
-- Same result with a LEFT JOIN anti-join:
-- SELECT c.id, c.name
-- FROM customers c
-- LEFT JOIN orders o ON o.customer_id = c.id
-- WHERE o.id IS NULL;