SQL LEFT JOIN
Keep every row of the left table, matched or not.
Syntax
SELECT … FROM a LEFT JOIN b ON b.key = a.key;
Where the right table has no match, its columns are NULL. LEFT JOIN … WHERE b.key IS NULL finds rows with no match (an anti-join).
Example
Query (on the shop database used in the SQL course)
SELECT c.name, COUNT(o.order_id) AS orders FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id GROUP BY c.customer_id ORDER BY orders LIMIT 5;
Result
| name | orders |
|---|---|
| Noah Brown | 0 |
| Kiran Rai | 0 |
| Anjali Karki | 0 |
| Mia Chen | 1 |
| Priya Patel | 1 |
5 rows
Good to know. A WHERE condition on the right table turns a LEFT JOIN into an inner join: put it in ON instead.
Related
Learn it step by step in the free lesson Joining Tables, with exercises that check your queries.