Why Does an OR Condition in an INNER JOIN Cause a Performance Penalty?

Database optimization: performance impact of OR conditions in INNER JOIN

In database query optimization, it is crucial to understand the impact of different conditions in the JOIN statement. One of the common issues is using OR conditions in INNER JOIN.

Consider the following query:

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

This query will take several minutes to execute initially. After closer inspection, the problem was determined to be the OR condition in the INNER JOIN.

To solve this problem, the query was rewritten to use a pair of LEFT JOINs:

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

The optimized query now completes execution in approximately one second.

Using OR conditions in INNER JOIN will seriously affect query performance for the following reasons:

• It prevents the optimizer from using more efficient join algorithms such as HASH JOIN or MERGE JOIN.
• It forces the optimizer to generate a Cartesian product, which is computationally expensive for large tables.

Therefore, it is better to express such conditions using concatenated result sets. In the example above, this would translate to:

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

By using equijoins and concatenating the results, the query optimizer can perform operations efficiently without incurring a performance penalty. Note the use of UNION ALL here, use UNION if you need to remove duplicate rows.

The above is the detailed content of Why Does an OR Condition in an INNER JOIN Cause a Performance Penalty?. For more information, please follow other related articles on the PHP Chinese website!