在 INNER JOIN 条件中使用“OR"是一个坏主意吗? [英] Is having an 'OR' in an INNER JOIN condition a bad idea?

问题描述

为了提高非常慢的查询的速度(在两个表上每个只有约 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

，它们中的每一个都是一个 equijoin，但是，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).

这篇关于在 INNER JOIN 条件中使用“OR"是一个坏主意吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！