IN versus ANY versus EXISTS

[aj]

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

[David Portas]

"aj" <ro****@mcdonalds.comwrote in message
news:13*************@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

[Brian Tkatch]

On Mon, 23 Jul 2007 14:51:49 -0400, aj <ro****@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.

[Knut Stolze]

Brian Tkatch <N/Awrote:

On Mon, 23 Jul 2007 14:51:49 -0400, aj <ro****@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.

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

[Brian Tkatch]

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

>Brian Tkatch <N/Awrote:

>On Mon, 23 Jul 2007 14:51:49 -0400, aj <ro****@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.

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.