SQL JOIN 或 WHERE 内的条件

Condition within JOIN or WHERE

提问by Steve Dignan

Is there any difference (performance, best-practice, etc...) between putting a condition in the JOIN clause vs. the WHERE clause?

在 JOIN 子句和 WHERE 子句中放置条件之间有什么区别（性能、最佳实践等...）？

For example...

例如...

-- Condition in JOIN
SELECT *
FROM dbo.Customers AS CUS
INNER JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
AND CUS.FirstName = 'John'

-- Condition in WHERE
SELECT *
FROM dbo.Customers AS CUS
INNER JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
WHERE CUS.FirstName = 'John'

Which do you prefer (and perhaps why)?

你更喜欢哪个（也许是为什么）？

采纳答案by Cade Roux

The relational algebra allows interchangeability of the predicates in the WHEREclause and the INNER JOIN, so even INNER JOINqueries with WHEREclauses can have the predicates rearrranged by the optimizer so that they may already be excludedduring the JOINprocess.

关系代数允许WHERE子句和 the中的谓词可互换INNER JOIN，因此即使INNER JOIN带有WHERE子句的查询也可以让优化器重新排列谓词，以便在此过程中它们可能已经被排除在外JOIN。

I recommend you write the queries in the most readable way possible.

我建议您以最易读的方式编写查询。

Sometimes this includes making the INNER JOINrelatively "incomplete" and putting some of the criteria in the WHEREsimply to make the lists of filtering criteria more easily maintainable.

有时，这包括使INNER JOIN相对“不完整”和将一些标准放入WHERE简单的过滤标准列表中，以便更容易维护。

For example, instead of:

例如，而不是：

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
    AND c.State = 'NY'
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
    AND a.Status = 1

Write:

写：

SELECT *
FROM Customers c
INNER JOIN CustomerAccounts ca
    ON ca.CustomerID = c.CustomerID
INNER JOIN Accounts a
    ON ca.AccountID = a.AccountID
WHERE c.State = 'NY'
    AND a.Status = 1

But it depends, of course.

但这当然取决于。

回答by HLGEM

For inner joins I have not really noticed a difference (but as with all performance tuning, you need to check against your database under your conditions).

对于内部连接，我并没有真正注意到差异（但与所有性能调优一样，您需要在您的条件下检查您的数据库）。

However where you put the condition makes a huge difference if you are using left or right joins. For instance consider these two queries:

但是，如果您使用左连接或右连接，放置条件的位置会有很大的不同。例如考虑这两个查询：

SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
WHERE ORD.OrderDate >'20090515'

SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
AND ORD.OrderDate >'20090515'

The first will give you only those records that have an order dated later than May 15, 2009 thus converting the left join to an inner join. The second will give those records plus any customers with no orders. The results set is very different depending on where you put the condition. (Select * if for example purposes only, you should not use of course in production code.) The exception to this is when you want to see only the records in one table but not the other. Then you use the where clause for the condition not the join.

第一个将仅提供订单日期晚于 2009 年 5 月 15 日的那些记录，从而将左联接转换为内联接。第二个将提供这些记录以及没有订单的任何客户。结果集非常不同，具体取决于您放置条件的位置。（选择 * 如果仅用于示例目的，您当然不应在生产代码中使用。）例外情况是您只想查看一个表中的记录而不是另一个表中的记录。然后您将 where 子句用于条件而不是连接。

SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
WHERE ORD.OrderID is null

回答by Bill Karwin

Most RDBMS products will optimize both queries identically. In "SQL Performance Tuning" by Peter Gulutzan and Trudy Pelzer, they tested multiple brands of RDBMS and found no performance difference.

大多数 RDBMS 产品会以相同的方式优化这两个查询。在 Peter Gulutzan 和 Trudy Pelzer 的“SQL Performance Tuning”中，他们测试了多个品牌的 RDBMS，没有发现性能差异。

I prefer to keep join conditions separate from query restriction conditions.

我更喜欢将连接条件与查询限制条件分开。

If you're using OUTER JOINsometimes it's necessary to put conditions in the join clause.

如果您OUTER JOIN有时使用，则有必要在 join 子句中添加条件。

回答by TheTXI

WHERE will filter after the JOIN has occurred.

WHERE 将在 JOIN 发生后进行过滤。

Filter on the JOIN to prevent rows from being added during the JOIN process.

过滤 JOIN 以防止在 JOIN 过程中添加行。

回答by Johnno Nolan

I prefer the JOIN to join full tables/Views and then use the WHERE To introduce the predicate of the resulting set.

我更喜欢 JOIN 连接完整的表/视图，然后使用 WHERE 来引入结果集的谓词。

It feels syntactically cleaner.

感觉在语法上更干净。

回答by marr75

I typically see performance increases when filtering on the join. Especially if you can join on indexed columns for both tables. You should be able to cut down on logical reads with most queries doing this too, which is, in a high volume environment, a much better performance indicator than execution time.

在对连接进行过滤时，我通常会看到性能提高。特别是如果您可以连接两个表的索引列。您应该能够通过执行此操作的大多数查询减少逻辑读取，这是在高容量环境中比执行时间更好的性能指标。

I'm always mildly amused when someone shows their SQL benchmarking and they've executed both versions of a sproc 50,000 times at midnight on the dev server and compare the average times.

当有人展示他们的 SQL 基准测试并且他们在开发服务器上的午夜执行两个版本的 sproc 50,000 次并比较平均时间时，我总是有点好笑。

回答by Eric

Joins are quicker in my opinion when you have a larger table. It really isn't that much of a difference though especially if you are dealing with a rather smaller table. When I first learned about joins, i was told that conditions in joins are just like where clause conditions and that i could use them interchangeably if the where clause was specific about which table to do the condition on.

在我看来，当你有一个更大的表时，联接会更快。但这确实没有太大区别，尤其是如果您正在处理一个相当小的桌子。当我第一次了解连接时，我被告知连接中的条件就像 where 子句条件一样，如果 where 子句特定于要在哪个表上执行条件，我可以互换使用它们。

回答by Jacob B

Putting the condition in the join seems "semantically wrong" to me, as that's not what JOINs are "for". But that's very qualitative.

在我看来，将条件放入连接中似乎“在语义上是错误的”，因为这不是 JOIN 的“用途”。但这是非常定性的。

Additional problem: if you decide to switch from an inner join to, say, a right join, having the condition be inside the JOIN could lead to unexpected results.

附加问题：如果您决定从内部联接切换到例如右联接，则条件位于 JOIN 内可能会导致意外结果。

回答by Jeeno Shibu

It is better to add the condition in the Join. Performance is more important than readability. For large datasets, it matters.

最好在Join中加入条件。性能比可读性更重要。对于大型数据集，这很重要。