469,330 Members | 1,373 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

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=table1.id)
Jul 20 '05 #1
8 5064
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=table1.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=table1.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@please) 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=table1.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****@sommarskog.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=table1.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@please) 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=table1.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=table1.id)

and

select * from table1 where exists(select id from table2 where
table2.id=table1.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=table1.id)

and

select * from table1 where exists(select id from table2 where
table2.id=table1.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=table1.id)

and

select * from table1 where exists(select id from table2 where
table2.id=table1.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=table1.id)

and

select * from table1 where exists(select id from table2 where
table2.id=table1.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****@sommarskog.se

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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Bosconian | last post: by
10 posts views Thread by Amit | last post: by
3 posts views Thread by Brian Wotherspoon | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.