Re: WHERE IN vs. WHERE OR OR OR

Hello, Peter Lancashire wrote: > > Art S. Kagel wrote: > > > > Daniel Kirkdorffer wrote: > > > > > > Hi, > > > > > > Using JDBC against and Informix database I was wondering if anyone > > > knew which WHERE clause would be more efficient: > > > > > > SELECT x FROM y WHERE x IN (1, 2, 3,..., n)> > > > > > or > > > > > > SELECT x FROM y WHERE x = 1 OR x = 2 OR x = 3 OR ... OR x = n> > > > > > > In general an IN clause is more efficient than a set of OR conditions. > > Just curious, but why? > > If they are logically the same, what is the optimiser doing representing > them differently internally? Does it not analyse multiple OR conditions > for common features? I'm not sugesting it should, just interested to > find out how sophisticated the optimiser is. yes, that is true, this depends on optimizer. You must look to documentation for your database. There is only one reason why use clause 'IN'. SELECT * FROM y WHERE x IN (SELECT a FROM b WHERE c>100); This is not possible by OR clause. But if you are asking in WHERE clause for fields on which exist key, don't care, this is enought fast in both cases. And if you want realy know what is faster, simply try on BIG table. I newer work with Informix but every database has your own specific type of optimalization and I don't know if you find something about this one in standard documentation. This is occasionally part of some specific internal materials (you must pay for training :-) ). ----------------------------------------- Pavel Tavoda <mailto:PavelT@axioma.co.at> CORBA, ODBMS, RDBMS and Java technologies