February 4, 2010 at 2:03 am
I have a table called NUMS with a single column n.
And I fill values 1,2,3,4,5,null in it.
Consider a query
SELECT n FROM Nums
WHERE n NOT IN (1, 2, null)
In this case I guess it's converted to
SELECT n FROM Nums
Where NOT(n = 1 OR n = 2 OR n = null)
The comparison n=null will evaluate UNKNOWN for all the values on n
including null.
Negating UNKNOWN returns unknown and therefore it returns empty set.
Consider the opposite case
SELECT n FROM Nums WHERE n IN(1, 2, null)
Here also the comparison is being performed so it should return unknown.
SO the whole result set should be empty.
But it's returning 1 and 2.
Can anyone explain in detail what's happening.
February 4, 2010 at 2:13 am
It will depend on your Ansi nulls setting
Consider this
set ansi_nulls on
declare @v integer
select @v=null
if(@V=null) begin
select 'Equals'
end
go
set ansi_nulls off
declare @v integer
select @v=null
if(@V=null) begin
select 'Equals'
end
go
If you execute that then you will on see one 'Equals' (where ansi nulls is off).
Best Practice is to never compare a value to null , test using is null / is not null
February 4, 2010 at 2:20 am
It comes down to the truth table for ANDs and ORs when nulls are involved. (It's in Books Online for anyone interested)
In the case of NOT in,
SELECT n FROM Nums
WHERE n NOT IN (1, 2, null)
equates to
WHERE n != 1 AND n !=2 and n!=null
The 3rd returns null and the truth table for ANDs and NULLs shows that as soon as there is one null involved, the only options that can be returned are FALSE or NULL.
TRUE AND NULL = NULL. FALSE AND NULL = FALSE
SELECT n FROM Nums
WHERE n IN (1, 2, null)
equates to
WHERE n = 1 OR n =2 OR n=null
The truth table for ORs and NULLs is the opposite way round to AND
TRUE OR NULL = TRUE. FALSE OR NULL = NULL
Hence, if one of the other comparisons returns true, the entire expression is true and the rows are returned.
I have a half-written blog post on the NOT IN (NULL) surprise.
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
February 4, 2010 at 2:38 am
In the case of NOT in,
SELECT n FROM Nums WHERE n NOT IN (1, 2, null)equates to
WHERE n != 1 AND n !=2 and n!=nullThe 3rd returns null
U mean to say that n!=null returns unknown when n is null, right ?
Also, please post the link to the TRUTH Table.
February 4, 2010 at 2:51 am
akshaycjoshi (2/4/2010)
U mean to say that n!=null returns unknown when n is null, right ?
If you want to be precise and formal, yes. I'll use null and interchangeably in logical expressions, especially in 'casual' writing (like a forum post).
Also, please post the link to the TRUTH Table.
Google it. Titles in the offline BoL are "AND (Transact-SQL)" and "OR (Transact-SQL)" You'll find them somewhere on msdn.microsoft.com
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
February 4, 2010 at 3:20 am
Thanks a lot for clearing all my doubts.
😀
February 4, 2010 at 4:41 am
You should always be careful with the 'NOT IN' especially when you compare to a result set of a sub-query.
Select * from table where some_attribute NOT IN (Select distinct some_attribute from table2)
should always be written as
Select * from table where some_attribute NOT IN (Select distinct IsNull(some_attribute, 'ImpossibleValue') from table2)
to avoid confusing results like discussed here. There can always accidentally be a NULL among the data you are not aware of...
Ville
February 4, 2010 at 5:49 am
Ville Lucander (2/4/2010)
Select * from table where some_attribute NOT IN (Select distinct some_attribute from table2)should always be written as
Select * from table where some_attribute NOT IN (Select distinct IsNull(some_attribute, 'ImpossibleValue') from table2)
Except that the DISTINCT is a waste of time and typing. IN looks for matches, it doesn't matter how many instances of the value there is.
IN (1,2,3) is the same as IN (1,1,1,1,1,2,2,3,3,3,3). Same for 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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply