Lesson 15 of 26 · see the practice database

LEFT JOIN and missing matches

An inner join drops rows that have no partner. Often those are exactly the rows you care about: the customers who have not ordered, the products that have not sold. A LEFT JOIN keeps them.

The idea

A LEFT JOIN B returns every row of A. Where a row of A has a match in B, the columns of B are filled in. Where it has none, the columns of B are NULL.

"Left" simply means the table written first, on the left of the word JOIN.

SELECT c.name, o.id AS order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;

Every customer is here. A customer with three orders appears three times. A customer with no orders appears once, with NULL in the order columns.

Compare the row counts of the two kinds of join:

SELECT
  (SELECT COUNT(*) FROM customers c JOIN orders o ON o.customer_id = c.id) AS inner_rows,
  (SELECT COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id) AS left_rows;

The difference is the number of customers without orders.

Finding rows with no match

This is the most valuable use of a left join. Keep everything from the left, then keep only the rows where the right side came back empty.

SELECT c.name, c.city
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 ordered. The test must be IS NULL on a column of the right-hand table that can never be NULL in a real match. The primary key, o.id, is the safe choice.

Which product has nobody ever bought?

SELECT p.name, p.category
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;

Counting, including the zeros

In lesson 12, a count of orders per customer left out the customers with none. A left join puts them back. There is one thing to get right: count a column from the right table, not *.

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

COUNT(o.id) skips the NULLs, so a customer with no orders gets 0. COUNT(*) would count the single row that the left join keeps for that customer and report 1, which is wrong. Try it and see.

Totals work the same way, with COALESCE to turn a missing total into zero.

SELECT p.name, COALESCE(SUM(oi.quantity), 0) AS units_sold
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY units_sold
LIMIT 5;

The trap: a WHERE that undoes the left join

Suppose you want every customer, with their delivered orders if they have any. This looks right and is not.

SELECT c.name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'delivered'
ORDER BY c.name;

The customers with no orders have vanished. For them o.status is NULL, the test NULL = 'delivered' is not true, and WHERE throws the row out. The left join has quietly become an inner join.

The cure is to put the condition on the right-hand table into the ON, so that it is part of the matching and not a filter afterwards.

SELECT c.name, o.id AS order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'delivered'
ORDER BY c.name;

Now every customer appears. The rule to remember:

• A condition in ON decides which rows of the right table count as a match.
• A condition in WHERE decides which rows of the final result survive.

For an inner join the two give the same result. For a left join they do not.

RIGHT JOIN

A RIGHT JOIN B keeps every row of B. It is a left join written the other way round, and you can always get the same result by swapping the tables and using LEFT JOIN. Most people use only left joins, so that "the table I am keeping everything from" is always the first one. Here is the "never ordered" query from above, written with a right join. It returns the same customers:

SELECT c.name, c.city
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id
WHERE o.id IS NULL;

FULL JOIN

A FULL JOIN, also written FULL OUTER JOIN, keeps the unmatched rows of both tables. It is useful for comparing two lists, such as this month's customers against last month's. PostgreSQL, SQL Server, Oracle and recent versions of SQLite have it. MySQL does not; there, people combine a left join and a right join with UNION.

The join family

The word OUTER may be written in the middle three, as in LEFT OUTER JOIN. It changes nothing.

Which join should I use?

Ask what should happen to a row that has no partner. If it should disappear, use an inner join. If it should stay, with blanks, use a left join starting from the table whose rows must all be kept. When in doubt, compare the row counts of both and look at the rows that differ.

Try it yourself

SELECT p.name, COUNT(oi.product_id) AS lines FROM products p LEFT JOIN order_items oi ON oi.product_id = p.id GROUP BY p.id, p.name;

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

SELECT c.name, MAX(o.order_date) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.name ORDER BY c.name;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.