Sort NULL values to the end of a table

sql

null-handling

sql-sorting

database-optimization

byAnton Shumikhin·Oct 24, 2024

To push NULL values to the end when sorting a column in SQL call for the ORDER BY, coupled with a CASE or IS NULL clause. Here's how to achieve this:

For most SQL variants, use:

SELECT * FROM tableName
ORDER BY CASE WHEN columnName IS NULL THEN 1 ELSE 0 END, columnName; -- because 1 goes after 0, like in a marathon

If using PostgreSQL, MySQL, or SQLite, this is your go-to:

SELECT * FROM tableName
ORDER BY columnName IS NULL, columnName; -- NULL behaves like it's allergic to the beginning of a column

Both examples put non-NULLs first and relegate NULLs to the bottom, resulting in a sorted order from the smallest to the largest value with NULLs lagging at the very end.

Understanding NULL in SQL

Grasping how to elegantly maneuver NULL sorting is critical for maintaining data consistency. Rather than ignoring NULL in your result sets, savvy sorting techniques can help you extract more value out of your data.

Customizing for SQL dialects

Nuances between different SQL databases affect NULL sorting behavior:

PostgreSQL handy NULLS LAST option for the ORDER BY clause sorts NULL values at the end, both in ascending and descending orders.
MySQL and SQLite, however, lack a built-inNULLS LAST syntax but achieve the same effect through expressions like shown in the fast answer.

PostgreSQL's NULL handling

PostgreSQL 8.3 and newer versions allow this straight-to-the-point syntax:

SELECT * FROM tableName
ORDER BY columnName DESC NULLS LAST; -- In SQL, even NULLs have a LAST name

In the ascending order scenario, NULLs are sorted as the last values by default, no special syntax needed there.

Retro solutions for older SQL versions

In older database versions or different RDBMS, such as SQL Server, you might want to take down to the battlefront a CASE statement or an extra column that mimics the NULL state. See an example:

SELECT * FROM tableName
ORDER BY (CASE WHEN columnName IS NULL THEN 1 ELSE 0 END), columnName; -- when you can't tell if NULL is a 1 or 0, it's time for CASE

Interacting with booleans

Know that in PostgreSQL, FALSE sorts before TRUE. This means that when sorting in descending order, FALSE (aka 0) will come before TRUE (aka 1), effectively putting NULL values at the end.

Pro-tips and common gotchas

Effectively using NULL sorting depends heavily on meticulousness and understanding. Here's how to handle NULL values when sorting that would make you a SQL ninja.

Pro-tips

Clear communication is key in SQL. Explicitly state NULL handling in the ORDER BY clause to bypass any ambiguity.
Testing is your friend. Always put your query to the test with data sets that include NULL values.
Document your query with appropriate comments. It helps maintainers understand your intention behind the special NULL sorting.

Common traps

Overlooking that NULL signifies a state of being unknown or missing, not an actual value, which can lead to logical errors in ordering.
Forgetting that databases sort NULL as either the lowest or highest value based on their default behavior.
Not remembering that NULL comparisons (like NULL = NULL) result in FALSE, which can lead to surprising sorting behaviors.