SQL: Ordering with NULL values

Ordering in SQL is quite easy:

SELECT * FROM people ORDER BY nick_name;

This would result in a nice table like this:

id nick_name
3 Ariejan
2 Harm
1 Ralph

Now, let’s say for some reason you have NULL values in the nick_name column. This may be because you allow NULL values here, or because you’re doing a LEFT JOIN or something else crazy. Having NULL values will result in the following:

id nick_name
4 NULL
5 NULL
3 Ariejan
2 Harm
1 Ralph

This is not always desireable. It’s very well possible that you want to sort all non-NULL values first. If you know the following trick, it’s quite easy. (Actually, it’s not a trick, just some creative SQL writing).

SELECT * FROM people ORDER BY nick_name IS NULL, nick_name;

id nick_name
3 Ariejan
2 Harm
1 Ralph
4 NULL
5 NULL

How does this work? The ORDER BY nick_name IS NULL part will create a new (virtual) column that stores the result of nick_name IS NULL. If the field contains a nick_name the resulting value is 0, if there’s a NULL value it will contain 1. Sorting by this virtual attribute will sort 0’s before 1’s. After sorting the NULL’s to the bottom, you may specify other sorting criteria, such as the normal nick_name.

http://blog.kabisa.nl/2008/11/14/sql-ordering-with-null-values/