. . . not in (select . . . - 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

Transact-SQL (2000)

. . . not in (select . . .

Author

Topic

ALBean
Starting Member

12 Posts

Posted - 2004-06-03 : 11:16:54

I use the query at the bottom a lot but I'm guessing that there is a better "Guru" way of doing it. If there is a link to an artile or just a simple (but better) query could one of you Gurus post it?

Thanks.

Table A
MyCol
1
2
3
4

Table B
MyCol
3
4

select * from A where MyCol not in (select MyCol from B)
1
2

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-03 : 11:41:02

Thats just a no-match... another way of writing it is:

Select A.*
From A
Left Join B
On A.MyCOl = B.MyCol
Where B.MyCol is null

I haven't tested the diference, but it seems like a join would be slightly more optimized.

Corey

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-03 : 13:18:27

NOT IN is a very dangerous construct if the base table in your subquery contains NULLS. (Read more.)

Usually, the best query plan (Read more.) for SQL Difference comes from ...
select * from A where not exists (select 1 from B where A.MyCol = B.MyCol)

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources