Understanding the difference between IS NULL and = NULL

  • Antares686

    SSC Guru

    Points: 125444

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp

  • sunshinekid

    Old Hand

    Points: 324

    I realised the topic specifically covered variables, but some of these aspects also apply more widely to any expression involving null.

    Under sql92 any expression involving null directly evaluates to unknown, rather than true/false, hence the issues with relational operators.

    Operators like IS NULL explicitly convert back to 2-valued logic so you can do some sensible boolean algebra.

    Note that different versions of t-sql have used different implementations of = (and possibly other operators) when used with NULL.

    In early versions of sqlserver (thosed based on Sybase, up to 6.0 I think), selecting values from a table using = NULL used the ANSI meaning and never matched any records. This has since changed and with ansi nulls off you will get 'matching' records.

  • Antares686

    SSC Guru

    Points: 125444

    Hey Sunshine

    I looked all over and did find all the details I wanted, so I appreciate that additional information. I wrote the article as I keep seeing this issue show up in questions on the forums. I based my information of course on a basic understanding of C++ and the way the variables react in the situations I could directly affect. However, the information you add here, can you point me to where you found this.

  • Simon Sabin

    SSCrazy Eights

    Points: 8142

    The other aspect to be aware is that NULL + 'hello world' = NULL in SQL 7 on wards without ansi nulls off

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • don1941

    SSCarpal Tunnel

    Points: 4183

    This was my first hard lesson learned in SQL. I've never forgotten it these past 8-9 years. I think every programming standards guide ought to mention this (as a running footer on every printed page!) because it's the #1 question i still get on every project.

  • sunshinekid

    Old Hand

    Points: 324

    Anyone interested in digging further should check out BOL.

    Its simplest to look for 'NULL Comparison Search Conditions' in the index & follow the link to 'Null Values' at the bottom of the article (its part of the expression syntax).

    Null behaviour is documented quite well in these sections, although previously my info has mostly been gleaned from empirical research & helping those with broken code.

    This area is a classic example of where changing a server default can break your code quite horribly:

    BP - always use 'set ansi nulls on' for each session

    WP1 - forget to check the server setting

    WP2 - assume behaviour is the same across versions

  • alexd1980

    Valued Member

    Points: 72

    Strange:

    I issue this

    DECLARE @val CHAR(4)

    If @val = NULL

    select 'Yup1'

    else

    select 'Nop1'

    SET @val = NULL

    If @val = NULL

    select 'Yup2'

    else

    select 'Nop2'

    there is a result:

    ----

    Nop1

    (1 row(s) affected)

    ----

    Nop2

    (1 row(s) affected)

    ?

  • sunshinekid

    Old Hand

    Points: 324

    alexd1980, you probably have ansi nulls turned on.

    In this case NULL=NULL always evaluates to unknown, so your if expression evaluates the ELSE branch.

    This is why it doesn't matter what the value of your variable is.

  • JayTKay

    SSC Veteran

    Points: 257

    Too lazy to remember the rules about nulls and ANSI NULLS and empty strings, so I avoid tripping over them.

    Typically, I use comparisons like this:

    isnull(@variable,'') = ''

    isnull(@variable,0) = 0

    coalesce(@variable,@anothervariable,'') = ''

  • zhudawei

    Old Hand

    Points: 310

    It is very hard to remember the rules. Of course we can always use 'is null' in sql however the problem is in asp code sometime you have to make up the sql like 'customerid = ' & sCustomerID. In this case, it's also too much work to change '=' to 'is' when sCustomerID is NULL

  • David le Quesne

    SSCertifiable

    Points: 5229

    The Borland Delphi application I administer uses a SQL Server 2000 back end with ANSI NULLS set to ON. If I remove a date from a field in the application, it doesnt return to NULL, it returns to a non-NULL value which is not displayed, so I have to explicitly set the value to NULL if the user removes the date otherwise my IS NULL comparisons will not work

    David le Quesne

    If it ain't broke, don't fix it...

  • Kelvin Lush

    SSCrazy

    Points: 2572

    I agree with axeld1980.

    The statements made about setting a value to null rather than just not setting it are incorrect. It makes absolutely no difference whether the value is assigned to null or not.

    The results you get are wholly dependent on the value for ANSI_NULLS. With this set to ON, nothing will ever '= NULL', not even if it is set explicitly. With the setting OFF, both '= NULL' and 'IS NULL' behave in the same way. So:


    SET ANSI_NULLS [ON|OFF]

    DECLARE @val CHAR(4)

    If @val = NULL

        print 'True'

    else

        print 'False'

    SET @val = NULL

     

    If @val = NULL

        print 'True'

    else

        print 'False'


    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!

  • Chris Stamey

    SSCrazy

    Points: 2635

    WHY CAN'T NULL = NULL?????????????

    That has always been my biggest problem in dealing with Nulls. Null does equal Null in the real world. If Null = the absence of a value, and you have two instances of the absence of a value, they are the same, Null.

    If the field value is Null, the absence of a value in that field, IT EQUALS NULL!!!

    I just don't get why it has to be so complicated. Humans write the software and we can make it evaluate Nulls any way we want to, so why can't it just be simple, Null = Null, "Is Null" should be the same thing as "= Null", but not the same thing as "= 'Null'".

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

    I think we could all get along just fine knowing that Null is a special value (or lack of) that is different than 'Null' (string of letters) but Null = Null.

    Chris

  • Kelvin Lush

    SSCrazy

    Points: 2572

    WHY CAN'T NULL = NULL?????????????


    Because NULL in SQL 92 is taken to mean Unknown Value, not NO Value. If the value is unknown, how can you say that it equals anything?

    That is why any test for '= NULL' will fail.

    Also, re your comment about using 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.

  • Antares686

    SSC Guru

    Points: 125444

    I see this. When I tested wrote and tested this I am fairly sure I used QA (not sure if was 7 or 2000 thou) and against multiple 7 instances but it would have been pre SP4 on SQL 7. If I get a chance I will probe into what changed the effect on SQL 7 and when. But at the time of the articles write up that was the effect you would get. Just one additional reason IS NULL is a better choice because if there was a fundamental chage in evaluation then your expected results may not be what you got after whatever changed this. It'll probably be a month before I can test thou as I am in the middle of a big project.

Viewing 15 posts - 1 through 15 (of 41 total)

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