November 3, 2010 at 9:26 am
I have a subquery as shown below that returns no rows when there is a null in the subquery. But it is ok without the keyword NOT. Why doese it work with "IN" but not with "NOT IN"?
SELECT * FROM dbo.Enrollment
WHERE SID NOT IN (
SELECT SID FROM dbo.BusRoute)
Of course, at this point i realize that I have do this:
SELECT * FROM dbo.Enrollment
WHERE SID NOT IN (
SELECT SID FROM dbo.BusRoute
WHERE SID IS NOT NULL)
I found some related information at the link below. It states that I can use NOT EXIST. But I've never used it before. I don't know the difference between NOT EXIST and NOT IN.
http://www.sqlservercentral.com/Forums/Topic102288-8-1.aspx?Highlight=%22not+in%22+null
Thanks!
November 3, 2010 at 9:34 am
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-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
November 3, 2010 at 9:36 am
Because "null" technically means "unknown value". If you aren't sure what a value is, can you really say that it isn't 3 or A or whatever? Thus, Where Not In with a null in the values will always return no rows, because it mathematically could be a match.
The Not Exists version is very similar, and SQL Server will often convert it to a Not In query anyway, behind the scenes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2010 at 9:43 am
GSquared (11/3/2010)
The Not Exists version is very similar, and SQL Server will often convert it to a Not In query anyway, behind the scenes.
Except it behaves differently in the presence of nulls. A single null in the subquery will result in NOT in returning no rows,however NOT EXISTS may return rows.
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
November 4, 2010 at 6:21 am
GilaMonster (11/3/2010)
GSquared (11/3/2010)
The Not Exists version is very similar, and SQL Server will often convert it to a Not In query anyway, behind the scenes.Except it behaves differently in the presence of nulls. A single null in the subquery will result in NOT in returning no rows,however NOT EXISTS may return rows.
Which is why I said "very similar". But it is odd that the behavior is different, if you really look at the justification for Not In working the way it does. I've often wondered about that, but never bothered to find out how it's justified.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 8:03 am
GSquared (11/4/2010)
But it is odd that the behavior is different, if you really look at the justification for Not In working the way it does. I've often wondered about that, but never bothered to find out how it's justified.
I tried for my reasoning on the above blog posts, how accurate I am is another matter.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply