DB2 8.2 LUW FP14

Is there any real difference between
select blahblahblah... where blah IN (select blah......)
versus
select blahblahblah... where blah = ANY (select blah.....)
versus
select blahblahblah... where exists (select blah.....)

Is one more ANSI than another? Which is most portable?

TIA

aj

"aj" <ronald@mcdonalds.comwrote in message
news:13a9u74g89euodc@news.supernews.com...

DB2 8.2 LUW FP14
>
Is there any real difference between
select blahblahblah... where blah IN (select blah......)
versus
select blahblahblah... where blah = ANY (select blah.....)
versus
select blahblahblah... where exists (select blah.....)
>
Is one more ANSI than another? Which is most portable?
>
TIA
>
aj

IN, ANY and EXISTS are all ANSI SQL 92.

A IN (B)

is equivalent to

A = ANY (B)

In fact the SQL standard defines IN by using the ANY syntax.

EXISTS is entirely different. Probably you meant to imply some correlated
subquery in place of some simple subqueries in your other examples. Without
seeing the actual queries, DDL etc I won't speculate on whether such queries
might be equivalent.

--
David Portas

On Mon, 23 Jul 2007 14:51:49 -0400, aj <ronald@mcdonalds.comwrote:

>DB2 8.2 LUW FP14
>
>Is there any real difference between
select blahblahblah... where blah IN (select blah......)
>versus
select blahblahblah... where blah = ANY (select blah.....)
>versus
select blahblahblah... where exists (select blah.....)
>
>Is one more ANSI than another? Which is most portable?
>
>TIA
>
>aj

Yes. IN means the compiler needs to put together a result set, and see
if it matches. EXISTS wll use any availible INDEX.

For smaller reult sets IN() is usually faster, for larger use EXISTS.
In some cases execution time will be the same, in others, there may be
a significant difference.

B.

Brian Tkatch <N/Awrote:

On Mon, 23 Jul 2007 14:51:49 -0400, aj <ronald@mcdonalds.comwrote:
>

>
Yes. IN means the compiler needs to put together a result set, and see
if it matches. EXISTS wll use any availible INDEX.

I wouldn't draw such a conclusion. The optimizer could rewrite one form
into the other and, thus, result in the same access plan.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

On Wed, 25 Jul 2007 20:18:30 +0200, Knut Stolze <stolze@de.ibm.com>
wrote:

>Brian Tkatch <N/Awrote:
>

>
>I wouldn't draw such a conclusion. The optimizer could rewrite one form
>into the other and, thus, result in the same access plan.

You know, the optimizer really gets in the way here. :P

B.