Solved

not in  to  not exists

Posted on 2013-06-18
2
162 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 69

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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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