By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

IN versus ANY versus EXISTS

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
"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

P: n/a
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

P: n/a
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

P: n/a
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.