Joins I — semantics
Concept  ·  SQL & Querying

In an OUTER join, a condition in ON filters the join; the same condition in WHERE filters the result — different meaning.

The two clauses run at different moments. ON runs during matching — a failed condition just means 'no partner row', and the outer join keeps the left row anyway. WHERE runs after — and for the partnerless rows every right column is NULL, so a.term = 'spring' is UNKNOWN and the row dies. Same text, different clause, different question answered.

See it break
members
member
Ada
Ben
Cy
attendance
memberterm
Adaspring
Cyfall
The trap
SELECT
  m.member
FROM
  members m
  LEFT JOIN attendance a ON a.member = m.member
WHERE
  a.term = 'spring'
ORDER BY
  m.member;
The fix
SELECT
  m.member
FROM
  members m
  LEFT JOIN attendance a ON a.member = m.member
  AND a.term = 'spring'
ORDER BY
  m.member;
The trap
Ada
The fix
Ada
Ben
Cy

New choir members with no spring attendance vanished from the roster — the term filter in WHERE deleted the NULL-extended rows and turned the LEFT JOIN inner.

The rule

Put right-table filters into ON to keep the outer rows, or accept the inner semantics and write INNER JOIN so the query says so.

Shows up elsewhere
The LEFT JOIN that turned INNERWHERE keeps only TRUE
Try one →Prove it in practice →