Re: I would like to know about IN operator,

"Pasi Parkkonen" <pasi_at_eigenvalue.com> wrote in message news:<a50279$3tn$1_at_tron.sci.fi>...
> "Leader" <sohelcsc_at_yahoo.com> wrote in message
> news:b1a93c73.0202190606.7a18c234_at_posting.google.com...
> > Hi All,
> > I have a problem for that i need to know Is there any maximum range
> > within IN operator? I mean inside IN how many values we can compare
> > (13, 14, 23,......)??
> >
>
> You can use IN operator only with less than 10000 comparisons.
> For example you cannot use it in sub-select if the sub-select
> returns more than 10000 rows.
>
> 'IN' is also quite slow, you should be able to find another solution.
>
> Regards,
> Pasi Parkkonen,
> EigenDBA

For large amounts of comparison you are better of using exists

select col1, col2 from table1 a where exists in (select 1 from table2 b
where a.col1=bcol2 and b.col2...);

Performance on exists is much better than in, also you really will benefit if both tables have an primary key/index Received on Fri Feb 22 2002 - 00:21:06 CET