Home > SQL Challenge > T-SQL: Using OR in JOIN ON clause VS. UNION

T-SQL: Using OR in JOIN ON clause VS. UNION

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.

Another way is to combine SerialNo and DiscSerialNo into a temp table (or sub query) (more on this later).

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: