How to Sort SQL Results With NULL Values at the End When Ascending

1. Introduction

When working with databases, we often encounter NULL values in our data. These represent missing or unknown information, and they can create unexpected behavior when sorting query results. By default, different database systems handle NULL values differently during sorting operations, which can lead to inconsistent results across platforms.

In this tutorial, we’ll explore how to ensure NULL values appear last when sorting results in ascending order across PostgreSQL, MySQL, and SQL Server.

The examples will be based on Baeldung’s University schema.

2. Sorting NULL Values

Let’s consider our Student table. We hold information about each student and their personal data. A student may have graduated or not, and this is reflected by the graduation_date column.

If we want to fetch all the students, sorting them by graduation date and keeping the ones that are still attending classes last, we’ll notice that how the NULL values are placed depends a lot on our database. Some databases place NULLs first, others place them last. This inconsistency can cause confusion and unexpected results in applications that need to work across different database platforms.

Let’s perform a query that returns all the students sorted by graduation date, keeping the students who are still attending our university at the bottom.

2.1. Sorting NULL Values in PostgreSQL

In PostgreSQL, there is a very simple way to sort by a column that contains NULL values. We can choose if we want them to appear on top or the bottom:

SELECT * FROM Student
ORDER BY graduation_date ASC NULLS LAST;

Using the NULLS LAST clause, our search moves all the students with NULL graduation_date to the bottom of our result list.

2.2. Sorting NULL Values in MySQL

MySQL doesn’t support the same easy way, so we need to manipulate our query:

SELECT * FROM Student
ORDER BY
  CASE WHEN graduation_date IS NULL THEN 1 ELSE 0 END,
  graduation_date ASC;

We’ve used a very simple function in our ORDER clause, so we can give a value different than NULL in our column and move the records to the bottom.

The case when statement gives the value 1 to NULL columns, moving them to the bottom of the result list. For all the records that don’t have a NULL value, we use the second part of the statement to sort them by the graduation_date.

We should note that this method isn’t optimal, as it creates a new sorting column, and it can affect performance in large datasets.

2.3. Sorting NULL Values in SQL Server

Finally, SQL Server doesn’t support the NULL LAST clause either, so we need to manipulate our query again, as we did for MySQL:

SELECT * FROM Student
ORDER BY
  CASE WHEN graduation_date IS NULL THEN 1 ELSE 0 END,
  graduation_date ASC;

This case when statement gives us the desired result.

3. Conclusion

In this article, we learned how to structure our query so that when we’re trying to sort by a column that contains NULL values, they appear at the end of the result list. More specifically, we looked at examples for PostgreSQL, MySQL, and SQL Server.