Understanding LEFT JOIN ON vs WHERE Conditions in SQL

When a LEFT JOIN is executed, the database first creates a temporary result set that contains all rows from the left table and matching rows from the right table; rows from the left table are kept even if the join condition is false.

The ON clause is evaluated during the join construction. It determines which rows from the right table are paired with each left‑table row, but it never removes rows from the left side. For example:

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

Adding additional predicates to the ON clause only restricts the right‑table columns that are displayed, while still returning every left‑table row:

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

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

In contrast, the WHERE clause is applied after the temporary table is built. It filters the combined rows, and any condition that evaluates to false removes the entire row, including the left‑table part. Thus:

select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA';

will discard left‑table rows that have no matching right‑table row with name='AAA' , effectively turning the LEFT JOIN into an INNER JOIN for those rows.

If the same predicate is placed inside the ON clause, the join still returns all left rows, showing NULLs for right‑table columns when the condition fails:

select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA');

The article also notes that INNER JOIN does not have this special behavior; placing a condition in ON or WHERE yields the same result because rows from both sides must match.

Understanding this distinction helps avoid unexpected duplicate or missing rows when writing complex queries involving LEFT, RIGHT, or FULL joins.