SQL Tutorial · Joins and Subqueries · Lesson 20 of 36

SQL LEFT JOIN

Keep every row from the left table, matched or not.

LEFT JOIN

LEFT JOIN returns all rows from the left table, plus matching rows from the right. Where there's no match, the right side's columns are NULL:

Example 1Try it yourself

SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY orders DESC, c.name;

Output

name | orders
Ada Lovelace | 3
Alan Turing | 2
Grace Hopper | 2
Linus Torvalds | 2
Guido van Rossum | 1
Tim Berners-Lee | 1
Yukihiro Matsumoto | 1
Barbara Liskov | 0
Katherine Johnson | 0
Margaret Hamilton | 0

Finding rows with no match

A classic pattern: LEFT JOIN then keep rows where the right side is NULL — "customers who never ordered":

Example 2Try it yourself

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

SELECT p.name AS never_ordered
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.product_id IS NULL;

Output

name
Margaret Hamilton
Barbara Liskov
Katherine Johnson

never_ordered
Standing Desk Mat
Robot Kit

Where to filter

A condition on the right table belongs in the ON clause if you want to keep unmatched left rows. Putting it in WHERE silently turns the query back into an inner join.

Test yourself

Which join keeps customers who have no orders?

customers INNER JOIN orders
customers LEFT JOIN orders
orders LEFT JOIN customers
customers CROSS JOIN orders

Show answer

Answer: customers LEFT JOIN orders