issue w/ "not in" in my where

Question

issue w/ "not in" in my where

BaldingLoopMan

SSCertifiable

Points: 6378
More actions
July 1, 2010 at 12:37 pm

#387263

the below do not return the same results...i think it is because it is trying to say where person not in null which doesnt hold true. Is that correct?
select tt.*
from TempBaby.dbo.Admin2_543 tt (nolock)
WHERE tt.PersonId not IN (SELECT PersonId
FROM TempBaby.dbo.SuccessCals (nolock))
select *
from TempBaby.dbo.Admin2_543 tt
left join tempbaby.dbo.SuccessCals sc
on sc.personid = tt.personid
where sc.personid is null
I cannont provide the data here cause there are too many recs and i cannot get it to not match w/ a subset of recs. U will notice that they are written identically though. Any ideas why the first one doesnt retrun records and the second does? The second query is my version and is returning correct data. Just dont understand why the first is not.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

Edited: Sorry I did not see the bottom paragraph. well the query is reformated anyway.lol sorry I did not see your explanation at the bottom.

there seams to be two seperate queries here.

--Query 1

select tt.*

from TempBaby.dbo.Admin2_543 tt (nolock)

WHERE

tt.PersonId not IN

(

SELECT PersonId

FROM TempBaby.dbo.SuccessCals (nolock)

)

--Query 1 end

--Query 2

select *

from TempBaby.dbo.Admin2_543 tt

left join tempbaby.dbo.SuccessCals sc

on sc.personid = tt.personid

where sc.personid is null

--Query 2 end

In the second query If there are no null personid's in TempBaby.dbo.Admin2_543 then nothing would be returned since the join is based on personid being null in the joning table.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

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

http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

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

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 3

the issue is if there is at least one record in the select inside the where that you are "not in"ing to then the whole result set returns nothing. So in this instance because there is one record in the successfulcals table then the whole result set returns nothing...thanks guys

--Query 1

select tt.*

from TempBaby.dbo.Admin2_543 tt (nolock)

WHERE

tt.PersonId not IN

(

SELECT PersonId

FROM TempBaby.dbo.SuccessCals (nolock)

)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

This looks like it could be a case of mistaken identity....

Please try this:

select tt.*

from TempBaby.dbo.Admin2_543 tt (nolock)

WHERE

tt.PersonId not IN

(

SELECT SC.PersonId

FROM TempBaby.dbo.SuccessCals AS SC (nolock)

)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);