SQL: WHEN CASE NULL Fails

Imagine you want to find all the null values in a column in a database table (SQL Server).

x
1
2
NULL
4
5

Here is the SQL that performs the task as required:

[sourcecode language=”sql”]
SELECT x,
CASE x
WHEN NULL THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]

The result he expected was:

x result
1 no
2 no
NULL yes
4 no
5 no

But that isn’t what he got. His result was like this:

x result
1 no
2 no
NULL no
4 no
5 no

Curiously, if you run this:

[sourcecode language=”sql”]
SELECT x,
CASE x
WHEN 1 THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]

You do get this:

x result
1 yes
2 no
NULL no
4 no
5 no

So, why didn’t the original work?

  • Because NULL means unknown
  • Because NULL does not equal NULL
  • Because NULL is just weird, weird, weird

Anyway, here is the SQL that gives the expected answer:
[sourcecode language=”sql”]
SELECT x,
CASE
WHEN x IS NULL THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]

Be Sociable, Share!
This entry was posted in SQL Server. Bookmark the permalink.

32 Responses to SQL: WHEN CASE NULL Fails

  1. Chris H says:

    Thanks, you helped me with a curly problem I was having a hard time figuring out:

    CASE WHEN JO.CandidateNo IS NULL THEN JO.JobOrderNo ELSE JO.CandidateNo END

    I have since figured out that the COALESCE statement will do a better job, but thanks anyway.

    COALESCE(JO.CandidateNo, JO.JobOrderNo)

  2. Rodney Yee says:

    Thank you! Your solution worked immediately for me! I tried other sites and their syntax kept failing for me. Your solution and syntax was perfect. You saved me a whole lot of time and grief. Thank you…. Thank you.

  3. kramii says:

    Chris / Rodney:

    I’m glad this helped.

  4. The Really fun part is that the top site returned in google for search “SQL SERVER Case when null” is this site:
    http://www.java2s.com/Code/SQLServer/Select-Query/CasewhenNULL.htm

    Their example is your example of what returns unexpected results. Thanks for having the correct info. I miss MySQL.

  5. kramii says:

    invertedspear:

    Thanks for the encouragement.

  6. Sumaira Butt says:

    just Use ISNULL(Columnname,condition)
    ISNull(JO.CandidiateNo,’NotAvailable’)

  7. kramii says:

    Sumaria,

    That’s a good point. What I really wanted to point out is that the CASE x WHEN NULL… example doesn’t work properly. But, your way is rather elegant when compared to my last example.

  8. Lukos says:

    Sumaira’s solution only works if you want the column value OR an alternative. In my case, I do not want the column value only a 1 or 0 depending on the value being null or not and only the case works there!!

  9. Ryan says:

    Great info!

    Here’s how I addressed it, using what you said above:

    SELECT x,

    CASE isnull(x, ‘yes’)

    WHEN ‘yes’ THEN ‘yes’

    ELSE ‘no’

    END AS result

    FROM
    someTable

    So basically using isnull() to set a default value, one which the case statement will like. This allowed me to keep my lengthy case statement easy to read without restating the “X” in the WHEN on each line.

  10. SQLnoob says:

    Thanks for this one. Its really rare to have a sql problem explained as clearly as you have 🙂

  11. If you ever want to hear a reader’s feedback 🙂 , I rate this article for 4/5. Detailed info, but I have to go to that damn yahoo to find the missed bits. Thank you, anyway!

  12. David says:

    innull() seems not work with oracle

  13. Lisa says:

    Thanks! This was driving me batty. You had exactly the answer I needed and it solved my problem.

  14. Zuhaib Alam says:

    You have no idea how much this helped, sitting here at work late trying to wrap up a project. Those darn NULLs. Thanks so much!

  15. AJ says:

    Thank You so much!
    Your solution saved me alot of time.

  16. Screff says:

    You rock! Every time I forget how to do this, I just come back to your site. Thanks!

  17. adobepro says:

    Thanks, very helpful!

  18. Borjus says:

    It’s very simple:
    SELECT x
    CASE x is null
    WHEN true THEN (‘yes’)
    ELSE ‘no’
    END AS result;

  19. ClaudioRN says:

    If you write
    CASE x WHEN null
    is the same you write: x = null.

    SQL has only IS NULL to compare x with null and you cannot use the “=”.

    If you write
    CASE WHEN x is null
    you are using the correct SQL syntax for null compare.

    I’m sorry for my very bad english.
    Claudio.

  20. reshma says:

    Hi,

    This example code really helped. Thank you.
    Reshma

  21. Erick says:

    Thanks, that helped me 🙂

  22. Allain says:

    Thanks a lot, now i can get my line to work

  23. Charice says:

    Thanks! That helped!

  24. George says:

    This was very helpful, thanks 🙂

  25. kennydust says:

    helped me alot. thanks!

  26. Adam says:

    CASE WHEN x IS NULL THEN ‘YES’
    ELSE ‘NO’
    END.

  27. Smallpawn says:

    Thank you! I was having a hard time with this… But you solved it!

  28. Aaron says:

    2.5 years later and this is still helping people. Thanks!

  29. Pingback: SQL: WHEN CASE NULL fails. (via All Wrong) « Alex Khvatov Blog

  30. John M says:

    Thanks for this. Even though I ended up solving my problem a different way this will definitely save me headaches in the future. I’ve run into NULL problems in CASE statements more than once.

  31. Andre says:

    Thanks, worked perfectly

  32. Christian says:

    This helped me solve my problem, thank you

Leave a Reply

Your email address will not be published. Required fields are marked *