Understanding LEFT JOIN Behavior and the Difference Between ON and WHERE Clauses in SQL

When using LEFT JOIN, the ON clause determines which rows from the right table are matched but does not filter rows from the left table; all left‑table rows appear in the result regardless of the ON condition.

The WHERE clause is applied after the temporary result of the join is created, and it can filter out rows, potentially removing the left‑join effect.

Examples:

select * from student s left join class c on s.classId=c.id order by s.id;

Adding a condition in ON:

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

Adding a condition in WHERE (not shown) would filter the final result.

Two sample queries illustrate the difference between placing a filter in WHERE versus in ON, showing how the first query returns all left‑table rows then filters by WHERE, while the second query keeps all left rows even if the ON condition is false.

The article also notes that LEFT, RIGHT, and FULL joins always retain rows from the preserved side, whereas INNER JOIN treats ON and WHERE equivalently.

At the end, a call‑to‑action invites readers to follow a public account for a collection of MySQL interview questions.