What's the difference between filtering in the

问题:

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:

回答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.

回答2:

The results are exactly same. Using "ON" clause is more suggested due to increasing performance of the query.

Instead of requesting the data from tables then filtering, by using on clause, you first filter first data-set and then join the data to other tables. So, lesser data to match and faster result is given.

回答3:

There is no difference between the above two queries outputs both of them result same.

When you are using On Clause the join operation joins only those rows that matches the codidtion specified on ON Clause

Where as in case of Where Clause, the join opeartion joins all the rows and then filters out based on where condidtion Specified

So, obviously On Clause is more effective and should be preferred over where condidtion