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.

Knowledge is power. Power corrupts. You are now more corrupted.

• Search for PHP articles similar to "MySQL Sort Order with NULL.
• Search all articles similar to "MySQL Sort Order with NULL".
• List PHP articles from all authors.