Understanding the difference between IS NULL and = NULL
-
I just ran into this yesterday, and could not get a count for Null values if I was using the field with the Nulls in it for the count(), regardless of the ANSI Nulls setting. If I used a different field, such as the ID field, in the count(), I did get the count correctly.
The query:
Select Count(PremiumGroup), PremiumGroup From RetireePremiums
Group by PremiumGroup
Order by PremiumGroup
The results:
0 NULL
27 A
94 B
124 C
32 D
345 E
193 F
16 G
195 New LA
7 Special
There are actually over 2000 records with a Null in that field.
But in any case, Null can be undetermined value, value not assigned, absence of value, etc. but in reality it is the same thing, no value, and should be recognized as its own special value to make logic easier when programming.
I am unable to imagine how making Nulls easier to deal with could make life more difficult for any of us, or our logic.
Thanks,
Chris
-
Thanks Antares. I never realised that there was so much confusion about NULL's. You have clearly highlighted a big problem area.
-
hi!!, i was playing around with my QA and tryin' to find the perfect query to avoid this NULL issues. Of course, i still haven't found it.
I think this is like... try to explain what means nothing (in Real Life
). SET ANSI_NULLS [Off | on]
DECLARE @val1 int
DECLARE @val2 int
If (isnull(@val1,null) = isnull(@val2,null))
print 'True'
else
print 'False'
SET @val1 = NULL
SET @val2 = NULL
If (isnull(@val1,null) = isnull(@val2,null))
print 'True'
else
print 'False'
Well i know this could not happen in real cases, but is curious how the result of this is always False, why??, i'm not expert in SQL Server but.. for SET ANSI_NULLS Off .. the result shouldn't be TRUE???
can anybody explain this??..
thanks!!
-
Familiarize yourself with the SQL function isnull().
When comparing a field which may have a null value, instead of = CustomerID
use = isnull(CustomerID, '')
which means: If the customerid is null, treat it as '', an empty string.
Good Luck
-
FYI sql like 'customerid = ' & sCustomerID
Can be 'isnull(customerid) = isnull(' & sCustomerID & ')'
or it can be '(customerid is null and ' & sCustomerID & ' is null) or customerid = ' & sCustomerID
-
Another place to watch out for
... WHERE field NOT IN (SELECT field2 FROM tbl)
If field2 ever has a null value, this will always result in no selections. It's funny, if that is never true you would think 'IN' would allways be true, but that one just compares the values.
-
The article is *wrong* on a few major points. I will submit a full article detailing the problems and supplying the correct information this weekend.
-
SQL uses Three-Valued Logic; it is not tied to the Two-Valued Logic you have forced on it in this article. Your comparison of @val = NULL does not result in FALSE. It results in UNKNOWN. You are being misled by the fact that you are printing 'FALSE' to the screen after the initial comparison. This is how IF works in SQL:
IF (a = b)
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
In the example, if a equals b is True, then 'TRUE' is printed on the screen; otherwise, 'FALSE' is printed on the screen. For two-valued logic (i.e., C++, VB, etc. logic) the result can be only True or False; so using ELSE as a catch-all for anything other than True does not present a problem. However, in three-valued logic this presents a problem which can be demonstrated here:
IF (a = b)
PRINT 'TRUE'
ELSE IF NOT(a = b)
PRINT 'FALSE'
ELSE
PRINT 'UNKNOWN'
In this case, if a or b is NULL, the result of comparison is neither True nor False; it is Unknown. The example above will print UNKNOWN if a = b results in Unknown. Three-valued logic requires three comparisons to determine the exact result of the expression as you can see from the above.
-
"Select Count(Field1) Where Field1 Is Null" should return the number of records that have a Null value for that field, regardless ofthe ANSI Nulls setting.
The problem is with the ANSI definition of COUNT() and other aggregate functions. COUNT(column_name) by definition counts all the rows that match your WHERE clause, and then eliminates all NULLs. COUNT(*), on the other hand, does not eliminate NULLs.
Try this:
SELECT COUNT(*) WHERE Column1 IS NULL
-
"Select Count(Field1) Where Field1 Is Null" DOES return the number of records that have a Null value for that field, regardless ofthe ANSI Nulls setting.
No, it does not. SELECT COUNT(column1) eliminates NULL values, per the ANSI-92 definition. SELECT COUNT(*) does not.
-
When 'ON', both tests return 'False'. When 'OFF', both tests return 'True'.
This is the case for both SQL 7 and 2000. Try it for yourselves!
With ANSI_SQL ON, both return Unknown, not False. Try it for yourself:
SET ANSI_NULLS [ON|OFF]
DECLARE @val CHAR(4)
IF @val = NULL
print 'True'
ELSE IF NOT(@val = NULL)
print 'False'
ELSE
print 'Unknown'
SET @val = NULL
If @val = NULL
print 'True'
ELSE IF NOT(@val = NULL)
print 'False'
ELSE
print 'Unknown'
-
As pointed out previously, COUNT(column1) eliminates NULLs from the final result set, per the ANSI SQL-92 standard. COUNT(*) does not. Try this instead:
Select Count(*), PremiumGroup From RetireePremiums
Group by PremiumGroup
Order by PremiumGroup
-
"COUNT(*), on the other hand, does not eliminate NULLs."
Actually, COUNT(val) will always eliminate nulls, but * includes all fields in the table and by definition is not null and will not be eliminated from the count.
I also noticed I said
Can be 'isnull(customerid) = isnull(' & sCustomerID & ')'
Should have said
Can be 'isnull(customerid,0) = isnull(' & sCustomerID & ',0)'
-
Per the ANSI-92 standard:
1. COUNT(*) returns the cardinality of a table.
2. COUNT(column) applies the value expression to all rows of the table, then eliminates all rows where column is NULL.
SQL Uses Three-valued logic, as describes in more detail here: http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls.asp
-
I am seeing something strange. When I do a count(*) from table where column IS NULL, my result is every row in the table. This is a 46 million row table and I know that not every row contains a null in the the specific column that I am testing for NULL. I know that there are about 2 million that do. If I do a select top 3000000 * into #temp where column is NULL, I get my 2 million records inserted into the temp table. But why will count(*) not show me a count of 2 million? Does SQL Server have problem with large tables in this regard? Thanks!
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply