SQL LEFT JOIN Explained (vs INNER JOIN, IS NULL)
· 7 min read
A LEFT JOIN keeps every row from the left table (the one after FROM) and adds matching columns from the right table. When a left row has no match, SQL still returns it and fills the right table's columns with NULL.
SELECT c.customer_name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;Use a left join whenever the question is about all of something: all customers, all products, all months, whether or not they have activity. The rest of this guide shows the output, compares left join vs inner join, and covers the WHERE mistake that quietly turns a left join back into an inner join.
A worked example
| customer_id | customer_name |
|---|---|
| 1 | Acme Dental |
| 2 | Birch Cafe |
| 3 | Cobalt Labs |
| 4 | Dune Studio |
| order_id | customer_id | status | amount |
|---|---|---|---|
| 101 | 1 | paid | 120.00 |
| 102 | 1 | refunded | 48.00 |
| 103 | 2 | paid | 210.00 |
| 104 | 3 | refunded | 75.50 |
The query above returns:
| customer_name | order_id | amount |
|---|---|---|
| Acme Dental | 101 | 120.00 |
| Acme Dental | 102 | 48.00 |
| Birch Cafe | 103 | 210.00 |
| Cobalt Labs | 104 | 75.50 |
| Dune Studio | NULL | NULL |
Dune Studio has never ordered, but it is still in the result. The NULLs are SQL saying "there was nothing to match".
Left join vs inner join
Swap LEFT JOIN for INNER JOIN and run it again on the same tables:
SELECT c.customer_name, o.order_id, o.amount
FROM customers AS c
INNER JOIN orders AS o
ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;| customer_name | order_id | amount |
|---|---|---|
| Acme Dental | 101 | 120.00 |
| Acme Dental | 102 | 48.00 |
| Birch Cafe | 103 | 210.00 |
| Cobalt Labs | 104 | 75.50 |
Same rows, minus Dune Studio. That is the only difference:
| INNER JOIN | LEFT JOIN | |
|---|---|---|
| Left rows with a match | kept | kept |
| Left rows with no match | dropped | kept, right columns are NULL |
| Use it when | you only care about rows that exist in both | you need every row from the left table |
If you are new to joins, start with how to join two tables and come back.
LEFT JOIN with IS NULL: find rows with no match
The NULLs are useful on their own. Filter for them and you get the left rows that have no match at all. This is often called an anti-join.
SELECT c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;| customer_name |
|---|
| Dune Studio |
Test a column that can never be NULL in a real match, like the right table's primary key (o.order_id). If you test a column that is sometimes empty anyway, you will catch false positives.
Only some right rows should count
Now a harder question: which customers have no paid order? Cobalt Labs has an order, but it was refunded, so it should be in the answer too.
The filter on status belongs in the ON clause, so it limits which orders are allowed to match:
SELECT c.customer_name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'paid'
ORDER BY c.customer_id;| customer_name | order_id | amount |
|---|---|---|
| Acme Dental | 101 | 120.00 |
| Birch Cafe | 103 | 210.00 |
| Cobalt Labs | NULL | NULL |
| Dune Studio | NULL | NULL |
Cobalt Labs now shows NULL because its only order failed the ON condition. Add WHERE o.order_id IS NULL to this query and you get Cobalt Labs and Dune Studio, which is the right answer.
Counting with a left join
Left joins are the standard way to build "one row per customer, including zeros":
SELECT c.customer_name,
COUNT(o.order_id) AS paid_orders,
COALESCE(SUM(o.amount), 0) AS paid_total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'paid'
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;| customer_name | paid_orders | paid_total |
|---|---|---|
| Acme Dental | 1 | 120.00 |
| Birch Cafe | 1 | 210.00 |
| Cobalt Labs | 0 | 0.00 |
| Dune Studio | 0 | 0.00 |
COUNT(o.order_id) skips NULLs, so customers with no match get 0. COALESCE turns the NULL sum into 0.
Left join multiple tables
You can chain left joins. Here a refunds table records why an order was refunded:
| order_id | reason |
|---|---|
| 102 | Damaged |
| 104 | Wrong item |
SELECT c.customer_name, o.order_id, r.reason
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
LEFT JOIN refunds AS r ON r.order_id = o.order_id
ORDER BY c.customer_id, o.order_id;| customer_name | order_id | reason |
|---|---|---|
| Acme Dental | 101 | NULL |
| Acme Dental | 102 | Damaged |
| Birch Cafe | 103 | NULL |
| Cobalt Labs | 104 | Wrong item |
| Dune Studio | NULL | NULL |
Every customer survives both joins. The rule: once you start a chain of left joins, keep every later join a left join, unless you mean to drop rows. Change the second one to a plain JOIN and you get this:
| customer_name | order_id | reason |
|---|---|---|
| Acme Dental | 102 | Damaged |
| Cobalt Labs | 104 | Wrong item |
The inner join to refunds threw away every row without a refund, including Dune Studio, which the first left join had carefully kept.
Common mistakes
Filtering the right table in WHERE (the AI mistake to watch for)
This is the left join bug AI assistants write most often. Asked for "all customers with their paid orders", an assistant will happily put the status filter in WHERE:
SELECT c.customer_name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
ORDER BY c.customer_id;| customer_name | order_id | amount |
|---|---|---|
| Acme Dental | 101 | 120.00 |
| Birch Cafe | 103 | 210.00 |
Cobalt Labs and Dune Studio are gone. WHERE runs after the join, and NULL = 'paid' is never true, so every unmatched row fails the filter. The query says LEFT JOIN but behaves like an inner join.
It gets worse when the assistant combines that filter with IS NULL to find customers with no paid order. A row cannot have status = 'paid' and a NULL order id at the same time, so the result is empty, and the assistant may tell you "no customers match".
How to spot it: any condition on a right-table column in WHERE (other than IS NULL) is a red flag. Move it into ON. Then check the row count: a left join from customers should never return fewer rows than there are customers.
Testing IS NULL on the wrong column
WHERE o.amount IS NULL looks the same as WHERE o.order_id IS NULL, but if some real orders have an empty amount, they sneak in. Use the right table's key.
Using COUNT(*) instead of COUNT(column)
COUNT(*) counts the row, and an unmatched customer still has a row. In the counting example above, COUNT(*) gives every customer at least 1. Count a right-table column instead. More in COUNT DISTINCT in SQL.
FAQ
What is the difference between LEFT JOIN and LEFT OUTER JOIN?
Nothing. OUTER is optional. Both keep every row from the left table.
When should I use a left join vs inner join?
Use an inner join when a row only matters if it has a match, such as order lines with a valid product. Use a left join when the question says "all", "every", "including those with none" or "which ones have no...".
How do I find records in one table that are not in another?
Left join the second table and keep rows where its key IS NULL, as in the anti-join above. WHERE NOT EXISTS (...) gives the same result and some people find it easier to read.
Why is my left join returning more rows than the left table?
Because some left rows match several right rows. Acme Dental has two orders, so it appears twice. If you need one row per customer, aggregate with GROUP BY, as in the counting example.
Is there a RIGHT JOIN?
Yes, it keeps every row from the right table instead. Most analysts never use it: swapping the table order and writing a left join is easier to read.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Customers with no completed orders” →