SQL course · Module 4: Joins · Lesson 13 of 40
Quick answer: A SQL LEFT JOIN returns every row of the left table, plus the matching rows of the right table. When a left row has no match, it still appears once, with NULL in the right table’s columns: SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id; lists customers who have never ordered too. A RIGHT JOIN does the same for the right table, and a FULL OUTER JOIN keeps unmatched rows from both sides. MySQL’s join syntax has no FULL OUTER JOIN, and SQLite supports RIGHT and FULL joins only from version 3.39.0.
In this lesson you will learn:
- how a SQL LEFT JOIN keeps rows that have no match, and where the NULLs come from
- how to count correctly after a LEFT JOIN, and turn NULL totals into 0
- how to find rows with no match (
WHERE ... IS NULL) - why a condition in
WHEREcan turn a LEFT JOIN into an inner join - how RIGHT and FULL OUTER JOIN work, and which databases support them
Before you start: Lesson 12, SQL INNER JOIN Explained and Lesson 6, SQL NULL Values: IS NULL, COALESCE and Three-Valued Logic.
We ran every example on PostgreSQL 16.15 with the shop database, and checked the current PostgreSQL 18 manual. We turned on \pset null '(null)' from Lesson 6 so the NULLs are visible.
Table of Contents
The problem: INNER JOIN drops rows
In Lesson 12, this inner join had no row for Omar Haddad (customer 10), because he has no orders:
SELECT c.id, c.name, o.id AS order_id, o.order_date
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id >= 8
ORDER BY c.id, o.id;
id | name | order_id | order_date
----+--------------+----------+------------
8 | NOAH WILSON | 7 | 2026-07-14
8 | NOAH WILSON | 15 | 2026-09-02
9 | Chloe Dubois | 12 | 2026-08-18
9 | Chloe Dubois | 18 | 2026-10-06
(4 rows)
For a list of customers, that is wrong: a customer with no orders is still a customer. Outer joins fix this.
SQL LEFT JOIN: keep every row of the left table
Change JOIN to LEFT JOIN:
SELECT c.id, c.name, o.id AS order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.id >= 8
ORDER BY c.id, o.id;
id | name | order_id | order_date
----+--------------+----------+------------
8 | NOAH WILSON | 7 | 2026-07-14
8 | NOAH WILSON | 15 | 2026-09-02
9 | Chloe Dubois | 12 | 2026-08-18
9 | Chloe Dubois | 18 | 2026-10-06
10 | Omar Haddad | (null) | (null)
(5 rows)
Omar is back, with NULL in every column that comes from orders. The PostgreSQL manual describes it in two steps: “First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1” (PostgreSQL docs: Joined Tables).
“Left” means the table written before LEFT JOIN, here customers. Over the whole database, we get the inner join’s 22 rows plus one for Omar:
SELECT count(*) AS joined_rows, count(DISTINCT c.id) AS customers
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
joined_rows | customers
-------------+-----------
23 | 10
(1 row)
Counting after a LEFT JOIN
Now we can count orders for every customer, using GROUP BY from Lesson 10. Compare count(o.id) with count(*):
SELECT c.name, count(o.id) AS order_count, count(*) AS rows_in_group
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY order_count, c.name;
name | order_count | rows_in_group
---------------+-------------+---------------
Omar Haddad | 0 | 1
Chloe Dubois | 2 | 2
Daniel Weber | 2 | 2
Emma Clarke | 2 | 2
Hira Siddiqui | 2 | 2
NOAH WILSON | 2 | 2
bilal ahmed | 2 | 2
Lucas Martin | 3 | 3
Sofia Rossi | 3 | 3
Ayesha Malik | 4 | 4
(10 rows)
Omar’s group has one row (full of NULLs), so count(*) says 1. count(o.id) skips NULLs (Lesson 9) and gives the right answer: 0. After a LEFT JOIN, count a column from the right table, ideally its primary key.
Find rows with no match
Unmatched rows have NULL in the right table’s columns, so IS NULL keeps only them. Customers who never ordered:
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
id | name
----+-------------
10 | Omar Haddad
(1 row)
Products that nobody has ordered:
SELECT p.id, p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;
id | name
----+-----------------
9 | Ergonomic Chair
(1 row)
Test a column that is never NULL in a real match, such as the join key or a primary key. This pattern is called an anti-join; Lesson 14 shows another way to write it.
Turn NULL totals into 0 with COALESCE
sum() over a group with no real rows gives NULL, not 0. Here are the units ordered per product:
SELECT p.name, sum(oi.quantity) AS units, coalesce(sum(oi.quantity), 0) AS units_ordered
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY units_ordered DESC, p.name;
name | units | units_ordered
-----------------------------+--------+---------------
Gel Pens 10-Pack | 16 | 16
Notebook Pack | 14 | 14
Wireless Mouse | 9 | 9
Desk Lamp | 5 | 5
USB-C Hub | 5 | 5
HD Webcam | 4 | 4
Laptop Stand | 4 | 4
Mechanical Keyboard | 4 | 4
Noise-Cancelling Headphones | 4 | 4
Ergonomic Chair | (null) | 0
(10 rows)
coalesce from Lesson 6 replaces the NULL with 0, which is what a report should show.
ON vs WHERE: the most common LEFT JOIN mistake
How many cancelled orders does each customer have? This looks right, but it is not:
SELECT c.name, count(o.id) AS cancelled_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'cancelled'
GROUP BY c.id, c.name
ORDER BY c.id;
name | cancelled_orders
--------------+------------------
Ayesha Malik | 1
Chloe Dubois | 1
(2 rows)
Eight customers vanished. As Lesson 11 showed, WHERE is processed after FROM, so it filters the joined rows. Omar’s o.status is NULL, so o.status = 'cancelled' is unknown, not true (Lesson 6), and the other seven only have orders with other statuses. WHERE removes them all, and the LEFT JOIN behaves like an inner join.
Move the condition into ON:
SELECT c.name, count(o.id) AS cancelled_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'cancelled'
GROUP BY c.id, c.name
ORDER BY c.id;
name | cancelled_orders
---------------+------------------
Ayesha Malik | 1
Lucas Martin | 0
Sofia Rossi | 0
bilal ahmed | 0
Emma Clarke | 0
Daniel Weber | 0
Hira Siddiqui | 0
NOAH WILSON | 0
Chloe Dubois | 1
Omar Haddad | 0
(10 rows)
Now the condition decides which orders match, and every customer stays. The manual explains: “a restriction placed in the ON clause is processed before the join, while a restriction placed in the WHERE clause is processed after the join. That does not matter with inner joins, but it matters a lot with outer joins.” The rule: a condition on the right table goes in ON. A condition on the left table (for example c.country = 'Pakistan') goes in WHERE.
RIGHT JOIN
A RIGHT JOIN keeps every row of the right table, the one after RIGHT JOIN. So orders RIGHT JOIN customers gives the same rows as customers LEFT JOIN orders:
SELECT c.id, c.name, o.id AS order_id, o.order_date
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id
WHERE c.id >= 8
ORDER BY c.id, o.id;
id | name | order_id | order_date
----+--------------+----------+------------
8 | NOAH WILSON | 7 | 2026-07-14
8 | NOAH WILSON | 15 | 2026-09-02
9 | Chloe Dubois | 12 | 2026-08-18
9 | Chloe Dubois | 18 | 2026-10-06
10 | Omar Haddad | (null) | (null)
(5 rows)
Any RIGHT JOIN can be written as a LEFT JOIN by swapping the tables, and a query is easier to read when the main table comes first. The MySQL manual says: “To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN” (MySQL 9.7 manual: JOIN Clause).
FULL OUTER JOIN: keep unmatched rows from both sides
In the shop, every order has a customer, so customers FULL JOIN orders would equal the LEFT JOIN. A FULL OUTER JOIN helps when both sides can have rows without a partner, such as two lists you compare. Which customers ordered in August, and which in September?
SELECT DISTINCT customer_id FROM orders
WHERE order_date >= '2026-08-01' AND order_date < '2026-09-01'
ORDER BY customer_id;
customer_id
-------------
1
3
7
9
(4 rows)
SELECT DISTINCT customer_id FROM orders
WHERE order_date >= '2026-09-01' AND order_date < '2026-10-01'
ORDER BY customer_id;
customer_id
-------------
1
4
5
8
(4 rows)
To join the two lists, put each query in brackets in FROM with an alias (a for August, s for September). These are subqueries (Lesson 20); for now, read each one as a small table:
SELECT a.customer_id AS aug_customer, s.customer_id AS sep_customer
FROM (SELECT DISTINCT customer_id FROM orders
WHERE order_date >= '2026-08-01' AND order_date < '2026-09-01') AS a
FULL OUTER JOIN
(SELECT DISTINCT customer_id FROM orders
WHERE order_date >= '2026-09-01' AND order_date < '2026-10-01') AS s
ON s.customer_id = a.customer_id
ORDER BY coalesce(a.customer_id, s.customer_id);
aug_customer | sep_customer
--------------+--------------
1 | 1
3 | (null)
(null) | 4
(null) | 5
7 | (null)
(null) | 8
9 | (null)
(7 rows)
Customer 1 ordered in both months. Customers 3, 7 and 9 ordered only in August, and 4, 5 and 8 only in September; the NULLs show which side is missing. coalesce picks whichever id is not NULL, so the rows sort by customer.

