LEFT JOIN and rows without a match

INNER JOIN silently drops the rows that have no match. Yet often those are exactly the interesting ones: “customers who never ordered”, “groups without a curator”, “students without a single grade”.

LEFT JOIN

Every row of the left table stays. Where there is no match in the right table, its columns come out as NULL.

SELECT g.name, t.last_name AS curator
FROM study_groups g
LEFT JOIN teachers t ON t.id = g.curator_id
ORDER BY t.last_name NULLS FIRST, g.name;

Groups without a curator show NULL as curator.

Finding the rows without a match

The most useful trick: LEFT JOIN + WHERE right.id IS NULL.

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

These are the customers who have never placed an order.

RIGHT JOIN and FULL JOIN

• RIGHT JOIN mirrors LEFT JOIN: the right table stays complete. It is usually clearer to swap the tables and write LEFT JOIN.
• FULL JOIN keeps the unmatched rows from both sides.

PostgreSQL supports FULL OUTER JOIN.

Careful: is the condition in WHERE or in ON?

In a LEFT JOIN, a condition on the right table written in WHERE rejects the unmatched rows (the NULLs), and the LEFT JOIN quietly becomes a plain JOIN. A condition on the right table belongs in ON:

SELECT c.first_name, COUNT(o.id) AS cancelled
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'cancelled'
GROUP BY c.id, c.first_name
ORDER BY cancelled DESC
LIMIT 5;

COUNT(o.id) does not count NULLs, so a customer without cancelled orders gets 0.

Remember

• LEFT JOIN keeps the whole left table; unmatched rows get NULL on the right.
• Rows without a match: LEFT JOIN … WHERE right.id IS NULL.
• Conditions on the right table go in ON, or the LEFT JOIN stops working.