LEFT JOIN & Finding What's Missing (Anti-Joins)

LEFT JOIN returns every row from the left table. Where no right-hand row matches, the right-hand columns are NULL:

SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;

The anti-join pattern. To find left rows that have no match at all, LEFT JOIN and then keep only the rows where a right-hand column that is never NULL in a real match (its primary key) is NULL:

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

Read it as: 'customers, with their orders if any; keep the ones where the order is missing' - customers who never ordered. The same shape answers 'accounts with no sessions', 'rooms with no bookings' and 'tickets nobody replied to'.

Where you put the filter matters. A condition on the right-hand table in WHERE (WHERE o.status = 'paid') turns the LEFT JOIN back into an inner join, because the NULL rows fail the test. To get 'every customer, with only their paid orders', put the condition in ON instead: LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid'.

Equivalent forms. WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) says the same thing. WHERE c.id NOT IN (SELECT customer_id FROM orders) looks equivalent but returns nothing at all if the subquery yields even one NULL, so prefer LEFT JOIN ... IS NULL or NOT EXISTS.

Counting with a LEFT JOIN. COUNT(o.id) counts only matched orders (0 for customers with none), while COUNT(*) would count the unmatched row itself as 1. Always count a column from the optional side.