use "left join" or "not exists" ?

Using SQL Server 2000,

Example :
==
create table jtest1 (id int primary key, col1 varchar(10))
create table jtest2 (id2 int primary key, col2 varchar(10))

insert jtest1 values (1, 'hello')
insert jtest1 values (2, 'two')

insert jtest2 values (1, 'world')
==

Now, I think the following two queries produce the same end result :

1) select j1.* from jtest1 j1 left join jtest2 on id = id2 where id2 is
null

2) select j1.* from jtest1 j1 where not exists(select * from jtest2 j2
where j1.id = j2.id2)

My preference in this case is number 2, since it "feels like" it should be
more efficient.
Is this the case ?

TIA,
Stephen

Most probably the EXIST will result in same execution plan as the left join. I prefer EXISTS, as it
tend to perform predictable and I think it is more intuitive.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


"Stephen Ahn" <noaddress> wrote in message
> Using SQL Server 2000,
>
> Example :
> ==
> create table jtest1 (id int primary key, col1 varchar(10))
> create table jtest2 (id2 int primary key, col2 varchar(10))
>
> insert jtest1 values (1, 'hello')
> insert jtest1 values (2, 'two')
>
> insert jtest2 values (1, 'world')
> ==
>
> Now, I think the following two queries produce the same end result :
>
> 1) select j1.* from jtest1 j1 left join jtest2 on id = id2 where id2 is null
>
> 2) select j1.* from jtest1 j1 where not exists(select * from jtest2 j2 where j1.id = j2.id2)
>
> My preference in this case is number 2, since it "feels like" it should be more efficient.
> Is this the case ?
>
> TIA,
> Stephen
>
>