MySQL JOIN ON vs WHERE: Why the Difference Matters

A very common question: “Should this condition go in ON or in WHERE?” The answer depends on the join type.

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');

Inner joins: no difference

For an INNER JOIN, a condition gives the same result in either place:

query.sql

SQL

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

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

Output

OUTPUT

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

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

The same three rows both times. Put the matching rule in ON, and the filters in WHERE. It reads better.

Left joins: a big difference

With a LEFT JOIN, the two places do different things:

ON decides how rows are matched. Left rows without a match are kept, with NULLs.
WHERE is applied after the join, and removes rows from the final result, including the “kept” ones with NULLs.

Condition in ON: keep all customers, and match only delivered orders:

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

Rohan only has a shipped and a pending order, so he appears with NULL. Every customer is listed.

Condition in WHERE: remove everyone who has no delivered order:

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

Rohan, Tara and Uday are gone. The WHERE turned the left join into an inner join.

The decision table

What you want	Put the condition
A filter on the left table (the kept one)	`WHERE`
A filter on the right table, but keep all left rows	`ON`
A filter on the right table that removes left rows without matches	`WHERE` (then you may as well use an `INNER JOIN`)
Find left rows with no match	`WHERE right.key IS NULL`

The anti-join with an extra condition

“Customers with no delivered order” (they may have other orders):

query.sql

SQL

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

Output

OUTPUT

+-------------+
| name        |
+-------------+
| Rohan Verma |
| Tara Nair   |
| Uday Singh  |
+-------------+
3 rows in set

Here the status condition must be in ON. If it were in WHERE together with o.id IS NULL, the result would be empty. The filter on delivered in WHERE would remove the very rows you are looking for.

Mix both

query.sql

SQL

SELECT c.name, c.city, COUNT(o.id) AS delivered_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'delivered'
WHERE c.city = 'Pune'
GROUP BY c.id, c.name, c.city
ORDER BY c.name;

Output

OUTPUT

+-----------+------+------------------+
| name      | city | delivered_orders |
+-----------+------+------------------+
| Maya Shah | Pune |                2 |
| Sana Ali  | Pune |                1 |
+-----------+------+------------------+
2 rows in set

WHERE c.city = 'Pune' filters the left table (customers from Pune). ON ... o.status = 'delivered' limits which orders are counted. Pune customers with no delivered orders still appear with a 0.

A quick test

If you move a condition from WHERE into ON (or back) and the result does not change, it was not affecting the unmatched rows. If it changes, you were relying on one of the two meanings. Know which one you want.

lightbulb

The rule of thumb

For outer joins: conditions that describe which right rows may match go in ON. Conditions that describe which final rows to keep go in WHERE.