not exists versus not in

Question

Post reply

not exists versus not in

timscronin

SSCertifiable

Points: 6851
More actions
April 23, 2013 at 9:00 am

#277002

Looking at the 2 statements should they not return the same?
select *,'I' from pharmdb.pat.dbo.patients where PHPatid not in (select PHPatid from Patients)
select *,'I' from pharmdb.pat.dbo.patients where not exists (select phpatid from Patients inner join pharmdb.pat.dbo.patients p on p.phpatid = patients.PHPatid)
2nd statement returns no records

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Those two aren't equivalent, the second has a join that the first does not.

The equivalent with exists would be

select *,'I' from pharmdb.pat.dbo.patients po where NOT EXISTS (SELECT 1 FROM Patients pi where po.PHPatid = pi.PHPatid)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

timscronin (4/23/2013)
Looking at the 2 statements should they not return the same?
select *,'I' from pharmdb.pat.dbo.patients where PHPatid not in (select PHPatid from Patients)
select *,'I' from pharmdb.pat.dbo.patients where not exists (select phpatid from Patients inner join pharmdb.pat.dbo.patients p on p.phpatid = patients.PHPatid)
2nd statement returns no records

Slight change to how I would code these select statements:

select

pat.*, -- Replace with actual list of columns required for the query, and use table alias

'I' as SomeColumnHeader -- name the column

from

pharmdb.pat.dbo.patients pat

where

pat.PHPatid not in (select pats.PHPatid from Patients pats);