473,605 Members | 2,507 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

more efficient - exists or in

Which is more efficient:

Select * from table1 where id in (select id from table2)

or

Select * from table1 where exists(select * from table2 where
table2.id=table 1.id)
Jul 20 '05 #1
8 5243
On Wed, 11 Aug 2004 14:53:52 +0100, Trev@Work wrote:
Which is more efficient:

Select * from table1 where id in (select id from table2)

or

Select * from table1 where exists(select * from table2 where
table2.id=tabl e1.id)


Hi Trev,

That question has no one correct answer; it depends on lots of factors,
such as table structures, whether there are indexes, etc. If you really
want to know, you'll have to test it for each specific situation. I think
that in many cases, the execution plan will be equal. And you firgot to
include the third option:
Select table1.* from table1
inner join table2 on table2.id = table1.id

Another important thing to remember: when you change the query to find
rows not in the other table, behaviour of the first query will become
unpredictable by NULL values in table1.id and table2.id:

CREATE TABLE table1 (id int)
CREATE TABLE table2 (id int)
INSERT table1 (id) SELECT 1
INSERT table1 (id) SELECT 3
INSERT table1 (id) SELECT NULL
INSERT table2 (id) SELECT 1
INSERT table2 (id) SELECT 2
INSERT table2 (id) SELECT NULL
Select * from table1 where id not in (select id from table2)
Select * from table1 where not exists(select * from table2 where
table2.id=table 1.id)
Select table1.* from table1
left join table2 on table2.id = table1.id
where table2.id is null
DROP TABLE table1
DROP TABLE table2
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2
'Exists' more efficient. SQL Server hardly calculates 'in' comprassions
Jul 20 '05 #3
Trev@Work (no.email@pleas e) writes:
Which is more efficient:

Select * from table1 where id in (select id from table2)

or

Select * from table1 where exists(select * from table2 where
table2.id=table 1.id)


In 6.5 EXISTS and NOT EXISTS were often big winners over IN and NOT IN.
In SQL 2000, I have feeling that IN/NOT IN are recast into EXISTS
NOT EXISTS, although I have not confirmed this.

Personally, I always use EXISTS and NOT EXISTS, and as Hugo pointed
out there are some gotchas with NOT IN.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
See
http://groups.google.nl/groups?hl=nl...er.programming

Gert-Jan
"Trev@Work" wrote:

Which is more efficient:

Select * from table1 where id in (select id from table2)

or

Select * from table1 where exists(select * from table2 where
table2.id=table 1.id)


--
(Please reply only to the newsgroup)
Jul 20 '05 #5
On Wed, 11 Aug 2004 21:53:07 +0000 (UTC), Erland Sommarskog wrote:
Trev@Work (no.email@pleas e) writes:
Which is more efficient:

Select * from table1 where id in (select id from table2)

or

Select * from table1 where exists(select * from table2 where
table2.id=table 1.id)


In 6.5 EXISTS and NOT EXISTS were often big winners over IN and NOT IN.
In SQL 2000, I have feeling that IN/NOT IN are recast into EXISTS
NOT EXISTS, although I have not confirmed this.

Personally, I always use EXISTS and NOT EXISTS, and as Hugo pointed
out there are some gotchas with NOT IN.


One follow-up question: is there a performance difference between

select * from table1 where exists(select * from table2 where
table2.id=table 1.id)

and

select * from table1 where exists(select id from table2 where
table2.id=table 1.id)

(assuming that table1.id and table2.id are clustered primary keys)
Jul 20 '05 #6
> One follow-up question: is there a performance difference between

select * from table1 where exists(select * from table2 where
table2.id=table 1.id)

and

select * from table1 where exists(select id from table2 where
table2.id=table 1.id)


There are not difference in
exists ( select * ....
exists ( select 1 ....
exists ( select id ....
SQL Server execute second expression for all situations
Jul 20 '05 #7
On Thu, 12 Aug 2004 19:26:57 +0300, Garry wrote:
One follow-up question: is there a performance difference between

select * from table1 where exists(select * from table2 where
table2.id=table 1.id)

and

select * from table1 where exists(select id from table2 where
table2.id=table 1.id)


There are not difference in
exists ( select * ....
exists ( select 1 ....
exists ( select id ....
SQL Server execute second expression for all situations


thanks
Jul 20 '05 #8
Ross Presser (rp******@imtek .com) writes:
One follow-up question: is there a performance difference between

select * from table1 where exists(select * from table2 where
table2.id=table 1.id)

and

select * from table1 where exists(select id from table2 where
table2.id=table 1.id)

(assuming that table1.id and table2.id are clustered primary keys)


As far as I know the * or id are only syntactic sugar in this case,
so as Garry says, it does not matter which you use.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
1581
by: Bosconian | last post by:
I need to make an associated array (all with the same key name) using values from another array. Is there a more efficient way to doing this in one pass (instead of looping)? I'm always looking to learn something new. :-) -------------------------------------------------------------------- $fields = array('username', 'passwd', 'firstname', 'lastname', 'email');
16
1739
by: MetalOne | last post by:
1) def f(xs): for x in xs: if test(x): return True return False I know that I can do (2), but it operates on the whole list and the original may break out early. I want the efficiency of (1), but the conciseness of (2). 2)
21
3908
by: Rabbit63 | last post by:
Hi: I want to show a set of records in the database table on the clicnt browser. I have two ways to do this (writen in JScript): 1.The first way is: <% var sql = "select firstname from table1"; var obj=new ActiveXObject("ADODB.Recordset");
3
2205
by: Sean | last post by:
Hi all I have a bit of a dilema that I am hoping some of you smart dudes might be able to help me with. 1. I have a table with about 50 million records in it and quite a few columns. 2. I have another table with just over 300 records in it and a single column (besides the id).
8
2437
by: brian.digipimp | last post by:
I turned this in for my programming fundamentals class for our second exam. I am a c++ newb, this is my first class I've taken. I got a good grade on this project I'm just wondering if there is a better and more efficient way to write this program. //This program calculates the users Gross Pay and subtracts the tax based off of Marital Status //and the number of exemptions they select #include <iostream>
4
3284
by: Koen | last post by:
Hi all, At work I created a database which is really helpful. The database is used by approx 15 users. Everything worked great, until I added some 'scoreboard' forms and reports. I get the "Cannot open any more databases" error. The 'scoreboard' form show a matrix of 6 columns, 7 rows. Each cell is calculated separate by (what I call complex) queries.
10
3492
by: Amit | last post by:
Which is more efficient and why? p++ or ++p. Thanks.
3
6416
by: Brian Wotherspoon | last post by:
I have a table with data that is refreshed regularly but I still need to store the old data. I have created a seperate table with a foreign key to the table and the date on which it was replaced. I'm looking for an efficient way to select only the active data. Currently I use: SELECT ... FROM DataTable AS D LEFT OUTER JOIN InactiveTable AS I ON I.Key = D.Key
3
2830
by: Ken Fine | last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets and DataTables should be able to answer fairly easily. The basic question is how I can efficiently match data from one dataset to data in a second dataset, using a common key. I will first describe the problem in words and then I will show my code, which has most of the solution done already. I have built an ASP.NET that queries an Index Server and returns a...
0
8004
marktang
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...
0
7934
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,...
1
8071
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,...
0
6743
agi2029
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...
0
5445
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();...
0
3958
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2438
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
1
1541
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1271
bsmnconsultancy
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...

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.