与 ON 子句相比，WHERE 子句中的过滤有什么区别? [英] What's the difference between filtering in the WHERE clause compared to the ON clause?

问题描述

我想知道使用WHERE子句和使用内连接ON中的匹配有什么区别.

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.

第一次查询:

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:

推荐答案

我的回答可能有点题外话，但我想强调一个当您将 INNER JOIN 变成 OUTER JOIN 时可能出现的问题.

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.

在这种情况下，将谓词(测试条件)放在 ON 或 WHERE 子句上的最重要区别在于，您可以将 LEFT 或 RIGHT OUTER JOINS 转换为 INNER JOINS 而无需注意，如果您将表的字段放在 WHERE 子句中.

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.

例如，在表 A 和 B 之间的 LEFT JOIN 中，如果在 WHERE 子句中包含涉及 B 字段的条件，则结果集中很可能不会从 B 返回空行.您有效且隐含地将 LEFT JOIN 转换为 INNER JOIN.

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 子句中包含相同的测试，将继续返回空行.

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

以下面的查询为例:

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

该查询还将返回 A 中不匹配任何 B 的行.

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

进行第二个查询:

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

第二个查询不会从 A 返回任何与 B 不匹配的行，即使您认为它会返回，因为您指定了一个 LEFT JOIN.那是因为测试 A.ID=B.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.

这就是为什么我喜欢将谓词放在 ON 子句中而不是放在 WHERE 子句中.

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

这篇关于与 ON 子句相比，WHERE 子句中的过滤有什么区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！