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)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)|||'Exists' more efficient. SQL Server hardly calculates 'in' comprassions|||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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||See
http://groups.google.nl/groups?hl=n...ver.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)|||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)|||> 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|||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|||Ross Presser (rpresser@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp