LEFT JOIN & COALESCE

Keep the rows that have no match

Lesson 1 of 20 · 10 min · Module 1: Joins beyond the basics

An INNER JOIN drops every row without a partner, so a customer who never ordered simply disappears from your report. A LEFT JOIN keeps every row of the left table and fills the missing side with NULL.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Keep unmatched rows with LEFT JOIN
Count only real matches
Read NULLs produced by a join

The idea

Write the table you want to keep completely first, then LEFT JOIN the optional one. When there is no match, every column of the right table is NULL. COUNT(o.id) counts only real matches, while COUNT(*) would count the empty row as 1.

SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.id IN (6, 8);

01The left table is complete

Every customer appears at least once. Customers with three orders appear three times before grouping; Hugo, who never ordered, appears once with NULL in every orders column.

02COUNT(column) skips NULL

COUNT(o.id) ignores the NULL produced for Hugo and returns 0. COUNT(*) counts rows, so it would wrongly report one order for him.

SELECT COUNT(*) AS rows_counted, COUNT(o.id) AS real_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.id = 8;

Output

1 | 0

03Filters can undo a LEFT JOIN

A WHERE condition on the right table, such as WHERE o.status = 'paid', removes the NULL rows again. Put such conditions in the ON clause when unmatched rows must stay.

Your turn

Return name and the number of orders as orders (any status) for every customer, including customers with no orders. Sort by orders from most to fewest, then by name.

What the checks look for

Lists all eight customers, including Hugo with 0 orders

Try it in the editor →

Take this with you

LEFT JOIN keeps everything on the left; count a column from the right.

Go a little further

Return only customers who have never ordered by adding WHERE o.id IS NULL, a pattern called an anti-join.