在 INNER JOIN 条件中使用“或"是一个坏主意吗?
在尝试提高极其缓慢的查询的速度(在两个只有约 50,000 行的表上，如果重要的话，在 SQL Server 2008 上需要几个 分钟)，我将问题缩小到OR 在我的内部连接中，如:
In trying to improve the speed of an immensely slow query (several minutes on two tables with only ~50,000 rows each, on SQL Server 2008 if it matters), I narrowed down the problem to an OR in my inner join, as in:
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!
将 OR 放在连接条件中通常是个坏主意吗?还是我只是在我的桌子布局上不走运?
Is it generally a bad idea to put an OR in a join condition? Or am I just unlucky somehow in the layout of my tables?
推荐答案
这种 JOIN 不适用于 HASH JOIN 或 MERGE JOIN.
This kind of JOIN is not optimizable to a HASH JOIN or 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
，它们中的每一个都是等值连接，但是，SQL Server 的优化器不够聪明，无法在您编写的查询中看到它(尽管它们在逻辑上是等价的).
, 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).
相关文章