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
4  8353 
"aj" <ro****@mcdonal ds.comwrote in message
news:13******** *****@news.supe rnews.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 <ro****@mcdonal ds.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 <ro****@mcdonal ds.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
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****@mcdonal ds.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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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
|
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 with
fresh data. I was considering using updates instead and my question was
which is more efficent?
|
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 null)
update myTable
set col2 = col1 + 'blahblah'
where col2 is null
|
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
execute in the database server with better performance?
Please advise good references for Oracle stored procedures also.
thanks!!
|
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 BEFORE Java3D,
right? no?
Worse, whats the difference between Java and Javascript?
Whats the difference between Visual Basic and Visual Java++? (I still thought
Visual Basic is like BASIC programming to allow you to write
| | |
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 if "global variables get re-set" doesn't tell the whole story, then what
does?
***please note***
I'm not looking for a solution - I'm looking for a more detailed description
of what happens when an un-handled error occurs - possibly with help file...
|
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 ?
Thanks :)
|
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 Python or Standard Forth
(but I have much experience with a very nonstandard Forth). I've noodled
around a bit with both gforth and Python, but I've never done a serious
application in either. In my heart, I'm more of a Forth fan: Python is a
bit...
|
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
http://www.dbazine.com/ofinterest/oi-articles/celko5
with an example of what I want.
Anyone got some examples?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
