在 INNER JOIN 条件中使用“OR"是一个坏主意吗?

2021-12-02 00:00:00 sql tsql sql-server-2008 sql-server 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

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

相关文章