Solved

not in  to  not exists

Posted on 2013-06-18
2
157 Views
Last Modified: 2013-06-21
hi guys

I am trying to convert my NOT IN sql to a NOT EXISTS sql

NOT IN..
select *  FROM table2 where ExecutionId = '966'
and StaffID NOT IN (select BookId from table1 TE
where TE.ExecutionId = '966')

20433 rows


NOT EXISTS..
select * from table2 where
ExecutionId = '966' and  NOT EXISTS (select 1 from table1  
where table1.BookId  = table2.StaffID)            

20455 rows

I was expecting same results, any idea where my NOT EXISTS sql is getting me wrong
result.

thanks
0
Comment
Question by:royjayd
2 Comments
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39257125
The inner join will result in more rows if there are duplicates. The IN eliminates duplicated StaffIDs. Also it is not guranteed that removing the ExecutionId restriction from table1 will not change the results.
0
 

Author Comment

by:royjayd
ID: 39265949
Qlemo,
i found out the answer is NOT EXISTS takes Null values into consideration and NOT IN
just ignores them.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now