Multiple "OR" parameters in sql SELECT statement

Is there a better way to write the following statement?

SELECT id, firstname, surname, age
FROM sometable
WHERE ( (id = 5058)
OR (id = 1389)
OR (id = 15075)
OR (id = 1099)
OR (id = 1299)
OR (id = 1961)
OR (id = 14733)
OR (id = 1245)
OR (id = 5208)
OR (id = 1704)
OR (id = 2596)
OR (id = 1616)
OR (id = 4532)
OR (id = 1810)
OR (id = 2758)
OR (id = 1212)
)
)

Re: Multiple "OR" parameters in sql SELECT statement

SELECT id, firstname, surname, age
FROM sometable
WHERE id IN (5058, 1389, 15075)

Bob Lehmann

"Luis" wrote in message
news:04ad01c518de$b3ec47a0$a601280a@phx.gbl...
> Is there a better way to write the following statement?
>
> SELECT id, firstname, surname, age
> FROM sometable
> WHERE ( (id = 5058)
> OR (id = 1389)
> OR (id = 15075)
> OR (id = 1099)
> OR (id = 1299)
> OR (id = 1961)
> OR (id = 14733)
> OR (id = 1245)
> OR (id = 5208)
> OR (id = 1704)
> OR (id = 2596)
> OR (id = 1616)
> OR (id = 4532)
> OR (id = 1810)
> OR (id = 2758)
> OR (id = 1212)
> )
> )
>

Re: Multiple "OR" parameters in sql SELECT statement

>SELECT id, firstname, surname, age
>FROM sometable
>WHERE id IN (5058, 1389, 15075)
>
>Bob Lehmann
>

PERFECT! And it runs much faster too!

Re: Multiple "OR" parameters in sql SELECT statement

"Luis" wrote in message
news:04d601c518e3$06999b20$a601280a@phx.gbl...
> >SELECT id, firstname, surname, age
> >FROM sometable
> >WHERE id IN (5058, 1389, 15075)
> >
> >Bob Lehmann
> >
>
> PERFECT! And it runs much faster too!

There shouldn't be any difference in performance.
The execution plans generated using IN or a series of ORs are identical.
Unless you got lazy like Bob (just kidding Bob) and just included 3 IDs in
your statement.

Re: Multiple "OR" parameters in sql SELECT statement

raydan wrote:
> "Luis" wrote in message
> news:04d601c518e3$06999b20$a601280a@phx.gbl...
>>> SELECT id, firstname, surname, age
>>> FROM sometable
>>> WHERE id IN (5058, 1389, 15075)
>>>
>>> Bob Lehmann
>>>
>>
>> PERFECT! And it runs much faster too!
>
> There shouldn't be any difference in performance.
> The execution plans generated using IN or a series of ORs are
> identical.

This is certainly true for SQL Server, but may not be true for other
rdbms's. FWIW, I do not know of an rdbms in which this statement is not
true. Maybe the OP is using one.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Multiple "OR" parameters in sql SELECT statement

This is what happens when the only 2 groups you look at are this one and
sqlserver.programming
Sometimes I don't know where I am.

"Bob Barrows [MVP]" wrote in message
news:%239xDdiPGFHA.1292@TK2MSFTNGP10.phx.gbl...
> raydan wrote:
> > "Luis" wrote in message
> > news:04d601c518e3$06999b20$a601280a@phx.gbl...
> >>> SELECT id, firstname, surname, age
> >>> FROM sometable
> >>> WHERE id IN (5058, 1389, 15075)
> >>>
> >>> Bob Lehmann
> >>>
> >>
> >> PERFECT! And it runs much faster too!
> >
> > There shouldn't be any difference in performance.
> > The execution plans generated using IN or a series of ORs are
> > identical.
>
> This is certainly true for SQL Server, but may not be true for other
> rdbms's. FWIW, I do not know of an rdbms in which this statement is not
> true. Maybe the OP is using one.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Multiple "OR" parameters in sql SELECT statement

On Tue, 22 Feb 2005 09:33:38 -0500, "raydan"
wrote:
>
>There shouldn't be any difference in performance.

Running Oracle 9.2i and I noticed a BIG improvement after making the
change. (Maybe it was something else???)

Besides, "WHERE id IN (5058, 1389, 15075...)" is easier to read than
"WHERE ( (id = 5058) OR (id = 1389) OR (id = 15075)..."

>The execution plans generated using IN or a series of ORs are identical.

I'll check the plans against the Oracle db, just to "be safe".

Re: Multiple "OR" parameters in sql SELECT statement

Sorry Luis, I didn't mean to say that there was anything negative about
using IN.
Quite the contrary, I find it a lot easier to type, to read and to
understand.
It's just that I'd be surprised if the plans using IN or ORs are different.
Tell us what you find.

