SQL ON vs WHERE

What & Why

With a plain INNER JOIN, it genuinely doesn't matter whether an extra filtering condition lives inside ON or inside WHERE. The moment an outer join is involved, that stops being true. ON decides which rows count as a match while the join is happening. WHERE filters the result after the join has already completed — including any NULL-filled rows the outer join added to preserve unmatched rows. If a WHERE condition checks a column that's NULL for one of those rows, the comparison evaluates to NULL (not true), and WHERE quietly throws that row away — cancelling out the entire reason the outer join was used.

See How It Works

SELECT
  c.name AS campaign_name,
  l.id AS qualified_lead_id
FROM marketing.campaigns c
LEFT JOIN marketing.leads l
  ON l.campaign_id = c.id
 AND l.qualified_at IS NOT NULL
ORDER BY c.id, qualified_lead_id;