Understanding the difference between IS NULL and = NULL

  • Chris Stamey

    SSCrazy

    Points: 2635

    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

  • Kelvin Lush

    SSCrazy

    Points: 2572

    Thanks Antares. I never realised that there was so much confusion about NULL's. You have clearly highlighted a big problem area.

  • Victor Virrueta

    SSC Veteran

    Points: 278

    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!!

     

  • sara karasik

    SSCommitted

    Points: 1563

    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

     

  • Kenneth Lee

    Ten Centuries

    Points: 1273

    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

     

  • Kenneth Lee

    Ten Centuries

    Points: 1273

    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

    SSC-Insane

    Points: 23224

    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

    SSC-Insane

    Points: 23224

    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

    SSC-Insane

    Points: 23224

    "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

    SSC-Insane

    Points: 23224

    "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

    SSC-Insane

    Points: 23224

    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

    SSC-Insane

    Points: 23224

    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

    Ten Centuries

    Points: 1273

    "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

    SSC-Insane

    Points: 23224

    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

    Newbie

    Points: 4

    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 41 total)

You must be logged in to reply to this topic. Login to reply