Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Understanding the difference between IS NULL and = NULL Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, June 03, 2005 4:43 PM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, August 18, 2005 4:21 PM Points: 75, 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.
Post #187600
 Posted Friday, June 03, 2005 6:50 PM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, November 12, 2013 4:13 PM Points: 1,276, Visits: 1,114
 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.
Post #187612
 Posted Friday, June 03, 2005 10:39 PM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, November 12, 2013 4:13 PM Points: 1,276, Visits: 1,114
 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.
Post #187624
 Posted Friday, June 03, 2005 10:51 PM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, November 12, 2013 4:13 PM Points: 1,276, Visits: 1,114
 "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
Post #187625
 Posted Friday, June 03, 2005 10:53 PM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, November 12, 2013 4:13 PM Points: 1,276, Visits: 1,114
 "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.
Post #187626
 Posted Friday, June 03, 2005 10:57 PM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, November 12, 2013 4:13 PM Points: 1,276, Visits: 1,114
 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'
Post #187627
 Posted Friday, June 03, 2005 10:59 PM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, November 12, 2013 4:13 PM Points: 1,276, Visits: 1,114
 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 RetireePremiumsGroup by PremiumGroupOrder by PremiumGroup
Post #187628
 Posted Monday, June 06, 2005 6:55 PM
 SSC Journeyman Group: General Forum Members Last Login: Thursday, August 18, 2005 4:21 PM Points: 75, 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 saidCan be 'isnull(customerid,0) = isnull(' & sCustomerID & ',0)'
Post #188038
 Posted Monday, June 06, 2005 7:41 PM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, November 12, 2013 4:13 PM Points: 1,276, Visits: 1,114
 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
Post #188042
 Posted Saturday, October 29, 2005 7:42 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, April 24, 2007 3:27 PM Points: 2, 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!
Post #233622

 Permissions