With the same two lists, an inner join returns only customer 1, a LEFT JOIN returns the four August customers, and a RIGHT JOIN the four September customers (we ran all four).
Which databases support RIGHT and FULL JOIN?
| Database | LEFT JOIN | RIGHT JOIN | FULL OUTER JOIN |
|---|---|---|---|
| PostgreSQL | yes | yes | yes |
| SQL Server | yes | yes | yes |
| MySQL | yes | yes | no (not in its join syntax) |
| SQLite | yes | from 3.39.0 | from 3.39.0 |
SQL Server lists LEFT, RIGHT and FULL [OUTER] in its join syntax (Microsoft Learn: FROM clause). The MySQL 9.7 join syntax has LEFT and RIGHT [OUTER] JOIN but no FULL (MySQL 9.7 manual: JOIN Clause), and so does the newer MySQL 26.10 manual. SQLite added “(long overdue) support for RIGHT and FULL OUTER JOIN” in version 3.39.0, released on 2022-06-25 (SQLite release log: 3.39.0). Our test with SQLite 3.53.1 gave the same rows as PostgreSQL for all four join types.
In MySQL, build a full join from two parts: the LEFT JOIN, plus the RIGHT JOIN rows with no match on the left. UNION ALL (Lesson 15) stacks the two results:
SELECT a.customer_id AS aug_customer, s.customer_id AS sep_customer
FROM (SELECT DISTINCT customer_id FROM orders
WHERE order_date >= '2026-08-01' AND order_date < '2026-09-01') AS a
LEFT JOIN
(SELECT DISTINCT customer_id FROM orders
WHERE order_date >= '2026-09-01' AND order_date < '2026-10-01') AS s
ON s.customer_id = a.customer_id
UNION ALL
SELECT a.customer_id, s.customer_id
FROM (SELECT DISTINCT customer_id FROM orders
WHERE order_date >= '2026-08-01' AND order_date < '2026-09-01') AS a
RIGHT JOIN
(SELECT DISTINCT customer_id FROM orders
WHERE order_date >= '2026-09-01' AND order_date < '2026-10-01') AS s
ON s.customer_id = a.customer_id
WHERE a.customer_id IS NULL
ORDER BY aug_customer, sep_customer;
aug_customer | sep_customer
--------------+--------------
1 | 1
3 | (null)
7 | (null)
9 | (null)
(null) | 4
(null) | 5
(null) | 8
(7 rows)
The same seven rows as the FULL OUTER JOIN, in a different order. We ran this on PostgreSQL and SQLite, not on MySQL. Every part of it is in the MySQL 9.7 manual: subqueries in FROM with a name (Derived Tables) and UNION [ALL | DISTINCT] (UNION Clause). MySQL sorts NULLs first (Working with NULL Values), so there the September-only rows come first.
Try it yourself
Exercise 1: Order lines per product
Show every product with the number of order lines it appears in, including products that were never ordered. Sort by that number, smallest first.
Exercise 2: Shipped orders per customer
Show every customer with their number of shipped orders, 0 if they have none. Most shipped orders first.
Exercise 3: Customers with no cancelled order
Use a LEFT JOIN to list the customers who have never had a cancelled order (customers with no orders at all count too).
Solutions
Solution 1
SELECT p.name, count(oi.order_id) AS order_lines
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY order_lines, p.name;
name | order_lines
-----------------------------+-------------
Ergonomic Chair | 0
HD Webcam | 3
Desk Lamp | 4
Laptop Stand | 4
Mechanical Keyboard | 4
Noise-Cancelling Headphones | 4
USB-C Hub | 4
Gel Pens 10-Pack | 5
Notebook Pack | 6
Wireless Mouse | 6
(10 rows)
count(oi.order_id) gives 0 for the chair; count(*) would give 1.
Solution 2
SELECT c.name, count(o.id) AS shipped_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'shipped'
GROUP BY c.id, c.name
ORDER BY shipped_orders DESC, c.name;
name | shipped_orders
---------------+----------------
Daniel Weber | 1
Hira Siddiqui | 1
Lucas Martin | 1
Ayesha Malik | 0
Chloe Dubois | 0
Emma Clarke | 0
NOAH WILSON | 0
Omar Haddad | 0
Sofia Rossi | 0
bilal ahmed | 0
(10 rows)
The status test is in ON, so all 10 customers stay.
Solution 3
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'cancelled'
WHERE o.id IS NULL
ORDER BY c.id;
id | name
----+---------------
2 | Lucas Martin
3 | Sofia Rossi
4 | bilal ahmed
5 | Emma Clarke
6 | Daniel Weber
7 | Hira Siddiqui
8 | NOAH WILSON
10 | Omar Haddad
(8 rows)
ON matches only cancelled orders; WHERE o.id IS NULL keeps the customers that found no such match. Ayesha and Chloe are left out.
Common mistakes
- Filtering the right table in WHERE.
WHERE o.status = 'cancelled'after a LEFT JOIN removed 8 of the 10 customers. Put conditions on the right table inON. - Counting rows instead of matches.
count(*)counts the NULL row, so Omar got 1 order instead of 0. Count a right-table column such ascount(o.id). - An inner JOIN after a LEFT JOIN.
customers c LEFT JOIN orders o ... JOIN order_items oi ON oi.order_id = o.idreturned 9 customers, not 10: Omar’so.idis NULL, so the inner join drops him again. UseLEFT JOINfor every table after the first outer join. - Showing NULL instead of 0.
sum()of no rows is NULL. Wrap it incoalesce(..., 0).
Frequently asked questions
What is the difference between LEFT JOIN and LEFT OUTER JOIN?
There is none. The PostgreSQL manual says “The words INNER and OUTER are optional in all forms”, and our test gave 23 rows either way. The same is true for RIGHT OUTER JOIN and FULL OUTER JOIN.
When should I use RIGHT JOIN instead of LEFT JOIN?
Almost never. Swap the two tables and use LEFT JOIN, as the MySQL manual recommends for portability. You will still meet RIGHT JOIN in other people’s code, so learn to read it.
Does MySQL support FULL OUTER JOIN?
No. The join syntax in the MySQL manual (9.7 and 26.10) lists only LEFT and RIGHT outer joins. Combine a LEFT JOIN with the unmatched rows of a RIGHT JOIN using UNION ALL, as shown above. PostgreSQL, SQL Server and SQLite 3.39.0 or newer support FULL OUTER JOIN directly.
Summary
- A SQL LEFT JOIN keeps every row of the left table; unmatched rows get NULL in the right table’s columns.
- After a LEFT JOIN, count a right-table column (
count(o.id)) and usecoalesce(sum(...), 0)for totals. LEFT JOIN ... WHERE right_key IS NULLfinds rows with no match, such as a customer with no orders.- Put conditions on the right table in
ON; inWHEREthey turn the LEFT JOIN into an inner join. - RIGHT JOIN is a LEFT JOIN with the tables swapped. FULL OUTER JOIN keeps unmatched rows from both sides; it is not in MySQL’s join syntax, and SQLite has it from 3.39.0.
Reports that must show zeros are built with LEFT JOIN. Use it in your own project with the data analyst portfolio guide, and later in the dbt tutorial for beginners, where each model is a SQL SELECT.
Next lesson
Next: Lesson 14, SQL Self Join, CROSS JOIN, Semi-Joins and Anti-Joins.
Sources
- PostgreSQL documentation: Table Expressions (Joined Tables)
- PostgreSQL tutorial: Joins Between Tables
- MySQL 9.7 Reference Manual: JOIN Clause
- MySQL 26.10 Reference Manual: JOIN Clause
- MySQL 9.7 Reference Manual: Working with NULL Values
- SQLite: SELECT (LEFT, RIGHT and FULL OUTER JOIN)
- SQLite release log: 3.39.0
- MySQL 9.7 Reference Manual: Derived Tables
- MySQL 9.7 Reference Manual: UNION Clause
- Microsoft Learn: FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)





