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, priceBut 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, priceBut 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, priceI 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, priceUpdate 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
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.
5 June 2008, 8:50 amCiro Theodoro says:
Very usefull. I was facing the same problem when I found this article. Thanks for that 😉
15 August 2008, 9:48 amSanjay says:
Good piece of information…..I had a confusion in writing a CASE statement with NULLS and this article solved that, Thanks
8 October 2008, 4:04 amJason says:
Very useful to me as well. I’m glad that I was able to stumble upon this.
9 December 2008, 12:46 pmMmark says:
One more thnak you. It was a big helping.
18 February 2009, 9:39 pmNayha says:
very helpful. Thanks.
15 September 2009, 5:18 amPatel Pinakin says:
was just forgot the syntax,
thanx
u helped me recall…
keep posting…
3 April 2010, 1:08 amPriya 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.
7 September 2010, 6:55 amPriya 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
7 September 2010, 7:51 amstelban says:
Nice trick using nulls with case statement. Very helpful! Thanks
29 October 2010, 11:46 amkumar says:
use ISNULL(columnName,<What ever you want if the value is NULL>)
Hope this helps…
22 December 2010, 9:52 amPriya 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 !!
31 January 2011, 6:11 amUsman says:
Thanks Buddy. Helped me to get those NULL variables to accept values! My blog on that here:
2 November 2011, 10:45 amhttp://usman-suglatwala.blogspot.com/2011/11/handling-nulls-in-sql-while-inserting.html
Phil says:
Thanks for this, I was having trouble with NULL values but the ‘search’ method of the CASE statement works just fine.
1 February 2012, 4:14 amJames 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.
29 April 2012, 8:21 pmIci says:
Many thanks
20 September 2012, 5:44 am