I’ve used this trick several times when needing to sort data into groups. For example say you need to sort car classified listings by price. Some of the dealers enter a price of “0” for their listings. When sorting by lowest price, you want the listings that actually have a low price to come first, rather than than the cars costing $0 (hey, I’d like to buy your free car, thank you).

SELECT year, make, model, price,
tmpSortPriceField =
    CASE price
        WHEN 0 THEN 2
        ELSE 1
    END
from cars ORDER BY tmpSortPriceField, price

But I ran into a problem when some of the records had a NULL price. They were getting a tmpSortPriceField value of 1, because they hit the “ELSE 1” part, thus were getting sorted up front. So I added a line in the CASE statement for the NULL values:

SELECT year, make, model, price,
tmpSortPriceField =
    CASE price
        WHEN 0 THEN 2
        WHEN NULL THEN 2
        ELSE 1
    END
from cars ORDER BY tmpSortPriceField, price

But that didn’t work, the records with a null price were still getting a tmpSortPriceField value of 1. I tried using IS NULL and that help either. I don’t know why but my null comparison just wasn’t working. I finally solved it by changing from the simple case form to the searched case form, where each entry in the case statement is compared for a boolean condition.

SELECT year, make, model, price,
tmpSortPriceField =
    CASE
        WHEN price = 0 THEN 2
        WHEN price IS NULL THEN 2
        ELSE 1
    END
from cars ORDER BY tmpSortPriceField, price

I don’t know exactly why this works better, but it does seem to handle the NULLs appropriately. I’ve also heard from a nearby SQL guru that he always gets better results with the searched case statement, too.

Update – Mark Kruger suggested using IsNull may work as well. I tested it, and it does work, but only in SQL Server. So far the code above will work in MSSQL or MySQL. Using IsNull() will break in MySQL because the IsNull() function in MySQL is different than the one in MSSQL. But here is how you would do it using IsNull() in SQL Server:

SELECT year, make, model, price,
tmpSortPriceField =
    CASE IsNull(price,0)
        WHEN 0 THEN 2
        ELSE 1
    END
from cars ORDER BY tmpSortPriceField, price

Update 2 – BUT, I believe using COALESCE() would work in either MySQL or MSSQL:

SELECT year, make, model, price,
tmpSortPriceField =
    CASE COALESCE(price,0)
        WHEN 0 THEN 2
        ELSE 1
    END
from cars ORDER BY tmpSortPriceField, price

16 Comments

  1. Dan G. Switzer, II says:

    The reason the first example doesn’t work is because NULL is never equal (=) to another value–even NULL = NULL will return false.

    So you in your first example/attempt the code is essentially saying “WHERE price = NULL”, which will always fail. The reason the second example works is because you’re now using the correct expression for testing to see if a value is NULL–which is using the IS operator.

    Obviously COALESCE works because if the value *is* NULL, it’s changing the value to 0.

  2. Ciro Theodoro says:

    Very usefull. I was facing the same problem when I found this article. Thanks for that 😉

  3. Sanjay says:

    Good piece of information…..I had a confusion in writing a CASE statement with NULLS and this article solved that, Thanks

  4. Jason says:

    Very useful to me as well. I’m glad that I was able to stumble upon this.

  5. Mmark says:

    One more thnak you. It was a big helping.

  6. Nayha says:

    very helpful. Thanks.

  7. Patel Pinakin says:

    was just forgot the syntax,
    thanx
    u helped me recall…

    keep posting…

  8. Priya says:

    I have a similar issue. I do not wanna display the NULL records hitting the
    else part.For eg .I tried out methods like

    Select NAME, EMPID,
    case
    when ( LOCATION like 'chennai' or LOCATION like 'coimbatore')
    then 'Tamil Nadu'
    when ( LOCATION like 'cochin' or LOCATION like 'Trivandrum')
    then 'Kerala'
    else
    end STATE
    from biodata where EMPID in ( select EMPID from biodata)
    and STATE is not null;

    But iam getting displayed with null STATE records. Please help.

  9. Priya says:

    The output would be like,

    NAME    EMPID    STATE
    Tom    12546    Tamil Nadu
    Amy    14645    Kerala
    Katie    24645    
    Ravesh    54654    

    I need only not null state records.Please do the needful. Iam facing the issue in my project

  10. stelban says:

    Nice trick using nulls with case statement.  Very helpful!  Thanks

  11. kumar says:

    use ISNULL(columnName,<What ever you want if the value is NULL>)

    Hope this helps…

  12. Priya says:

    Hi Stelban and Kumar,
    @ Kumar : Actually, I was in need of not null records.

    I have resolved the issue in a simple way by just putting my query in a new select statement.

    select * from (Select NAME, EMPID,
    case
    when ( LOCATION like 'chennai' or LOCATION like 'coimbatore')
    then 'Tamil Nadu'
    when ( LOCATION like 'cochin' or LOCATION like 'Trivandrum')
    then 'Kerala'
    else
    end STATE
    from biodata where EMPID in ( select EMPID from biodata))
    where STATE is not null;

    This gives the o/p
    NAME    EMPID    STATE
    Tom    12546    Tamil Nadu
    Amy    14645    Kerala
    rather than

    The output would be like,

    NAME    EMPID    STATE
    Tom    12546    Tamil Nadu
    Amy    14645    Kerala
    Katie    24645    
    Ravesh    54654

    Hope this helps !!

  13. Usman says:

    Thanks Buddy. Helped me to get those NULL variables to accept values! My blog on that here:
    http://usman-suglatwala.blogspot.com/2011/11/handling-nulls-in-sql-while-inserting.html

  14. Phil says:

    Thanks for this, I was having trouble with NULL values but the ‘search’ method of the CASE statement works just fine.

  15. James says:

    The problem with sub selects is that they tend to slow everything down when working with large data sets. I am currently having trouble with this where the nulls will be sorted out properly if I put the statement in the where clause but I cannot change the value via a case statement in the select clause using all the methods mentioned above and then some.

  16. Ici says:

    Many thanks

Leave a Reply

You must be logged in to post a comment.