Order by NULL Values in MySQL, Postgresql and SQL Server

If you have a column that may contain NULL values, and you want sort on that column with an ORDER BY clause, which comes first the null values or the non null values?

This is something that I have to look up, or simply test each and every time I need to know, so I figured it would be good material for a blog entry.

NULL Values First on MySQL or PostgreSQL

If you want the columns with null values to show up first, then sort ascending.

SELECT * FROM orders
ORDER BY date_shipped ASC

This would show orders that have not shipped first, and then the orders sorted by date_shipped ascending.

NULL Values Last on MySQL or PostgreSQL

If you want the NULL values to show up after column values, use a DESC sort direction in your order by clause:

SELECT * FROM orders
ORDER BY date_shipped DESC

This query would show the most recently shipped orders first, and orders that have not shipped yet would be last.

Here's a DB Fiddle so you can run the above example on MySQL or PostgreSQL.

SQL Server works the Opposite

Now, I found that SQL Server actually will sort the opposite way, and put the null values first when you sort ascending, and the null values last when you sort descending. Interesting, but also something to be aware of if you are trying to write cross database SQL.

Null Sorting by Database Engine