[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

not in  to  not exists

Posted on 2013-06-18
2
Medium Priority
?
166 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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