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.
-- 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;
-- 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;
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:
SQL for Devs Founder
Be notified on future content. Never spam.