How LEFT JOIN Works with WHERE: When to Filter in ON vs WHERE

Background

When a database joins two or more tables, it first builds an intermediate temporary table and then returns the final result to the user.

LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (even if there is no matching row in the right table).

LEFT JOIN Syntax

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name

In some databases, LEFT JOIN is called LEFT OUTER JOIN .

Sample Data

Two tables are used in the examples:

sql_person : id, username, address, city

sql_order : id, order_no, person_id, status

Sample rows illustrate persons (Apple, Google, Huawei) and their orders with varying status values.

Example 1: Using WHERE After LEFT JOIN

Goal: List all persons and their orders, but only where the order status equals 1.

SELECT
  sql_person.username,
  sql_person.city,
  sql_order.order_no,
  sql_order.status
FROM sql_person
LEFT JOIN sql_order ON sql_person.id = sql_order.person_id
WHERE sql_order.`status` = 1
ORDER BY sql_order.order_no.id;

Execution steps:

The ON condition sql_person.id = sql_order.person_id creates the temporary join result.

The WHERE clause sql_order.status = 1 filters that temporary result, discarding rows where the condition is false or null.

Result shows only rows with status = 1, plus any left‑table rows that have matching orders meeting the condition.

Example 2: Placing the Condition in the ON Clause

Goal: Achieve the same filtering without a separate WHERE clause.

SELECT
  sql_person.username,
  sql_person.city,
  sql_order.order_no,
  sql_order.status
FROM sql_person
LEFT JOIN sql_order ON (sql_person.id = sql_order.person_id AND sql_order.status = 1)
ORDER BY sql_order.order_no;

Execution steps:

The ON condition now includes sql_order.status = 1, so only matching rows satisfy both join criteria.

Rows from the left table without a matching right‑table row (or with status not equal to 1) appear with (null) values for the right‑table columns.

This approach retains all left‑table rows, showing (null) for orders that do not meet the status condition.

Note: When the condition is placed in the WHERE clause, rows that do not satisfy the condition are removed entirely; when placed in the ON clause, the left‑table rows are preserved with nulls.

Key Takeaways

The database creates a temporary table when joining tables.

The WHERE clause filters the temporary result after the join.

Putting the filter in the ON clause filters during the join, preserving left‑table rows with nulls when the condition is false.

Using WHERE after a LEFT JOIN can effectively turn the join into an inner join for rows that do not meet the condition.

Choosing between ON and WHERE depends on whether you need to keep unmatched left rows.