How will the result change if the filter condition is moved from ON to WHERE during JOIN?

When using JOIN in SQL, the filtering condition placed in ON affects which rows from the joined table are included in the result before the join. If this condition is moved to WHERE, then the tables are first joined based on the ON condition, and then filtering is applied to the result of the join.

This is especially noticeable with LEFT JOIN and similar:

• If the filtering condition is in ON, then rows from the left table without matches in the right table are preserved (with NULL in the right table's fields).
• If the condition is moved to WHERE, such rows are filtered out, as WHERE is applied after the join and excludes rows with NULL.

Example:

SELECT * FROM A
LEFT JOIN B ON A.id = B.a_id AND B.status = 'active'

and

SELECT * FROM A
LEFT JOIN B ON A.id = B.a_id
WHERE B.status = 'active'

In the second case, rows from A without active records in B are excluded, while in the first, they are preserved with NULLs in B's fields.

Thus, moving the condition from ON to WHERE can change the number and composition of rows in the result.