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
|NULL Values First||ASC||ASC||DESC|
|NULL Values Last||DESC||DESC||ASC|
Like this? Follow me ↯Tweet Follow @pfreitag
Order by NULL Values in MySQL, Postgresql and SQL Server was first published on March 10, 2022.
If you like reading about mysql, sql, postgresql, or sqlserver then you might also like:
- INFORMATION_SCHEMA Support in MySQL, PostgreSQL
- SQL to Select a random row from a database table
- SQL Reserved Key Words Checker Tool
- Top 10 Reserved SQL Keywords
- Backwards LIKE Statements
- SQL Pagination with LIMIT and OFFSET
- Top 3 differences between PostgreSQL and MS SQL
- Returning TOP N Records