maybe dumb, but it doesn''t work like I thought...
-
I have a query with the statement "WHERE field <> 'O'". I thought that NULLs <> 'O', but the results drop out nulls, effectively returning "WHERE field <> 'O' AND field IS NOT NULL".
Why? Can I change that behavior?
-
where ISNULL(field, '') <> 'O'
-
This behaviour is defined by ANSI standard.
You need to read a little about NULLs.
NULL means "Don't know".
If the value is NULL then it may be 'O', but it may be = 'O'. You just don't know.
Use NULLs appropriately, otherwise you'll get into troubles endlessly.
_____________
Code for TallyGenerator -
Sergiy is almost correct. Null does not equal 0 and Null does not not equal 0. That's why we use "Is Null" and "Is Not Null" instead of "= Null" and "<> Null". And if that doesn't blow your mind enough, then consider this, Null does not equal Null nor does Null not not equal Null.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Wouldn't it be less confusing just to say that NULL means 'Unknown'? (or 'unquantifiable', if you are a mathematician). Then the results of expressions seem perfectly reasonable. The only behaviour of SQL Server that I find odd is when rows containing NULLSs are removed from aggregations such as SUM(). After all, the sum of a column containing a NULL ought to be NULL. If you add 2 tins of beer to an unknown number of tins of beer, you get an unknown quantity, not 2.
Best wishes,
Phil Factor -
Though, on the other hand, it's quite practical many times that aggregates skips nulls the way they do.
Once you learn the behaviour, it's usable.
And, if nulls are excluded, there is a message that informs of it.
(unless it's switched off, making SET yet again an important command to remember)
/Kenneth
-
You are correct, Phil. That would be less confusing.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Phil, Robert -
If I understand Robert's comment, then the opposite must be true, too. There may be no beer at all and so your sum is 2. I believe that the reason it returns the sum without nulls is it's returning only what is known.
-
As long as there is at least *some* beer, then we're OK.
-
The problem with your original expression is that SQL92 and ANSII standard specifies that the result of a logical expression with a NULL in it is NULL. This is because the result of an expression with an 'Unquantifiable' or unknown in it is always unquantifiable. This is always interpreted as 'not true'. Your expression was returning TRUE when the field='0', FALSE when the field wasn't, and NULL when the field was NULL. You can test this...
SELECT 'This is '+CASE WHEN '0'='0' THEN '' ELSE ' not ' END +'a match' --This is a match SELECT 'This is '+CASE WHEN '0'=NULL THEN '' ELSE ' not ' END +'a match' --This is not a match SELECT 'This is '+CASE WHEN NULL=NULL THEN '' ELSE ' not ' END +'a match' --This is not a match SELECT 'This is '+CASE WHEN NOT(NULL=NULL) THEN '' ELSE ' not ' END +'a match' --This is not a match
It certainly seems odd that NULL=NULL and NOT(NULL=NULL)have the same result in an IF or WHEN expression.
I suspect that beer drinkers would like an extension to SQL that allows us to say that, if one adds two cans of beer to an unquantifiable number of cans, one has at least two cans. That would seem fairer. This seems, already, to be the logic underlying the aggregation method used by SQL Server.
Best wishes,
Phil Factor -
Unless the unknown is a negative number of beer cans.
It could happen!
I heard it from a guy who heard it from a guy...
-
It doesn't seem so wierd if you use it correctly.
SELECT
'This is '+CASE WHEN Null Is Null THEN '' ELSE ' not ' END +'a match'
--This is a match
SELECT
'This is '+CASE WHEN Not(Null Is Null) THEN '' ELSE ' not ' END +'a match'
--This is not a match
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Quite right!, IS NULL will always return either true or false, but I was trying to illustrate how a logical expression involving a NULL will return a NULL rather than TRUE or FALSE, rather than showing how to test for NULL
I'm sorry if I didn't make that clear
Best wishes,
Phil Factor -
I was trying to illustrate how a logical expression involving a NULL will return a NULL rather than TRUE or FALSE
This is not always true.
SET ANSI_NULLS OFF
SELECT 'This is '+CASE WHEN NULL=NULL THEN '' ELSE ' not ' END +'a match'
--This is a match
SELECT 'This is '+CASE WHEN NOT(NULL=NULL) THEN '' ELSE ' not ' END +'a match'
--This is not a match
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply