SQL 如何做 WHERE 子句 BEFORE INNER JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16632901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do WHERE clause BEFORE INNER JOIN
提问by Night Walker
How I do query like this ?
我如何进行这样的查询？
select Distinct Station , Slot , SubSlot, CompID , CompName
from DeviceTrace as DT DT.DeviceID = '1339759958'
inner join CompList as CL
where and DT.CompID = CL.CompID
I need to do DT.DeviceID = '1339759958'before I start with the inner join.
I work with sql server.
DT.DeviceID = '1339759958'在开始内部连接之前，我需要做。我使用 sql 服务器。
回答by David Aldridge
I find it difficult to believe that it makes any difference. The query optimiser should apply the predicate before the join if it calculates that it is more efficient to do so. The only circumstance where you might need to do this is when the optimiser makes an erroneous choice (for inner joins at least -- there are valid cases for outer joins).
我发现很难相信这有什么不同。如果查询优化器计算出这样做更有效，则它应该在连接之前应用谓词。您可能需要这样做的唯一情况是优化器做出错误选择时（至少对于内连接——外连接存在有效情况）。
回答by John Woo
try adding in ONclause.
尝试添加 inON子句。
SELECT DISTNCT Station, Slot, SubSlot, CompID, CompName
FROM DeviceTrace AS DT INNER JOIN CompList AS CL
ON DT.CompID = CL.CompID AND
DT.DeviceID = '1339759958'
In this case, the result is the same since you are doing INNER JOIN. Adding the condition in the ONclause can be very different when doing LEFT JOINand filtering on the right hand side table.
在这种情况下，结果是相同的，因为您正在执行INNER JOIN. 在右侧表上ON执行LEFT JOIN和过滤时，在子句中添加条件可能会有很大不同。
回答by Andomar
You can use a subquery to apply a whereclause before a join:
您可以使用子查询在 awhere之前应用子句join：
select *
from (
select *
from DeviceTrace
where DeviceID = '1339759958'
) as DT
inner join
CompList as CL
on DT.CompID = CL.CompID
Although in this case, it should not matter whether you filter in a subquery, the onclause, or even the final where.
尽管在这种情况下，您是否在子查询、on子句甚至最终where.
回答by Roman Podlinov
A small clarification to answer from David Aldridge. You must use query
大卫奥尔德里奇回答的一个小澄清。您必须使用查询
select Distinct Station , Slot , SubSlot, CompID , CompName
from DeviceTrace as DT
inner join CompList as CL on DT.CompID = CL.CompID
where DT.DeviceID = '1339759958'
