473,320 Members | 1,904 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
4 8296
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Fred | last post by:
Has anyone a link or any information comparing c and c++ as far as execution speed is concerned? Signal Processing algorithms would be welcome... Thanks Fred
3
by: Vithar | last post by:
I have a database that is being used as sort of a reports data warehouse. I use DTS packages to upload data from all the different sources. Right now I have it truncating the tables and appending...
2
by: louis | last post by:
When I need to perform an update against multi-million row table I typically specify @@rowcount, to reduce locks. e.g. set @@rowcount 1000 while exists (select * from myTable where col2 is...
11
by: jrefactors | last post by:
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures...
9
by: MStepansky | last post by:
Whats the difference between Javascript and Java3D? I mean can Javascript do like Java3D can? Or is Java3D on top of Javascript (the core, if thats what it is)? Then I should learn Javascript...
21
by: Anthony England | last post by:
Everyone knows that global variables get re-set in an mdb when an un-handled error is encountered, but it seems that this also happens when the variable is defined as private at form-level. So...
59
by: Mr. x | last post by:
Hello, I need a good help/tutorial for VB, please (I preffer *.chm help file). Also : for the function : now() in VBScript for getting the current time, what is the counterpart function of VB ?...
42
by: John Doty | last post by:
I realized that I have a little job on the table that is a fine test of the Python versus Standard Forth code availability and reusability issue. Note that I have little experience with either...
2
by: --CELKO-- | last post by:
I am trying to collect a few examples of SQL queries that were done with a "procedural mindset" and another solution done with a "set- oriented mindset". I have a short article at...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.