On Fri, 2002-11-08 at 09:40, Chuck Tomasi wrote: > Currently I have a Perl app that reads a config file and creates an SQL > "where" clause. For legacy reasons, the clause comes out like this: > > where group=1001 or group=1005 or group=1017 or group=1025 > > I was doing some reading the other day and noticed this could be done like > this: > > where group in (1001,1005,1017,1025) > > Obviously it is a shorter statement, but is it any better? >
Most databases optimize the second statement into the first. Just like col BETWEEN 1001 AND 1005 generally becomes col >= 1001 AND col <= 1005 It's just a short-hand that parses into the same stuff. However, beware the similar-looking where group in (select group_id from special_groups) Many databases (PostgreSQL in particular) are *really, really bad* when it comes to optimizing this construct. On my database, running Pg 7.2, select * from table where pkey = 1; and table has 200K rows, is optimized into an index scan with a "cost" of about 4. select * from table where pkey in (1); is identical. But select * from table where pkey in (select 1); becomes a sequential scan with a "cost" of 75,000. Recast such queries into joins: select * from table join (select 1 as pkey) as x using (pkey); If your database doesn't support such a syntax, rewrite the inner select as a view or temporary table. -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
