SEO

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/techinf...2000/books.asp

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)

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/techinf...2000/books.asp