对连接进行过滤？
与 WHERE 子句相反，是否有任何关于性能方面的参数可以在连接中进行过滤？
例如，
SELECT blah FROM TableA a
INNER JOIN TableB b
ON b.id = a.id
AND b.deleted = 0
WHERE a.field = 5
与
SELECT blah FROM TableA a
INNER JOIN TableB b
ON b.id = a.id
WHERE a.field = 5
AND b.deleted = 0
我个人更喜欢后者相反，因为我觉得过滤应该在过滤部分（WHERE）中完成，但是执行这两种方法是否有任何性能或其他原因？
Is there any argument, performance wise, to do filtering in the join, as opposed to the WHERE clause?
For example,
SELECT blah FROM TableA a
INNER JOIN TableB b
ON b.id = a.id
AND b.deleted = 0
WHERE a.field = 5
As opposed to
SELECT blah FROM TableA a
INNER JOIN TableB b
ON b.id = a.id
WHERE a.field = 5
AND b.deleted = 0
I personally prefer the latter, because I feel filtering should be done in the filtering section (WHERE), but is there any performance or other reasons to do either method?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果查询优化器完成其工作，则两种形式的内连接根本没有任何区别（除了其他方面的清晰度之外）。
也就是说，对于左连接，连接中的条件意味着在连接之前过滤掉第二个表中的行。 where 中的条件表示从连接后的最终结果中过滤掉行。这些意味着非常不同的事情。
If the query optimizer does its job, there is no difference at all (except clarity for others) in the two forms for inner joins.
That said, with left joins a condition in the join means to filter rows out of the second table before joining. A condition in the where means to filter rows out of the final result after joining. Those mean very different things.
使用内部联接，您将获得相同的结果，并且可能具有相同的性能。但是，使用外连接，两个查询将返回不同的结果，并且根本不等效，因为将条件放在 where 子句中实质上会将查询从左连接更改为内连接（除非您正在查找某些记录字段为空）。
With inner joins you will have the same results and probably the same performance. However, with outer joins the two queries would return different results and are not equivalent at all as putting the condition in the where clause will in essence change the query from a left join to an inner join (unless you are looking for the records where some field is null).
不，这两者之间没有区别，因为在查询的逻辑处理中，
WHERE将始终紧接在过滤子句（ON）之后，在您的示例中，您将拥有：您的示例采用 ANSI SQL-92 标准，您也可以使用 ANSI SQL-89 标准编写查询，如下所示：
对于内部连接来说这是正确的，带有外部连接是相似的，但不一样
No there is no differences between these two, because in the logical processing of the query,
WHEREwill always go right after filter clause(ON), in your examples you will have:Your examples are in ANSI SQL-92 standard, you could also write the query with ANSI SQL-89 standard like this:
THIS IS TRUE FOR INNER JOINS, WITH OUTER JOINS IS SIMILAR BUT NOT THE SAME