March 4, 2009 at 9:41 am
I have this condition in a select statement
tbl.columnName <> 'ACT'
In the actual table tbl.columnName is NULL. I thought NULL is not equal to 'ACT'. but nothing is selected when this is in the where clause. the statement only returns rows if i comment out this line.
Whats going on in the background, that makes sql server think that NULL = 'ACT' ?
March 4, 2009 at 9:57 am
winston Smith (3/4/2009)
I have this condition in a select statementtbl.columnName <> 'ACT'
In the actual table tbl.columnName is NULL. I thought NULL is not equal to 'ACT'. but nothing is selected when this is in the where clause. the statement only returns rows if i comment out this line.
Whats going on in the background, that makes sql server think that NULL = 'ACT' ?
NULL <> 'ACT' is actually undetermined, or in this case, false.
Also, NULL = 'ACT' is also undetermined, or in this case, false as well.
If you may have null values you need to do something like this:
(tbl.columnName <> 'ACT' or tbl.columnName is null)
or this:
isnull(tbl.columnName,'') <> 'ACT'
Realize that the second option may preclude the use of indexes in your query.
March 4, 2009 at 10:01 am
From Books On Line
A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.
If you want to include NULL values look at IS NULL
To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL.
March 4, 2009 at 10:01 am
winston Smith (3/4/2009)
I have this condition in a select statementtbl.columnName <> 'ACT'
In the actual table tbl.columnName is NULL. I thought NULL is not equal to 'ACT'. but nothing is selected when this is in the where clause. the statement only returns rows if i comment out this line.
Whats going on in the background, that makes sql server think that NULL = 'ACT' ?
It's not that SQL server thinks that NULL='ACT' it is just that when comparing against NULL the result comes back as 'Unkown' so this will not return any rows.
There is a good explanation here
http://www.sqlservercentral.com/articles/Advanced+Querying/fourrulesfornulls/1915/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply