Understanding LEFT JOIN: ON vs WHERE Conditions in SQL

When using LEFT JOIN , the ON condition determines how rows from the right table are matched but does not filter rows from the left table; all left‑table rows are always returned.

The WHERE clause is applied after the temporary join result is created, so it can filter out rows and the LEFT JOIN guarantee of returning all left‑table records is lost.

Example without additional filtering:

<code>select * from student s left join class c on s.classId=c.id order by s.id</code>

Example with an AND condition in the ON clause (filters the right‑table rows but still returns all left‑table rows):

<code>select * from student s left join class c on s.classId=c.id and s.name="张三" order by s.id</code>

Example with an AND condition in the ON clause that filters by a column of the right table:

<code>select * from student s left join class c on s.classId=c.id and c.name="三年级三班" order by s.id</code>

Key point: ON conditions are evaluated while building the intermediate result table; they never remove rows from the left side. WHERE conditions are evaluated after the intermediate table is built, and they can remove rows, effectively turning the LEFT JOIN into an inner join if the condition is not met.

Illustrative tables:

id

size

1

10

2

20

3

30

size

name

10

AAA

20

BBB

30

CCC

Two SQL queries illustrate the difference:

<code>select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'</code>

In this query the WHERE clause filters the intermediate result, removing rows where the condition is false.

<code>select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')</code>

Here the additional condition is placed in the ON clause; it still returns all rows from tab1 , but only matches rows from tab2 that satisfy the condition, leaving unmatched left rows with NULLs for right‑table columns.

The article concludes that LEFT, RIGHT, and FULL joins always preserve rows from the specified side(s) regardless of the truth of ON conditions, whereas INNER JOIN does not have this special behavior, making ON and WHERE interchangeable for inner joins.