union vs or clause

I bet many of SQL developers have attempted or have written SQL code like this:

select    a.*
from    tblA a
join    tblB b
on        a.DiscoverySerialNo = b.SerialNo
or        a.DiscoverySerialNo = b.DiscSerialNo

You must have noticed that code like this takes longer than usual, or even worse, never returns result.

I haven’t analyze this so cannot tell exactly what the query plan looks like. There are a couple of other ways to achieve the same results without using OR in JOIN ON clause, and they are life savers in terms of improved performance.

One way is to use UNION:

select    a.*
from    tblA a
join    tblB b
on        a.DiscoverySerialNo = b.SerialNo
UNION
select    a.*
from    tblA a
join    tblB b
on        a.DiscoverySerialNo = b.DiscSerialNo

Make sure that UNION does not introduce duplicate matching records because of different values in the selected columns. In another word, using getdate() function in SELECT is not a good idea.

http://bisherryli.wordpress.com/2011/01/06/t-sql-using-or-in-join-on-clause-vs-union/

This entry was posted in SQL Server and tagged . Bookmark the permalink.