OR vs. UNION

Question

OR vs. UNION

PiMané

SSChampion

Points: 13965
More actions
October 8, 2012 at 9:19 am

#282712

Hi,
I'm made some tests and read about this on some blogs but I'm not sure my conclusions are correct...
http://www.sql-server-performance.com/2011/union-or-sql-server-queries/
http://sqlinthewild.co.za/index.php/2011/07/05/converting-or-to-union/
What I can resume from tests I made and from the posts is that OR should be used whenever the restriction is on the same column and use UNION for each different column.
-- original
SELECT ProductId, Description, Price FROM Products WHERE Price < 100 AND (ProductId = 1 OR ProductId = 10 OR ProductCode LIKE 'RB%')
-- converted to
SELECT ProductId, Description, Price FROM Products WHERE Price < 100 AND (ProductId = 1 OR ProductId = 10)
UNION
SELECT ProductId, Description, Price FROM Products WHERE Price < 100 AND ProductCode LIKE 'RB%'
UNION ALL is faster but if Product 1 or 10 has a Code that begins with RB I would get duplicate rows on the 2nd query with UNION ALL
Are my assumptions correct?
Thanks,
Pedro

If you need to work better, try working less...

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004490 More actions · Answer 1

If you got that conclusion from my blog post I clearly need to rewrite it.

Write your queries logically, if you mean OR, use OR. If that performs unacceptably, then consider testing whether UNION is faster (on 2005+ it's not necessarily going to be), being careful of the logical difference between the two (which is touched on in my blog)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PiMané SSChampion Points: 13965 More actions · Answer 2

GilaMonster (10/8/2012)
If you got that conclusion from my blog post I clearly need to rewrite it.
Write your queries logically, if you mean OR, use OR. If that performs unacceptably, then consider testing whether UNION is faster (on 2005+ it's not necessarily going to be), being careful of the logical difference between the two (which is touched on in my blog)

The conclusion was from the 1st link, not from your blog. Sorry bout the confusion...

I just put your link there because of the UNION problem without specifying the Id column on the results which can give the wrong number of rows.

So OR is "always" the solution until proven otherwise...

It's hard to write queries that can be ran on tables that can have 1.000.000 rows or 1.000 rows depending on the customers' use...

Almost makes you want to have a "configuration" table with values and according to those values the SP executes one way or the other... 🙂

But since the customers that complaint are the ones with large databases we can start writing the code assuming all the tables have lots of data ...

Thanks,

Pedro

If you need to work better, try working less...

Gail Shaw SSC Guru Points: 1004490 More actions · Answer 3

In general... write queries that do what you want and write them in the logical way to achieve it. If performance proves to be unacceptable, then start considering alternatives. Don't write complex queries because they might be faster without testing that they are faster and that the simpler form is unacceptably slow.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PiMané SSChampion Points: 13965 More actions · Answer 4

Thanks...

I'm still writing the best practices rules for developers to follow and some points are very complex for them to analyze, so I'll have to do that myself.

Pedro

If you need to work better, try working less...