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)