blocks|key|1525284|text|WHERE将在连接发生后进行过滤。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1525285|对联接进行筛选，以防止在联接过程中添加行。|1525286|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

WHERE will filter after the JOIN has occurred.

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

blocks|key|1310322|text|我更喜欢使用连接来连接整个表/视图，然后使用WHERE来引入结果集的谓词。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1310323|它在语法上感觉更干净。|1310324|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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

It feels syntactically cleaner.

blocks|key|1310355|text|在我看来，将条件放在连接中“在语义上是错误的”，因为这不是连接的“目的”。但这是非常定性的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1310356|其他问题:如果您决定从内部连接切换为右连接，那么将条件放在连接中可能会导致意外的结果。|1310357|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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

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.

blocks|key|1310418|text|大多数RDBMS产品将以相同的方式优化这两个查询。在Peter+Gulutzan和Trudy+Pelzer的"SQL+Performance+Tuning“中，他们测试了多个品牌的RDBMS，没有发现性能差异。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1310419|我更喜欢将连接条件与查询限制条件分开。|1310420|如果您使用的是OUTER+JOIN，有时有必要在join子句中加入条件。|offset|length|style|CODE|1310421|entityMap^0|0|0|7|A|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@$F|O|G|P|H|I]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

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.

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

If you're using <code>OUTER JOIN</code> sometimes it's necessary to put conditions in the join clause.

blocks|key|1310461|text|我通常会在对连接进行过滤时看到性能的提高。特别是当您可以在两个表的索引列上联接时。在大多数查询也这样做的情况下，您应该能够减少逻辑读取，在大容量环境中，这是一个比执行时间更好的性能指标。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1310462|当有人展示他们的SQL基准测试，并且他们在午夜在开发服务器上执行两个版本的存储过程50,000次，并比较平均时间时，我总是有点好笑。|1310463|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

blocks|key|1310506|text|在我看来，当你有一个更大的表时，连接会更快。不过，这真的没有太大的区别，特别是如果您处理的是一个相当小的表。当我第一次学习连接时，有人告诉我连接中的条件就像where子句中的条件，如果where子句指定在哪个表上执行条件，我可以互换使用它们。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1310507|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1525601|text|对于内部连接，我并没有真正注意到有什么不同(但是对于所有的性能调优，您需要根据您的条件检查您的数据库)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1525602|但是，如果您使用的是左连接还是右连接，那么将条件放在哪里会产生巨大的差异。例如，考虑以下两个查询：|1525603|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'|code-block|syntax|javascript|1525604|第一个将只给出那些订单日期晚于2009年5月15日的记录，从而将左连接转换为内连接。|1525605|第二个将给出这些记录加上任何没有订单的客户。根据您放置条件的位置，结果集会有很大的不同。(Select+*仅用于示例目的，当然您不应该在生产代码中使用它。)|1525606|例外情况是，您只想查看一个表中的记录，而不想查看另一个表中的记录。然后将where子句用于条件，而不是连接。|1525607|SELECT+*
FROM+dbo.Customers+AS+CUS+
LEFT+JOIN+dbo.Orders+AS+ORD+
ON+CUS.CustomerID+=+ORD.CustomerID
WHERE+ORD.OrderID+is+null|1525608|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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:
<pre><code>SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
WHERE ORD.OrderDate &gt;'20090515'

SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
AND ORD.OrderDate &gt;'20090515'
</code></pre>
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 * is for example purposes only, of course you should not use this 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.
<pre><code>SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
WHERE ORD.OrderID is null
</code></pre>

blocks|key|1310570|text|最好在Join中添加条件。性能比可读性更重要。对于大型数据集，这很重要。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1310571|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|1310609|text|同意第二多人投票的答案，当使用LEFT+JOIN或RIGHT+JOIN时会有很大的不同。实际上，下面的两个语句是等价的。因此您可以看到，AND子句在JOIN之前进行过滤，而WHERE子句在JOIN之后进行过滤。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1310610|SELECT+*
FROM+dbo.Customers+AS+CUS+
LEFT+JOIN+dbo.Orders+AS+ORD+
ON+CUS.CustomerID+=+ORD.CustomerID
AND+ORD.OrderDate+>'20090515'

SELECT+*
FROM+dbo.Customers+AS+CUS+
LEFT+JOIN+(SELECT+*+FROM+dbo.Orders+WHERE+OrderDate+>'20090515')+AS+ORD+
ON+CUS.CustomerID+=+ORD.CustomerID|code-block|syntax|javascript|1310611|entityMap^0|F|9|P|A|1W|3|22|4|2E|5|2M|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|L|$]]

Agree with 2nd most vote answer that it will make big difference when using <code>LEFT JOIN</code> or <code>RIGHT JOIN</code>. Actually, the two statements below are equivalent. So you can see that <code>AND</code> clause is doing a filter before <code>JOIN</code> while the <code>WHERE</code> clause is doing a filter after <code>JOIN</code>.
<pre><code>SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN dbo.Orders AS ORD 
ON CUS.CustomerID = ORD.CustomerID
AND ORD.OrderDate &gt;'20090515'

SELECT *
FROM dbo.Customers AS CUS 
LEFT JOIN (SELECT * FROM dbo.Orders WHERE OrderDate &gt;'20090515') AS ORD 
ON CUS.CustomerID = ORD.CustomerID
</code></pre>

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

For example...

<pre><code>-- 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'
</code></pre>

Which do you prefer (and perhaps why)?

Condition within JOIN or WHERE

是否有任何差异(性能、最佳实践等)在JOIN子句和WHERE子句中添加条件？例如..。-- Condition in JOINSELECT *FROM dbo.Customers AS CUSINNER JOIN dbo.Orders AS ORD ON CUS.CustomerID = ORD.CustomerIDA...

问JOIN或WHERE中的条件
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问JOIN或WHERE中的条件EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问JOIN或WHERE中的条件
EN