NULLS not behaving as expected
-
Working on a database where SET ANSI_NULLS is set to OFF. However, an example query might include:
WHERE field1 <> '0'
but the result does not return records where field1 is null. According to BOL, the boolean expression should return TRUE under these circumstances (and the records where field1 is null should be returned) but it obviously isn't. Any ideas please.
-
Check out this thread on the T-Sql forum.
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=241022#bm242133
ron
-
Just check BOL. ANSI_NULLS setting is not for this purpose. It controls the result when a column/value comapres to NULL.
The SQL-92 standard requires that an equals (=) or not equal to (<>
comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>
comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column. In addition, a SELECT statement using WHERE column_name <> XYZ_value returns all rows that are not XYZ value and that are not NULL. -
Mmm...i am not sure we're talking about the same thing.
I am not looking for NULLs. I would use IS NULL or IS NOT NULL for that.
I am looking for where the field is <> '0'. A NULL has no (determinable) value whatsoever and therefore it is <> '0'. Therefore the boolean <> should be TRUE and return the records containing the NULLs.
I've been reading BOL all day and read those articles.
-
Normally I just look at them in the rear-view mirror and threaten to pull over.
... ok, I'm not helping. Sorry.
-
The short answer from my perspective is to ALWAYS treat nulls as an exceptional case.
If you are relying on the presence or non-presence of a NULL value, then specifically test for this.
It has the added advantage of explicitly showing what your query is trying to achieve. This is always a benefit over implied intended use.
-
Thanx
Was hoping to avoid any OR's in the scripts. I was under the impression that
WHERE field1 <> '0'
would have been enough to pull back all records where the value isn't 0.
-
It might help to look at this from a different point of view. What if it was critical to NOT return records where the value MIGHT be zero.
The fact that it is null does NOT exclude the fact that is might be zero. It just could be that the data has not yet be entered.
For example: Give this medication to all patients WHERE blood_type <> "B". If that kind of query returned patients where the blood type = null (test result not yet entered) .... well I guess you can see the problem.
ron
-
Thanx Ron
Of course, if that was your situation, then you would have to program for that. My situation is different...more a data cleansing issue and NULLs need to be cleansed and I guess that I have to program for that too (within the limits/design of SQL Server). My code will now be less readable, less logical and less efficient to run.
My opinion only but I would expect a boolean predicate to return true or false based on the argument. WHERE field1 <> '0' should return all records where field1 values are not = 0 including NULLs. A NULL is a NULL is a could be anything (but it is not a 0). I seem to be wrong in my expectations from SQL Server. At least I know what I have to do now.
Any thoughts from the site experts would be welcome.
Thanx again
-
Perhaps it has something to do with NULLs being indeterminate. You state that
A NULL is a NULL is a could be anything (but it is not a 0)
But, if NULL is indeterminate, then it could be a 0. As SQL cannot read minds, it must therefore leave out of any equation anything that it cannot determine.
Maybe it is the way that we interperate what indeterminate means and how that should behave.
Sorry, don't mean to be a devils advocate but I just see it slightly differently and can't actually see anything wrong with SQLs behaviour here.
-
To backup what Steve says....
NULL basically means "I don't know what this is".
So, when you compare a NULL to 0....(WHERE NULL 0), SQL Server says 'I don't know if it does or doesn't equal 0. I don't know what NULL really is'.
-SQLBill
-
Sorry. NULL has no value at all...not even '0'. The only information that can be taken from a NULL (on it's own) is that no value has been entered for that record for that field. It doesn't even mean the value might be '0'. On some systems it might mean a value is expected to be entered but (from my experience only) on large migrated data volumes it is not likely.
If, by design, that field is allowed no entry (producing a NULL), in my case, I want to cleanse that field to indicate that no entry has been made, perhaps for migration to validated, cleansed, normalised tables that do not allow NULLs in their corresponding field. We all have to work with the systems that we inherit I suppose, unless of course we're lucky enough to start from scratch.
It is the boolean predicate that is not behaving as I expected i.e. return TRUE or FALSE depending on the argument. If the clause is:
WHERE field1 <> '0'
then I had expected it to return all records where that field is not '0' including NULLs. My expectations were wrong
I am disappointed that I have to use (what I see as) extra code to deal with NULLs.Hope this makes sense. Thanx for all your input guys.
-
No. The boolean predicate is working as expected. However when dealing with NULLs you face the Three-valued logic of NULL. Anything compared with NULL yields NULL (or UNKNOWN, for that matter).
So, your WHERE clause reads
WHERE NULL <> '0'
=> WHERE UNKNOWN <> '0'
=> UNKNOWN
and those rows are not returned.
Funny thing, isn't it?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I think the subtle difference in understanding lies in the whether NULL represents 'no value entered' or 'unknown'.
Obviously, SQL is behaving as though the value is 'unknown', therefore it excludes those rows because it cannot positively determine that there is a match (in this case a negatvie match).
If it was expected that NULL represents 'no value entered', then it should return rows where the value is NULL for the expression in the where clause.
However, I have never understood NULL to work like this. Not even in other relational DBs I have worked with. It always behaves as if NULL is 'unknown'
-
"...However, I have never understood NULL to work like this. Not even in other relational DBs I have worked with. It always behaves as if NULL is 'unknown'..."
Yes! I could not agree more. This was one of the very first things I was taught when becoming database literate.
I think once you accept that when dealing with databases null is always considered to be an "unknown" value your frustration level will be eased at least a little.
ron
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply