Why Are 'OR' Conditions in INNER JOINs So Slow?

Analysis of performance issues caused by using "OR" under INNER JOIN conditions in SQL Server 2008

A slow query against two small tables of about 50,000 rows, and careful inspection by a SQL Server 2008 database expert revealed the culprit: the "OR" operator in the INNER JOIN condition.

The problematic code snippet is as follows:

<code class="language-sql">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</code>

After replacing "OR" with a pair of LEFT JOIN, the query speed is significantly improved and can be completed in just one second.

Is "OR" in JOIN condition bad practice?

Experts wonder if using "OR" in a JOIN condition is always undesirable, or if their case is just an exception due to the specific table structure.

Technical explanation

The problem with using "OR" in a JOIN condition is that it cannot be optimized into a HASH JOIN or MERGE JOIN operation. The query optimizer can only represent this as a merge of different result sets:

<code class="language-sql">SELECT  *
FROM    maintable m
JOIN    othertable o
ON      o.parentId = m.id
UNION ALL
SELECT  *
FROM    maintable m
JOIN    othertable o
ON      o.id = m.parentId</code>

Although each individual result set is an equijoin, the optimizer does not recognize them as equijoins when "OR" is present. This results in inefficient queries.

The above is the detailed content of Why Are 'OR' Conditions in INNER JOINs So Slow?. For more information, please follow other related articles on the PHP Chinese website!