SQL 在 INNER JOIN 条件中使用“OR”是一个坏主意吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5901791/
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
Is having an 'OR' in an INNER JOIN condition a bad idea?
提问by ladenedge
In trying to improve the speed of an immensely slow query (several minuteson two tables with only ~50,000 rows each, on SQL Server 2008 if it matters), I narrowed down the problem to an ORin my inner join, as in:
为了提高非常慢的查询的速度（在两个表上每个只有大约 50,000 行的几分钟，如果重要的话，在 SQL Server 2008 上），我将问题缩小到OR我的内部联接中，如下所示：
SELECT mt.ID, mt.ParentID, ot.MasterID
FROM dbo.MainTable AS mt
INNER JOIN dbo.OtherTable AS ot ON ot.ParentID = mt.ID
OR ot.ID = mt.ParentID
I changed this to (what I hope is) an equivalent pair of left joins, shown here:
我将其更改为（我希望是）一对等效的左连接，如下所示：
SELECT mt.ID, mt.ParentID,
CASE WHEN ot1.MasterID IS NOT NULL THEN
ot1.MasterID ELSE
ot2.MasterID END AS MasterID
FROM dbo.MainTable AS mt
LEFT JOIN dbo.OtherTable AS ot1 ON ot1.ParentID = mt.ID
LEFT JOIN dbo.OtherTable AS ot2 ON ot2.ID = mt.ParentID
WHERE ot1.MasterID IS NOT NULL OR ot2.MasterID IS NOT NULL
.. and the query now runs in about a second!
.. 查询现在运行大约一秒钟！
Is it generally a bad idea to put an ORin a join condition? Or am I just unlucky somehow in the layout of my tables?
将 anOR置于连接条件中通常是个坏主意吗？或者我只是在我的桌子布局方面不走运？
回答by Quassnoi
This kind of JOINis not optimizable to a HASH JOINor a MERGE JOIN.
这种类型JOIN不可优化为 aHASH JOIN或 a MERGE JOIN。
It can be expressed as a concatenation of two resultsets:
它可以表示为两个结果集的串联：
SELECT *
FROM maintable m
JOIN othertable o
ON o.parentId = m.id
UNION
SELECT *
FROM maintable m
JOIN othertable o
ON o.id = m.parentId
, each of them being an equijoin, however, SQL Server's optimizer is not smart enough to see it in the query you wrote (though they are logically equivalent).
，它们中的每一个都是 equijoin，但是，SQL Server的优化器不够聪明，无法在您编写的查询中看到它（尽管它们在逻辑上是等效的）。
回答by MEO
I use following code for get different result from condition That worked for me.
我使用以下代码从对我有用的条件中获得不同的结果。
Select A.column, B.column
FROM TABLE1 A
INNER JOIN
TABLE2 B
ON A.Id = (case when (your condition) then b.Id else (something) END)
回答by Mitul Panchal
You can use UNION ALL instead.
您可以改用 UNION ALL。
SELECT mt.ID, mt.ParentID, ot.MasterID
FROM dbo.MainTable AS mt
Union ALL
SELECT mt.ID, mt.ParentID, ot.MasterID
FROM dbo.OtherTable AS ot
SELECT mt.ID, mt.ParentID, ot.MasterID
FROM dbo.MainTable AS mt
Union ALL
SELECT mt.ID, mt.ParentID, ot.MasterID
FROM dbo.OtherTable AS ot
