Lessons · SQL · is there at least one

Is there such a row

EXISTS (subquery) is true when the subquery returns at least one row. NOT EXISTS is the robust way to ask 'has no matching row', with any condition you like inside.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Customers with no paid order, users with no login this month, products never reviewed: absence of a qualified event. NOT IN breaks on NULLs and a LEFT JOIN with a WHERE filters the wrong side.

How to think about it

Write the inner query as the thing that must or must not exist, correlate it to the outer row (o.customer_id = c.id), and put the qualifying condition inside it.

Worked example

SELECT name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'paid');

Customers with no paid order: the paid condition lives inside, so a refunded order does not count as one.

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

Customers with at least one order.

Your turn

Films with no review of five stars.

SELECT title FROM films f WHERE  EXISTS (SELECT 1 FROM reviews r WHERE r.film_id = f.id AND r.stars = 5);

The trap

LEFT JOIN orders ... WHERE o.status = 'paid' drops the customers with no orders, because their status is NULL. The condition belongs inside EXISTS, or in the ON.

Practise is there at least one on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.