LEFT JOIN: rows without a match too

In short: LEFT JOIN keeps every row of the left table, even those with no match in the right table, and fills the missing columns with NULL.

Problem

Who has not ordered?

Marketing wants to write to the customers who signed up but have not ordered anything yet. In the last lesson JOIN returned 60 rows, and Teodora and Yavor were not among them.

JOIN keeps only the pairs. A row without a match disappears without a warning. Here those are exactly the rows we need.

The idea

LEFT JOIN

Compare the two queries. The first uses JOIN:

SQLdatabase: shop

SELECT c.name, o.id, o.ordered_at
FROM customers c
JOIN orders o ON o.customer_id = c.id;

SELECT c.name, o.id, o.ordered_at FROM customers c JOIN orders o ON o.customer_id = c.id;

▶ RunRestore the queryFresh database

The second uses LEFT JOIN:

SQLdatabase: shop

SELECT c.name, o.id, o.ordered_at
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

SELECT c.name, o.id, o.ordered_at FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;

▶ RunRestore the queryFresh database

62 rows instead of 60. LEFT JOIN keeps every row of the left table (the one in FROM), even if there is no match in the right one. Where there is no match, the columns of the right table are NULL. Scroll to the end: Teodora and Yavor are there, with NULL for the number and the date.

From here the customers without orders are one step away:

SQLdatabase: shop

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

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

▶ RunRestore the queryFresh database

WHERE o.id IS NULL keeps only the rows where no order was found. We check o.id because the primary key of an order that was found is never empty.

See it

An empty answer is an answer too

Are there products that nobody has ever ordered? The same approach, with order_items:

SQLdatabase: shop

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

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

▶ RunRestore the queryFresh database

Not a single row. Every one of the 25 products has been ordered at least once. A table with no rows is not an error: the query has answered “there are none”.

Try it

The empty categories

Which categories have no product directly in them? Show the category name.

SQLdatabase: shop

SELECT cat.name
FROM categories cat
JOIN products p ON p.category_id = cat.id;

SELECT cat.name FROM categories cat JOIN products p ON p.category_id = cat.id;

Check▶ Run

See it

Counting with the zeros

How many orders does each customer have, including those with none? A first attempt with COUNT(*):

SQLdatabase: shop

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

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

▶ RunRestore the queryFresh database

Teodora and Yavor have 1 order each? No. COUNT(*) counts rows, and LEFT JOIN gave each of them one row full of NULLs. Count what has a value, COUNT(o.id):

SQLdatabase: shop

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;

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;

▶ RunRestore the queryFresh database

COUNT(column) skips the NULLs, so they come out with 0. With LEFT JOIN, count a column of the right table, preferably its primary key.

Try it

Reviews per product

Show every product and how many reviews it has, including the products without a review (with 0).

SQLdatabase: shop

SELECT p.name, COUNT(r.id) AS reviews
FROM products p
JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name;

SELECT p.name, COUNT(r.id) AS reviews FROM products p JOIN reviews r ON r.product_id = p.id GROUP BY p.id, p.name;

Check▶ Run

See it

The WHERE trap

A report for October: all customers and how many orders they have placed since 1 October 2026. The date condition goes in WHERE:

SQLdatabase: shop

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.ordered_at >= '2026-10-01'
GROUP BY c.id, c.name;

SELECT c.name, COUNT(o.id) AS orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.ordered_at >= '2026-10-01' GROUP BY c.id, c.name;

▶ RunRestore the queryFresh database

1. Why did WHERE o.ordered_at >= … remove the customers without orders in October?

Because GROUP BY removes empty groupsBecause LEFT JOIN does not work with datesBecause for them o.ordered_at is NULL, a comparison with NULL is not true, and WHERE throws them out

Check

Five rows, but there are 20 customers. A condition on the right table in WHERE turns a LEFT JOIN into a plain JOIN. Its place is in ON: there it says which orders to join, and all the customers stay.

SQLdatabase: shop

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.ordered_at >= '2026-10-01'
GROUP BY c.id, c.name;

SELECT c.name, COUNT(o.id) AS orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.ordered_at >= '2026-10-01' GROUP BY c.id, c.name;

▶ RunRestore the queryFresh database

20 customers, most of them with 0. The rule: with LEFT JOIN, a condition on the right table goes in ON. The exception is IS NULL, when you are looking for the missing ones.

Exercise

Two tasks

1. The shop will invite the customers who have not written a review yet to write one. Who are they? Show the name.

The customers without a single review:

SQLdatabase: shop

Check▶ Run

2. The “We miss you” campaign is for the customers who have not placed an order since 1 July 2026 (including those who have never ordered). Show the name and email.

The customers without an order since 2026-07-01:

SQLdatabase: shop

Check▶ Run

More precisely, if you are curious

There are also RIGHT JOIN (keeps the rows of the right table) and FULL JOIN (keeps both sides). SQLite understands them since version 3.39. RIGHT JOIN is rarely written: it is the same as LEFT JOIN with the tables swapped. MySQL has no FULL JOIN.

SQLdatabase: shop

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

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

▶ RunRestore the queryFresh database

Only the customers without orders come out here, because every order has a customer.

Test

Check yourself

1. FROM customers c LEFT JOIN orders o … Which customers come out?

All customers, and those without orders have NULL in the columns of ordersOnly those with ordersOnly those without orders

2. How do you find the customers without orders?

LEFT JOIN orders o … WHERE o.id IS NULLJOIN orders o … WHERE o.id IS NULLLEFT JOIN orders o … WHERE o.id = NULL

3. A customer without orders, LEFT JOIN and GROUP BY. What does COUNT(*) give for them?

NULL01

4. You want all customers and only their October orders. Where does the date condition go?

In WHEREIn ON, next to the joining conditionIt does not matter

Check

Start the lesson over

Related lessons

• Builds on: Aggregate functions: COUNT, SUM, AVG · GROUP BY: a report by groups · Primary key and foreign key · JOIN: two tables in one query
• Continues in: Many tables, and a table joined to itself · UNION, INTERSECT, EXCEPT and CROSS JOIN · A report in steps: WITH · Trees and sequences: WITH RECURSIVE
• Tasks on this topic: Joining tables