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?
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
UNION ALL will give you duplicates compared to the JOIN with an OR condition.
union all which is not correct as the article you link to also describes.
Follow WeChat
Success story sharing
Want to stay one step ahead of the latest teleworks?
Subscribe Now相似问题
- What is the difference between "INNER JOIN" and "OUTER JOIN"?
- LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
- Difference between JOIN and INNER JOIN
- INNER JOIN ON vs WHERE clause
- When should I use CROSS APPLY over INNER JOIN?
- How can I do an UPDATE statement with JOIN in SQL Server?
- INNER JOIN vs LEFT JOIN performance in SQL Server
- How can I delete using INNER JOIN with SQL Server?
ON w=x OR y=zpattern entirely?ON w=x OR y=z? (Thanks for your patience!)SQL Serverunderstand that a concatenation would be needed. Say, the querySELECT * FROM othertable WHERE parentId = 1 OR id = 2will use a concatenation if both fields are indexed so theoretically there is nothing that would prevent doing the same thing in a loop. WhetherSQL Serverwill build this plan actually or not, depends on very many factors, but I've never seen it built in real life.