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)
