Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Understanding the difference between IS NULL and = NULL Expand / Collapse
Author
Message
Posted Friday, June 3, 2005 4:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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 3, 2005 6:50 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
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 3, 2005 10:39 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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 3, 2005 10:51 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

"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 3, 2005 10:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

"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 3, 2005 10:57 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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 3, 2005 10:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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

Post #187628
Posted Monday, June 6, 2005 6:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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 said

Can be 'isnull(customerid,0) = isnull(' & sCustomerID & ',0)'

Post #188038
Posted Monday, June 6, 2005 7:41 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse