June 8, 2012 at 4:35 am
Hi
why int columns with Null value is treated as zero. It caused trouble in one of my proc. Here is the sample code:
create table tt(id int,id2 int)
insert into tt select 1,2
insert into tt select 1,null
select * from tt where id2<>0
drop table tt
Now I was expecting that with condition <>0, NULL values will be returned because NULL is not 0. But it was not returned in SQL Server 2008 R2. To me, it doesn't logically look correct.
Thoughts?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 8, 2012 at 4:42 am
have a look through books online - or google "NULL EVALUATION SQL SERVER"
http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server
http://msdn.microsoft.com/en-us/library/ms172138(v=vs.80).aspx
basically (in simplest terms) NULL does not evaluate: you cannot say =NULL or <> NULL
you can use the IS NULL and IS NOT NULL functions (or also the isnull(x,y) )
for example
where isnull(x,0)<>0
where x<>0 and x is not null
but generally if find it better to think about your data model and think why do you need to put nulls in the table???? can it be not null
MVDBA
June 8, 2012 at 4:43 am
thats correct as you cannot compare a null value as the result comes back as unknown so is excluded from the result set
http://msdn.microsoft.com/en-us/library/ms191270%28v=sql.105%29.aspx
June 8, 2012 at 4:48 am
But my where clause is not doing comparision with NULL. it is doing comparision with 0.
And since NULL is not a zero, those records should come logically.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 8, 2012 at 4:51 am
S_Kumar_S (6/8/2012)
But my where clause is not doing comparision with NULL. it is doing comparision with 0.And since NULL is not a zero, those records should come logically.
no - not correct
please have a look at the links i posted
NULL does not evaluate at all
therefore NULL is "unknown" - effectively sql does not know if it is <>0 or =0 (since it is "unknown")
MVDBA
June 8, 2012 at 4:52 am
You cannot compare a value to a null value, NULL is NULL and only ever NULL
What is NULL is it 0,1,2,3,4,5,'IDontKnowWhatIAm','ShouldIBeThisValueOf60834abc', so as there is no value it is excluded when you filter on the column. The compare is none deterministic and removed from the result set.
Again, you CANNOT COMPARE a value with NULL.
June 8, 2012 at 4:52 am
Since NULL means "unknown", you can't know whether NULL is 0 or not. Therefore NULL<>0 cannot be evaluated as "True". This is why you need to be very careful with NULLs, as other posters have suggested.
John
June 8, 2012 at 4:56 am
anthony.green (6/8/2012)
thats correct as you cannot compare a null value as the result comes back as unknown so is excluded from the result sethttp://msdn.microsoft.com/en-us/library/ms191270%28v=sql.105%29.aspx
Ok, got your point. So if some row in the column has NULL values,then my condition effectively becomes Where NULL<>0 and that row won't be returned. So it also means that whenever I'll put a nullable column in where clause , the rows with null values will never be returned provided I have not used IS NULL or IS NOT NULL.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 8, 2012 at 5:00 am
Despite all the arguments, I still feel that if sql server can not evaluate a condition, then those rows should have been returned instead of preventing them. May be I am missing the bad consequences of it, But this is what looks logical to me.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 8, 2012 at 5:03 am
or make your column NOT NULL - and set a default value that means "unassigned"
e.g (using a mocked up status table)
status value
1 open
2 closed
3 cancelled
becomes
status value
0 unknown
1 open
2 closed
3 cancelled
if you are using an ID column surely it must relate to something, otherwise it's pointless... (in all but a few instances)
this makes life a lot easier
MVDBA
June 8, 2012 at 5:06 am
Not at all. Read up on 3-state logic. TRUE, FALSE and UNKNOWN. Any comparisons with NULL (other than IS NULL/IS NOT NULL) return UNKNOWN, not TRUE or FALSE
WHERE SomeColumn != 0 must only return rows where the condition SomeColumn != 0 returns TRUE. UNKNOWN is not TRUE and hence any rows that have NULL for SomeColumn cannot possibly be returned without completely rewriting boolean logic.
If you also want the null values, then you need to include a condition that does return TRUE when the column is NULL. Something like WHERE SomeColumn != 0 OR SomeColumn IS NULL.
p.s. It's not that SQL can't evaluate the condition. It absolutely can evaluate it and does, and the result of the evaluation is the logical state UNKNOWN.
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
June 8, 2012 at 6:00 am
GilaMonster (6/8/2012)
Not at all. Read up on 3-state logic. TRUE, FALSE and UNKNOWN. Any comparisons with NULL (other than IS NULL/IS NOT NULL) return UNKNOWN, not TRUE or FALSEWHERE SomeColumn != 0 must only return rows where the condition SomeColumn != 0 returns TRUE. UNKNOWN is not TRUE and hence any rows that have NULL for SomeColumn cannot possibly be returned without completely rewriting boolean logic.
If you also want the null values, then you need to include a condition that does return TRUE when the column is NULL. Something like WHERE SomeColumn != 0 OR SomeColumn IS NULL.
p.s. It's not that SQL can't evaluate the condition. It absolutely can evaluate it and does, and the result of the evaluation is the logical state UNKNOWN.
Thanks for explanation Gail. The line "WHERE SomeColumn != 0 must only return rows where the condition SomeColumn != 0 returns TRUE." makes things more clear.
I infact used WHERE SomeColumn != 0 OR SomeColumn IS NULL.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply