CodePark/ questions / SQL queries & joins
START PRACTICINGPRACTICE
Backend question · Databases & SQL › SQL queries & joins

Write two correct ways to find customers who have never placed an order, and explain why NOT EXISTS is usually preferred over NOT IN.

SELECT c.* FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

SELECT c.* FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Use NOT EXISTS, or a LEFT JOIN with WHERE o.id IS NULL. Both are anti-joins: they keep the left rows that have no matching right row.

NOT EXISTS: for each customer, it asks whether any order exists. It keeps the customer only if none does.

LEFT JOIN: it keeps all customers. Then it keeps only those where the joined order is missing. A missing order shows up as o.id IS NULL. Test a column that cannot be NULL in a real match, such as the primary key.

Why NOT EXISTS is preferred over NOT IN:

  • NOT IN (SELECT customer_id FROM orders) looks the same. But if the subquery returns any NULL, the whole condition becomes unknown, and you get no rows.
  • Because the NULL rules are different, PostgreSQL can plan NOT EXISTS and the LEFT JOIN form as an anti join. This is often a fast Hash Anti Join.
  • PostgreSQL generally cannot do that for NOT IN.
  • NOT EXISTS uses a correlated subquery that checks each customer for orders.
  • The LEFT JOIN form keeps rows WHERE right.pk IS NULL.
  • NOT IN breaks when the subquery returns a NULL.
  • PostgreSQL can plan NOT EXISTS as an anti join, but generally not NOT IN.