My DBA has advised me that it is better to use the EXISTS statement
than the IN statement. He says that it uses less server resources or
is better handled by the optimiser.

Does anybody know if this is true?

Saul Margolis wrote:

> My DBA has advised me that it is better to use the EXISTS statement
> than the IN statement. He says that it uses less server resources or
> is better handled by the optimiser.
>
> Does anybody know if this is true?

Your DBA is quoting mythology as fact. As with everything in an RDBMS
.... it depends.

While one might say it is usually true ... it is not at all a
situation where one can say that it is always true.

Unless performance and scalability are not an issue it is always
best to write something both ways and test.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

>> Does anybody know if this is true? <<

It depends. Older versions of DB2 used to use indexes for EXISTS()
predicates and they were quite a bit faster than the IN() predicate
that mateiralized a temp table on the fly. Since a myth was born.
These days, a good optimizer will find the best way regardless of the
predicate. I'd say use the one which is easier to read, unless you
find a real difference by testing both.