Not Exists / 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)

Not Exists / Not IN

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2006-06-26 : 18:53:04

Can any one please explain the difference between Not Exists / Not IN

I have 2 tables and there is difference of 334 records. I am able to get by using NOT IN, but when I use not exists, there is no records to return.

here is query for lookup.

select colID from table_bk
where not exists (select a.colId from table_bk a
inner join #temp2
on a.colId = #temp2.colId)

select colID from table_bk
where colID not in (select a.colId from table_bk a
inner join #temp2
on a.colId = #temp2.colId)

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-26 : 19:06:41

EXISTS uses subquery to check for existance of rows

IN check for column matches value selected from subquery

There is a simple example that compares EXISTS & IN in the Books On Line. Look up EXISTS in BOL.

So your 1st query can be rewritten to

select colID
from   table_bk
where  not exists (select * from #temp2 x where temp2.colId = table_bk.colID)

and the 2nd Query should be

select colID 
from  table_bk
where colID not in (select colId from #temp2)

thanksfor help
Posting Yak Master

106 Posts

Posted - 2006-06-26 : 19:19:07

Thank U, I got it.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-06-26 : 19:19:52

plus you can put more than one condition in the exists and only one in the "not in"

Go with the flow & have fun! Else fight the flow

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-26 : 23:48:16

quote:
Originally posted by thanksfor help

Can any one please explain the difference between Not Exists / Not IN

I have 2 tables and there is difference of 334 records. I am able to get by using NOT IN, but when I use not exists, there is no records to return.

here is query for lookup.

select colID from table_bk
where not exists (select a.colId from table_bk a
inner join #temp2
on a.colId = #temp2.colId)

select colID from table_bk
where colID not in (select a.colId from table_bk a
inner join #temp2
on a.colId = #temp2.colId)

thanks

Once EXISTS meet the records it will quits but IN will select all possible rows. Performance wise the exists and not exists is the best one
-- KK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-27 : 01:40:33

http://groups.google.de/group/microsoft.public.sqlserver.programming/tree/browse_frm/thread/1c12caa50923d3d5/f86de13e0ed65a37?rnum=1&hl=de&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.programming%2Fbrowse_frm%2Fthread%2F1c12caa50923d3d5%2Fe96cf1972f400ad9%3Fhl%3Dde%26lr%3D%26ie%3DUTF-8%26rnum%3D48%26prev%3D%2Fgroups%3Fq%3Dsql%2Bserver%2Bdifference%2Bnot%2Bin%2Bnot%2Bexists%26start%3D40%26hl%3Dde%26lr%3D%26ie%3DUTF-8%26selm%3Dumy7hwRaBHA.1916%2540tkmsftngp05%26rnum%3D48%26#doc_f530df34d5afe639

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources