在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.

它可以表示为两个结果集的串联:

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).

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