... not in ... what is the fastest way ?

Hi,
i have two tables, each has a columnn called "value". Now i want all
"values" from table_1 which does not exist in table_2. I tried:
select table_1.value from table_1 where not exists (select * from table_2
where table_2.value = table_1.value)
Is this the fastest way (in SQL-Server2000)?
thanks,
HelmutHi Helmut,
Yes.
Make sure there is an index on table2.value.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"helmut woess" <hw@.iis.at> wrote in message
news:1tm1fparsdfl.w6pfxn05crxk.dlg@.40tude.net...
> Hi,
> i have two tables, each has a columnn called "value". Now i want all
> "values" from table_1 which does not exist in table_2. I tried:
> select table_1.value from table_1 where not exists (select * from table_2
> where table_2.value = table_1.value)
> Is this the fastest way (in SQL-Server2000)?
> thanks,
> Helmut|||Your query just might be the fastest, but compare in Profiler with this one:
select value
from table_1
left join table_2
on table_2.value = table_1.value
where (table_2.value is null)
ML
http://milambda.blogspot.com/|||Am Tue, 3 Jan 2006 01:34:05 -0800 schrieb ML:

> Your query just might be the fastest, but compare in Profiler with this on
e:
> select value
> from table_1
> left join table_2
> on table_2.value = table_1.value
> where (table_2.value is null)
>
> ML
> --
> http://milambda.blogspot.com/
Thanks, i checked it with the profiler and it seems to be nearly indentical
(i have not so much records now). But your query needs some rows more and
has an additional filter, so if the table has millions of records maybe my
way is faster.
bye,
helmut|||Am Tue, 3 Jan 2006 09:36:12 -0000 schrieb Tony Rogerson:

> Hi Helmut,
> Yes.
> Make sure there is an index on table2.value.
I have an index on it - thanks,
Helmut|||Please, let us know when you test it on millions of rows. We'd like to know.
:)
ML
http://milambda.blogspot.com/