SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding the difference between IS NULL and = NULL


Understanding the difference between IS NULL and = NULL

Author
Message
Kenneth Lee
Kenneth Lee
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 1

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.


Mike C
Mike C
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6577 Visits: 1172
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.
Mike C
Mike C
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6577 Visits: 1172

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.


Mike C
Mike C
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6577 Visits: 1172

"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


Mike C
Mike C
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6577 Visits: 1172

"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.


Mike C
Mike C
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6577 Visits: 1172

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'


Mike C
Mike C
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6577 Visits: 1172

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


Kenneth Lee
Kenneth Lee
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 1

"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)'


Mike C
Mike C
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6577 Visits: 1172

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


Stephen Baez
Stephen Baez
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search