Comparing with NULL

  • Thomas Franz

    Hall of Fame

    Points: 3565

    Comments posted to this topic are about the item Comparing with NULL

    God is real, unless declared integer.

  • Iulian -207023

    SSCertifiable

    Points: 7508

    Nice one

    it's null's fault 🙂

  • Rune Bivrin

    SSCertifiable

    Points: 7589

    Nice question, but the correct answer is as usual: "It depends". In this case on the setting of ANSI_NULLS.


    Just because you're right doesn't mean everybody else is wrong.

  • Iulian -207023

    SSCertifiable

    Points: 7508

    Rune Bivrin (9/26/2016)


    Nice question, but the correct answer is as usual: "It depends". In this case on the setting of ANSI_NULLS.

    Good one! SET ANSI_NULLS ON / OFF; makes a difference

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286958

    A simple question to highlight an important concept. Thanks.

  • Kaye Cahs

    SSCarpal Tunnel

    Points: 4135

    One of my reoccurring frustrations is that very few people understand that NULL is not a value. It is the absence of a value.

    It doesn't really seem that hard, does it?

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Stewart "Arturius" Campbell (9/26/2016)


    Nice question, thanks Thomas.

    The ANSI NULLS setting, will, of course, impact the final outputs.

    Didn't know that. Makes sense.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Should be something like "is null" for the compare. Another gotcha would be to do something like CASE X WHEN NULL, which fails.

  • Revenant

    SSC-Forever

    Points: 42467

    Very witty. Thanks, Thomas!

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716274

    Updated the question to say ANSI_NULLS set to ON.

    Note, at some point, this will be the only setting allowed.

  • sknox

    SSChampion

    Points: 12231

    Steve Jones - SSC Editor (9/26/2016)


    Updated the question to say ANSI_NULLS set to ON.

    Note, at some point, this will be the only setting allowed.

    Thanks, Steve! While I think it's fair* to assume deprecated features are not used unless explicitly stated, it's always best if the question is clear.

    For reference on deprecated features: https://msdn.microsoft.com/en-us/library/ms143729.aspx

    (Personally, I'm a little disappointed that SET ANSI_NULLS OFF, SET ANSI_PADDING OFF, and SET CONCAT_NULL_YIELDS_NULL OFF are still supported in SQL 2016, let alone the next version.)

    *Fair in terms of a QotD - in real-world work, always check!

  • webrunner

    One Orange Chip

    Points: 29948

    Thanks, great question.

    - webrunner

    P.S. I see the following code returns false, true.

    SELECT CASE

    WHEN (9999 IS NULL) THEN

    'true'

    ELSE

    'false'

    END,

    CASE

    WHEN NOT (9999 IS NULL) THEN

    'true'

    ELSE

    'false'

    END;

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Easy one, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 14 posts - 1 through 14 (of 14 total)

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