T-SQL coding

Should I use an “OR” in a JOIN?

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…

https://www.red-gate.com/simple-talk/sql/performance/how-to-avoid-conditional-joins-in-t-sql/

https://stackoverflow.com/questions/5901791/is-having-an-or-in-an-inner-join-condition-a-bad-idea

https://stackoverflow.com/questions/19725014/sql-server-left-join-with-or-operator

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s