How do you find rows in one table that have no matching row in another?

"Which students never paid the fee?" You compare the student list against the payment list and keep the students who find no partner. This is called an anti-join.

Tables customers(id, name) and orders(id, customer_id). Find customers with no orders.

-- Option 1, LEFT JOIN plus IS NULL
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Option 2, NOT EXISTS
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

• In Option 1 the LEFT JOIN keeps every customer. A customer with no orders gets NULL in every o column, and the WHERE keeps exactly those rows. Test a column that is never NULL in real rows, such as the primary key.
• In Option 2 the subquery runs per customer and NOT EXISTS is true when it finds nothing.
• Avoid WHERE id NOT IN (SELECT customer_id FROM orders) if customer_id can be NULL, because then no row is returned at all.

Time: with an index on orders.customer_id, about O(n log m) for n customers and m orders; a hash anti-join is O(n + m). Space: O(1) to O(m) for a hash table.