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:

SELECT x,

CASE x

WHEN NULL THEN ‘yes’

ELSE ‘no’

END AS result

FROM

someTable

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:

SELECT x,

CASE x

WHEN 1 THEN ‘yes’

ELSE ‘no’

END AS result

FROM

someTable

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: 

SELECT x,

CASE

WHEN x IS NULL THEN ‘yes’

ELSE ‘no’

END AS result

FROM

someTable

Advertisements

67 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. […] SQL: WHEN CASE NULL fails. (via All Wrong) February 13, 2011 khvatov Leave a comment Go to comments 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: SELECT x, CASE x WHEN NULL THEN 'yes' ELSE 'no' END AS result FROM someTable 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: SELECT x, CASE x WHEN 1 THEN ' … Read More […]

  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

  33. Mike says:

    thanks for your postings especually the COALESCE command

  34. asp.net, c#,javascript…

    […]SQL: WHEN CASE NULL fails. « All Wrong[…]…

  35. Felipe Almeida says:

    Good work.
    Thanks a lot.
    It worked perfectly.

  36. Steve Hearne says:

    Thanks very much!

  37. Robert Denk says:

    Thanks… worked like a charm.

  38. ways to make hair grow faster…

    […]SQL: WHEN CASE NULL fails. « All Wrong[…]…

  39. Cesar says:

    Thanks a lot !!

  40. electric shaver,best electric shaver,electric shaver reviews…

    […]SQL: WHEN CASE NULL fails. « All Wrong[…]…

  41. (mk-tips|Mk-Tips)…

    […]SQL: WHEN CASE NULL fails. « All Wrong[…]…

  42. elder says:

    thank you very much, it worked like a charm.

  43. cms dle says:

    cms dle…

    […]SQL: WHEN CASE NULL fails. « All Wrong[…]…

  44. Gayung says:

    Nice, It works, good job :))

  45. Nulled Scripts – Download Free Full Nulled Scripts, SEO Tools, WordPress and Joomla Premium Templates, Premium Plugin, CMS Scripts….

    […]SQL: WHEN CASE NULL fails. « All Wrong[…]…

  46. Raul says:

    Say better SQL Server is Weird weird weird

  47. Paul Godfrey says:

    Thanks, really useful advice. Saved me a lot of looking.

  48. jee says:

    thanks. its helped me.

  49. D3v IT bendruominė…

    […]SQL: WHEN CASE NULL fails. « All Wrong[…]…

  50. Sandip says:

    Hi Guys, Thanks for the update.

    Is any body aware how to do it in DB2 ? it neither identifies ‘IS NULL’ nor it has ‘isNull’ function.

    What I am trying is:
    I need to select PHONE value from the table PS_MPL_PHON_DTL_VW for a PersonID, if a NULL value then return ‘N’ (as shown below)

    Select CASE WHEN PHONE IS NULL THEN ‘N’ ELSE PHONE END
    from PS_MPL_PHON_DTL_VW where PERSON_ID = ‘50381’;

    The outcome: it treats PHONE (when Null) as a stranger and just returns blank values (undesired)

    I tried with functions like COALESCE and VALUE but no success.

    your help will be highly appreciated.

    Thanks,
    Sandip

  51. kramii says:

    I’ve never used DB2, but you could try:

    SELECT IFNULL(PHONE , “N”) AS Phone
    FROM PS_MPL_PHON_DTL_VW where PERSON_ID = ’50381′;

    • Sandip says:

      Thanks guys for your help.
      I got the answer of my question. The view actually does not return any row ( hence, no null value) that means zero rows and it could Not replace with an appropriate value.

      Thanks appreciated.

  52. King Amit says:

    Thanks , It helped 🙂

  53. doubleM says:

    I have a situation where if the a stored procedure can be called with three values

    Below “@col_val” represents a SET statement in an UPDATE for a table. “@my_val” is the value being passed in as a param of the dbo.uspUpdateMyTable stored procedure. I’m using a SET here because it’s easier than creating a table – yada-yada-yada

    There are three possible states for param
    NULL means use the current column value
    0 means clear the current column value to 0
    1 or any value really which mean update the column with this value

    DECLARE
    @my_val INTEGER = 0
    ,@col_val INTEGER = 1
    ,@current_col_val = 1;

    SET @col_val =
    CASE WHEN @my_value IS NULL
    THEN @current_col_val
    ELSE
    CASE @my_value
    WHEN 0
    THEN 0
    ELSE
    @my_value
    END
    END;

    PRINT ‘Result is: ‘ + CAST(@col_val AS NVARCHAR (15));

    If I were doing a NVARCHAR column I would use ” rather than 0

    It’s another view to a simular situation

  54. Trixxter says:

    Finally thank you!!! After 15+ forums I had to read that says the same thing YOUR salution is the only one that made sense and worked
    THANK YOU!!!

  55. Mokwaja says:

    Thanks, this solved my problem as well

  56. David says:

    I needed to use similar in an update statement… but I only wanted to update IF it was null or an empty string ”. Here is what worked for me…

    update MyTable set
    Email = (case isnull(Email, ”) when ” then ‘AA’ else Email end)
    where ID = 1

    This allowed me to update only when x was null or ” and left anything else in place.

    Thank you.

  57. dalr says:

    Thanks. exact answer!!!!

  58. freak says:

    great thanks

  59. Goum says:

    Thanks a lot ! That’s what I was searching for !!

  60. Beatriz says:

    fantastic post, very informative. I wonder why the opposite specialists of
    this sector do not understand this. You must proceed your writing.
    I’m confident, you have a great readers’ base already!

  61. Thank you a bunch for sharing this with all people you actually realize
    what you’re speaking about! Bookmarked. Please additionally visit my website =). We will have a link change contract between us

  62. elgentilarte says:

    Super claro. Gracias por la ayuda Bro!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: