LEFT OUTER JOIN vs NOT IN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

LEFT OUTER JOIN vs NOT IN

Author

Topic

GenerationWithoutName
Starting Member

26 Posts

Posted - 2003-02-13 : 04:55:26

hi all,
i have 3 tables :
CREATE TABLE tblA
( id int PRIMARY KEY,
name varchar(20)
)
CREATE TABLE tblB
( id int FOREIGN KEY REFERENCES tblA(id),
code varchar(3) PRIMARY KEY,
name varchar(20)
)
CREATE TABLE tblC
( id FOREIGN KEY REFERENCES tblA (id),
dvalue decimal(12,2),
CONSTRAINT PK_tblA PRIMARY KEY (id)
)
which faster with these 2 statement :
1. SELECT a.id, a.name, b.code, b.name
FROM tblA AS a INNER JOIN tblB AS b ON a.id=b.id
WHERE a.id NOT IN (SELECT id FROM tblC)
2. SELECT a.id, a.name, b.code, b.name
FROM tblA AS a INNER JOIN tblB AS b ON a.id=b.id
LEFT OUTER JOIN tblC AS c ON a.id=c.id
WHERE c.id IS NULL

thank's All

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-02-13 : 07:01:45

They both take the same amount of time since there is no data in the tables.

You can also play with:
select a.id, a.name, b.code, b.name
FROM tblA AS a INNER JOIN tblB AS b ON a.id=b.id
WHERE not exists (select * from tblc where a.id = id)

and read http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13615

The bottom line is you can look at the execution plans yourself and you can time the querys too. See which works best for you.

Jay White
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-13 : 09:06:23

I did some testing on v7 sp2 some time ago (and also with a not exists).
There was not much difference with the data I had and surprisingly the not in was slightly faster.
But with an update the not in clause was very slow and the left outer join faster.

It's the sort of thing that can change with service packsand data for the select but I would always expect the left outer join to perform well.

Also the not in clause just doesn't look nice.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources