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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to inner-join