"Luis" wrote in message
news:812n11lrctsl0o0iciquah88oq4aqdaeqo@4ax.com...
> On Tue, 22 Feb 2005 09:33:38 -0500, "raydan"
> wrote:
> >
> >There shouldn't be any difference in performance.
>
> Running Oracle 9.2i and I noticed a BIG improvement after making the
> change. (Maybe it was something else???)
>
> Besides, "WHERE id IN (5058, 1389, 15075...)" is easier to read than
> "WHERE ( (id = 5058) OR (id = 1389) OR (id = 15075)..."
>
> >The execution plans generated using IN or a series of ORs are identical.
>
> I'll check the plans against the Oracle db, just to "be safe".
>

Re: Multiple "OR" parameters in sql SELECT statement

On Tue, 22 Feb 2005 15:32:42 -0500, "raydan"
wrote:

>Sorry Luis, I didn't mean to say that there was anything negative about
>using IN.

No problem...

>Quite the contrary, I find it a lot easier to type, to read and to
>understand.

I agree!

>It's just that I'd be surprised if the plans using IN or ORs are different.
>Tell us what you find.

Ok, I confess, the big improvement was due to the way I was getting my
data from the database - I originally had a horrible bunch of nested
select statements that were painfully slow. When I rewrote the query
so all the data was retrieved with just 2 simpler queries is when I
got the improved performance. But, using the IN certainly made the
code shorter and easier to read.

Re: Multiple "OR" parameters in sql SELECT statement

Luis wrote:
> On Tue, 22 Feb 2005 15:32:42 -0500, "raydan"
> wrote:
>
>> Sorry Luis, I didn't mean to say that there was anything negative
>> about using IN.
>
> No problem...
>
>> Quite the contrary, I find it a lot easier to type, to read and to
>> understand.
>
> I agree!
>
>> It's just that I'd be surprised if the plans using IN or ORs are
>> different. Tell us what you find.
>
> Ok, I confess, the big improvement was due to the way I was getting my
> data from the database - I originally had a horrible bunch of nested
> select statements that were painfully slow. When I rewrote the query
> so all the data was retrieved with just 2 simpler queries is when I
> got the improved performance. But, using the IN certainly made the
> code shorter and easier to read.

If you are passing a lot of values into that IN construct, you may see a
performance boost from inserting them into a temp table and using an inner
join to that temp table, as opposed to using IN, which forces a scan to be
used.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Multiple "OR" parameters in sql SELECT statement

"Bob Barrows [MVP]" wrote in message
news:%23hvIlbdGFHA.4032@TK2MSFTNGP12.phx.gbl...
> Luis wrote:
> > On Tue, 22 Feb 2005 15:32:42 -0500, "raydan"
> > wrote:
> >
> >> Sorry Luis, I didn't mean to say that there was anything negative
> >> about using IN.
> >
> > No problem...
> >
> >> Quite the contrary, I find it a lot easier to type, to read and to
> >> understand.
> >
> > I agree!
> >
> >> It's just that I'd be surprised if the plans using IN or ORs are
> >> different. Tell us what you find.
> >
> > Ok, I confess, the big improvement was due to the way I was getting my
> > data from the database - I originally had a horrible bunch of nested
> > select statements that were painfully slow. When I rewrote the query
> > so all the data was retrieved with just 2 simpler queries is when I
> > got the improved performance. But, using the IN certainly made the
> > code shorter and easier to read.
>
> If you are passing a lot of values into that IN construct, you may see a
> performance boost from inserting them into a temp table and using an
inner
> join to that temp table, as opposed to using IN, which forces a scan to be
> used.
>

As an interesting side note, SQL Server 6.5 did this automatically. Here's a
thread:
http://groups-beta.google.com/group/microsoft.public.sqlserv er.programming/browse_frm/thread/712dd4f655ef9b6e/3563e4708a 6dff31

There's no mention of this in SQL7.0+, so either it was removed from the
engine or simply classified as a low level operation and omitted from the
documentation.

Re: Multiple "OR" parameters in sql SELECT statement

Chris Hohmann wrote:
>>
>> If you are passing a lot of values into that IN construct, you may
>> see a
>> performance boost from inserting them into a temp table and using an
> inner
>> join to that temp table, as opposed to using IN, which forces a scan
>> to be
>> used.
>>
>
> As an interesting side note, SQL Server 6.5 did this automatically.
> Here's a
> thread:
> http://groups-beta.google.com/group/microsoft.public.sqlserv er.programming/browse_frm/thread/712dd4f655ef9b6e/3563e4708a 6dff31
>
> There's no mention of this in SQL7.0+, so either it was removed from
> the
> engine or simply classified as a low level operation and omitted from
> the
> documentation.

Wow! That IS interesting. I'm going to do a little digging to see if I can
come up with some more info on this.

Bob

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"