Home / SQL / Reference / Joins

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

nameorders
Noah Brown0
Kiran Rai0
Anjali Karki0
Mia Chen1
Priya Patel1

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.