IN vs Multiple OR statements 1

[xlbo]

Hi all - not a SQL expert by any stretch of the imagination although I have moved on from GUI generated SQL to writing my own. I don't know a huge amount about query optimising but myself and a colleague were just discussing whether it was better / faster to use IN or a set of multiple OR statements when limiting data based on a set of values e.g.

SELECT blah FROM Table WHERE SomeCode IN ('a','b','c')

or

SELECT blah FROM Table WHERE (SomeCode = 'a' OR SomeCode = 'b' OR SomeCode = 'c')

Any thoughts? Apologies if this is either pointless or obvious - I did do a search of the forum but OR tends to bring up a lot of results

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[HarleyQuinn]

Geoff, as far as my limited SQL Server experience goes I'm pretty sure that there's little difference between the two (as both would cause indexes to be ignored and would cycle through the table searching for the rows, I stand open for correction on that one though).

If your values are contiguous (as in your example) I'd use BETWEEN as I'm pretty sure that would make use of any indexes on the field.

Hope this helps (and hasn't sent you off in completely the wrong direction )

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[xlbo]

Cheers Harley - it's not a critical question at the mo - just trying to learn a bit more about SQL. I can write it fine now pretty much so I'm trying to move on from just writing it to understanding the best way to do certain things that tend to crop up a lot.

Thanks for the heads up on the BETWEEEN syntax - the particular query that was being worked on just had a list of text strings to filter for

As an additional piece to this, do you (or anyone else) know whether it would be more or less efficient to use something like:

SELECT theCode, blah, blah, blah
FROM Table t1 inner join
(SELECT 'a','b,'c' as theCode) t2 ON t1.theCode = t2.theCode

rather than using IN or multiple ORs...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bborissov]

That code will give you ONLY these values with C, because derived table will be created like this:

No name       No name       TheCode
--------------------------------------
   a             b             c

In case you want to use that technique you must use:

SELECT 'a' as theCode
UNION
SELECT 'b' as theCode
UNION
SELECT 'c' as theCode

BTW SQL Optimizer will translate IN () clause to bunch of ORs

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[xlbo]

aaaah - thank you twice bborissov

That answers my question on whether In or ORs is better!

Would you know whether that 2nd technique would be more efficient than the multiple ORs ? I did do some reading that suggested that joins were more efficient than conditions for filtering out data...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ESquared]

Since IN() gets expanded to OR by the query optimizer, there's no difference whatsoever.

If your list of items is short, an OR clause usually performs better.

If your list of items is long, a JOIN usually performs better.

If your list is somewhere in the middle... I tend to lean toward JOINs.

Just for reference here's the JOIN:

SELECT theCode, blah, blah, blah
FROM Table t1 inner join
(SELECT theCode = 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c' as theCode) t2 ON t1.theCode = t2.theCode

 

[xlbo]

Thanks ESquared - I did get most of that from bborisov's post but thanks for the update on whether to use multiple ORs or JOINs

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question