MySQL LEFT JOIN: Keep All Rows from the Left Table

LEFT JOIN keeps every row of the left table. If a left row has a match in the right table, you get the combined row. If it has none, you still get the left row, with NULL in all the right-hand columns.

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Maya Shah',    'Pune'),
    ('Rohan Verma',  'Delhi'),
    ('Sana Ali',     'Pune'),
    ('Tara Nair',    'Kochi'),
    ('Uday Singh',   'Delhi');

INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
    (1, '2026-05-02',  680.00, 'delivered'),
    (1, '2026-05-20', 1299.00, 'delivered'),
    (2, '2026-05-21',  145.00, 'shipped'),
    (3, '2026-06-01',  450.00, 'delivered'),
    (3, '2026-06-05',   80.00, 'cancelled'),
    (3, '2026-06-18',  999.00, 'shipped'),
    (2, '2026-06-20',  220.00, 'pending');

query.sql

SQL

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

Output

OUTPUT

+-------------+-------+----------+---------+
| name        | city  | order_id | total   |
+-------------+-------+----------+---------+
| Maya Shah   | Pune  |        1 |  680.00 |
| Maya Shah   | Pune  |        2 | 1299.00 |
| Rohan Verma | Delhi |        3 |  145.00 |
| Rohan Verma | Delhi |        7 |  220.00 |
| Sana Ali    | Pune  |        4 |  450.00 |
| Sana Ali    | Pune  |        5 |   80.00 |
| Sana Ali    | Pune  |        6 |  999.00 |
| Tara Nair   | Kochi |     NULL |    NULL |
| Uday Singh  | Delhi |     NULL |    NULL |
+-------------+-------+----------+---------+
9 rows in set

Tara Nair and Uday Singh are present, with NULL for the order columns, because they have no orders. An inner join would have dropped them.

Find the rows with no match

Because unmatched rows have NULL on the right, you can find them with IS NULL on a column that can never be NULL in a real match (the right table’s primary key):

query.sql

SQL

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

Output

OUTPUT

+----+------------+
| id | name       |
+----+------------+
|  4 | Tara Nair  |
|  5 | Uday Singh |
+----+------------+
2 rows in set

These are the customers who never ordered. This is called an anti-join (Finding Missing Rows).

Counting with a LEFT JOIN

Count a column of the right table. COUNT(*) would count the “empty” row as 1:

query.sql

SQL

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

Output

OUTPUT

+-------------+--------+---------+
| name        | orders | spent   |
+-------------+--------+---------+
| Maya Shah   |      2 | 1979.00 |
| Sana Ali    |      3 | 1529.00 |
| Rohan Verma |      2 |  365.00 |
| Tara Nair   |      0 |    0.00 |
| Uday Singh  |      0 |    0.00 |
+-------------+--------+---------+
5 rows in set

Every customer appears, including those with zero orders and a total of 0. Use COALESCE to turn the NULL sum into 0.

Which table is on the left?

The table before LEFT JOIN is the one whose rows are all kept. To keep all orders even when the customer is missing, put orders on the left:

query.sql

SQL

SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
ORDER BY o.id
LIMIT 3;

Output

OUTPUT

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Maya Shah   |
|  2 | Maya Shah   |
|  3 | Rohan Verma |
+----+-------------+
3 rows in set

Here every order has a customer, so the result looks like an inner join. If a foreign key forbids orphan orders, left and inner joins agree.

LEFT JOIN and the WHERE clause

A condition on the right table in the WHERE clause can silently turn a LEFT JOIN into an INNER JOIN:

query.sql

SQL

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.id, o.id;

Output

OUTPUT

+-----------+----------+-----------+
| name      | order_id | status    |
+-----------+----------+-----------+
| Maya Shah |        1 | delivered |
| Maya Shah |        2 | delivered |
| Sana Ali  |        4 | delivered |
+-----------+----------+-----------+
3 rows in set

Tara and Uday are gone again. For them, o.status is NULL, and NULL = 'delivered' is not true, so WHERE removes the row. To keep all customers and show only delivered orders, move the condition into the ON clause:

query.sql

SQL

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.id, o.id;

Output

OUTPUT

+-------------+----------+-----------+
| name        | order_id | status    |
+-------------+----------+-----------+
| Maya Shah   |        1 | delivered |
| Maya Shah   |        2 | delivered |
| Rohan Verma |     NULL | NULL      |
| Sana Ali    |        4 | delivered |
| Tara Nair   |     NULL | NULL      |
| Uday Singh  |     NULL | NULL      |
+-------------+----------+-----------+
6 rows in set

All five customers are listed, and only delivered orders are matched. ON vs WHERE goes deeper.

Chains of left joins

When you join several tables, keep using LEFT JOIN for the optional ones. One INNER JOIN after a LEFT JOIN can throw away the rows you wanted to keep:

query.sql

SQL

CREATE TABLE order_notes (id INT AUTO_INCREMENT PRIMARY KEY, order_id INT, note VARCHAR(30));
INSERT INTO order_notes (order_id, note) VALUES (1, 'gift wrap');

SELECT c.name, o.id AS order_id, n.note
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN order_notes n ON n.order_id = o.id
ORDER BY c.id, o.id
LIMIT 4;

Output

OUTPUT

+-------------+----------+-----------+
| name        | order_id | note      |
+-------------+----------+-----------+
| Maya Shah   |        1 | gift wrap |
| Maya Shah   |        2 | NULL      |
| Rohan Verma |        3 | NULL      |
| Rohan Verma |        7 | NULL      |
+-------------+----------+-----------+
4 rows in set

Replacing the second LEFT JOIN with an INNER JOIN would remove every row without a note.

Summary

Question	Query shape
Everything on the left, plus matches	`A LEFT JOIN B ON ...`
Only rows on the left without a match	`A LEFT JOIN B ON ... WHERE B.key IS NULL`
Right-side filter, keep unmatched left rows	Put the filter in `ON`
Count per left row, zero allowed	`COUNT(B.key)` + `GROUP BY A.key`

warning

A hidden inner join

LEFT JOIN b ... WHERE b.column = 'x' is an inner join in disguise. If the point of the left join is to keep unmatched rows, a right-side condition goes in the ON clause.