Conditional Joins…I don’t see much about this topic in cyberspace, but I occasionally run into performance problems with them. So this posting is all about them…or more specifically about why you should avoid them.
A conditional join is a join (either inner or outer, by the way) that contains an “either-or” condition, easily identified because the word “OR” is in the ON clause.
I don’t see conditional joins very often, but when I do it’s usually because someone has a performance issue with the query. I was given one yesterday that I had to tune, and I found the culprit: an OR in a join condition like this:
SELECT mycolumnlist FROM MyFirstTable a LEFT OUTER JOIN MySecondTable b ON ( a.MyAID = b.MyAID OR a.MyBID = b.MyBID ) WHERE more conditions… ;
(By the way, I tried to use AdventureWorks to create an example, but since there aren’t any indexes my query plans did not change, providing less than stellar examples )
Think about it…what you are asking the database to do is to retrieve records based on one condition OR another condition…so essentially telling the database to grab all (or most) of the records and then filter out what is actually needed (I usually see a table scan or other index scan combined with some kind of join operation in the execution plan). Usually the performance issue isn’t noticeable until one or more of the tables grows to a larger size (in our case after 1 million rows we started seeing a slow-down).
Bottom line, AVOID the OR in any join clause. Here are a few articles that provide some examples and more technical details…