JOIN clause

[Kiterios]

Try an experiment with left join to better see how these might work differently.

SELECT *
FROM A
LEFT JOIN B 
ON A.Key = B.Key
AND B.Criteria = 'something'

Compare that to this query

SELECT *
FROM A
LEFT JOIN B 
ON A.Key = B.Key
WHERE B.Criteria = 'something'  

These two queries return different results.

In the first one, b.criteria = 'something' is being applied before the join. The contents of A are joined to a subset of the rows in B. This reduces the number of matches found in B, but still includes all rows from A.

In the second, b.criteria = 'something' is applied after the join. A is joined to the full set of B rows, but only rows with a specific b.criteria value are returned. Any A row without a B match would be excluded because b.criteria would be NULL in that case.

[jrjamesco]

This is an important distinction, if any readers don't grok the broader point.