467,153 Members | 1,091 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,153 developers. It's quick & easy.

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
Jul 23 '07 #1
  • viewed: 7245
Share:
4 Replies
"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


Jul 24 '07 #2
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.
Jul 25 '07 #3
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
Jul 25 '07 #4
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.
Jul 25 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by Fred | last post: by
3 posts views Thread by Vithar | last post: by
9 posts views Thread by MStepansky | last post: by
42 posts views Thread by John Doty | last post: by
2 posts views Thread by --CELKO-- | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.