Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

not in to not exists

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
royjayd
Asked:
royjayd
1 Solution
 
QlemoC++ DeveloperCommented:
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
 
royjaydAuthor Commented:
Qlemo,
i found out the answer is NOT EXISTS takes Null values into consideration and NOT IN
just ignores them.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now