Is having an 'OR' in an INNER JOIN condition a bad idea?

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!

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?

161005 次浏览
小开
最佳答案

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

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

小开

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)
小开

You can use UNION ALL instead

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

提交答案