What's the difference between filtering in the WHERE clause compared to the ON clause?

Question

I would like to know if there is any difference in using the WHERE clause or using the matching in the ON of the inner join.

The result in this case is the same.

First query:

with Catmin as 
(
    select categoryid, MIN(unitprice) as mn
    from production.Products
    group by categoryid
) 
select p.productname, mn
from Catmin 
inner join Production.Products p
on p.categoryid = Catmin.categoryid
and p.unitprice = Catmin.mn;

---

Second query:

with Catmin as 
(
    select categoryid, MIN(unitprice) as mn
    from production.Products
    group by categoryid
) 
select p.productname, mn
from Catmin 
inner join Production.Products p
on p.categoryid = Catmin.categoryid
where p.unitprice = Catmin.mn;          // this is changed

---

Result both queries:

Answer 1

My answer may be a bit off-topic, but I would like to highlight a problem that may occur when you turn your INNER JOIN into an OUTER JOIN.

In this case, the most important difference between putting predicates (test conditions) on the ON or WHERE clauses is that you can turn LEFT or RIGHT OUTER JOINS into INNER JOINS without noticing it, if you put fields of the table to be left out in the WHERE clause.

For example, in a LEFT JOIN between tables A and B, if you include a condition that involves fields of B on the WHERE clause, there's a good chance that there will be no null rows returned from B in the result set. Effectively, and implicitly, you turned your LEFT JOIN into an INNER JOIN.

On the other hand, if you include the same test in the ON clause, null rows will continue to be returned.

For example, take the query below:

SELECT * FROM A 
LEFT JOIN B
   ON A.ID=B.ID

The query will also return rows from A that do not match any of B.

Take this second query:

SELECT * FROM A 
LEFT JOIN B
WHERE A.ID=B.ID

This second query won't return any rows from A that don't match B, even though you think it will because you specified a LEFT JOIN. That's because the test A.ID=B.ID will leave out of the result set any rows with B.ID that are null.

That's why I favor putting predicates in the ON clause rather than in the WHERE clause.