Placement of NULL values for ORDER BY with nullable columns

MySQL and PostgreSQL are sorting NULL values in nullable columns entirely different. In MySQL they will be placed before everything and in PostgreSQL they will be last. But the intention of the application or the UX may need a different sort order. In these cases the ordering for NULL values can be changed easily.

Usage

MySQL

-- Default behaviour: NULL values placed first
SELECT * FROM customers ORDER BY country ASC;

-- NULL values placed first by rule
SELECT * FROM customers ORDER BY country IS NOT NULL, country ASC;

-- NULL values placed last by rule
SELECT * FROM customers ORDER BY country IS NULL, country ASC;

PostgreSQL

-- Default behaviour: NULL values placed last
SELECT * FROM customers ORDER BY country ASC;

-- NULL values placed first by rule
SELECT * FROM customers ORDER BY country ASC NULLS FIRST;

-- NULL values placed last by rule
SELECT * FROM customers ORDER BY country ASC NULLS LAST;

Detailed Explanation

The NULL value in SQL indicates an unknown value - the value for a column is just not known. These missing values are a problem when comparing them to another value and when ordering the rows in a table. From the mathematical point of view, a not-existing value cannot be compared with, e.g., any number. Therefore a mathematical-proven ordering is impossible. As the SQL standard missed specifying how NULL values should be treated for sorting results, the correct ordering is undefined. Every database needed to invent its own convention. In MySQL, any NULL value will be placed before everything when sorting in ascending direction, and in PostgreSQL they will be placed last. Both approaches are different solutions to define a deterministic ordering the database can use.

However, these database-specific sorting directions may not satisfy the application's requirements. To improve the user experience, the placement of empty values at the beginning or end is favorable. A user sorting a list of names in ascending or descending order will not be interested in seeing empty values first when searching for a specific record. Showing a list of blog articles in a backend will have different requirements in contrast. The unpublished articles should be listed first, followed by all published articles in descending order to see the newest ones first.

The placement of NULL values has to change in the application's context as the database's default ordering will not always be helpful. A simple rule to control the placement for NULL values is as follows:

• Show NULLS LAST when a user may search for specific records.
• Show NULL FIRST when these records have a special meaning and therefore need to precede everything.

Additional Resources

• MySQL Documentation: How to Work with NULL Values and the ordering implications.
• PostgreSQL Documentation: How to Sort Rows (ORDER BY) and handle NULL values.

Tobias Petry

SQL for Devs Founder