MySQL Sort Order with NULL

As a MySQL programmer, I often need to sort lists in alphabetical or numeric order, but many times the columns I use have null values. When ORDER BY col ASC is used, null vales appear at the top of the list; with DESC they appear at the bottom. Often, I would like a list to sort in alphabetical or numeric order, but with NULL at the bottom. Example: You have a table that stores user information, including nicknames. You users can search this table so that they can find friends on your site. When the sort by nickname, you would like for people without nicknames to be on the bottom of the list. Try: SELECT * FROM people ORDER BY nickname ASC Doesn't work, does it? It returns something like:

nickname	name
NULL	Art
NULL	Mary
Abby	Abigal
Bobby	Robert
Cindy	Cynthia

All the NULL values are on top. What to do? Try this: SELECT * , nickname IS NULL AS isnull FROM people ORDER BY isnull ASC, nickname ASC

nickname	name	isnull
Abby	Abigal	0
Bobby	Robert	0
Cindy	Cynthia	0
NULL	Art	1
NULL	Mary	1

What's Happening?
[nickname IS NULL AS isnull] adds a column named "isnull" and fills it with a 0 if there is a value for nickname, and 1 if nickname is NULL. We sort first on this "isnull" column, which puts nicknames first, and null values last. We then sort alphabetically by nickname.

You can also shorten this up by using the ISNULL() function. The ISNULL() function returns 1 if the parameter is null, and 0 otherwise. The query now looks like: SELECT * FROM people ORDER BY ISNULL(nickname), nickname ASC; It will return:

nickname	name
Abby	Abigal
Bobby	Robert
Cindy	Cynthia
NULL	Art
NULL	Mary

(Thanks to Amit Prasad for pointing out this cleaner solution.)