How ORDER BY and NULL Work Together in SQL

Do NULL values come first or last when you use ORDER BY? Are they considered higher or lower than non-NULL values? In this article, I’ll explain how different relational databases treat NULL values when sorting output and how to change the default behavior of the ORDER BY clause.

When LearnSQL users practice the ORDER BY clause in our SQL Basics course, they often ask why NULL values appear first in the output and how they can change this behavior. Inspired by these inquiries, I’m going to do a deep dive into the topic of ordering rows that contain NULL values.

Do NULL values always come first by default? Is it possible to change how ORDER BY sorts NULL values? How can you apply the NULLS FIRST and NULLS LAST options? Let’s find out.

How Are NULLs Sorted by Default?

The SQL standard does not define the default ordering of NULLs. What does this mean?

If you apply the ORDER BY clause to a column with NULLs, the NULL values will be placed either first or last in the result set. The output depends on the database type. So, let’s see how different relational databases sort NULL values.

PostgreSQL

By default, PostgreSQL considers NULL values larger than any non-NULL value. If you sort your output in ascending order – by either adding the ASC keyword or by default (i.e. not specifying the order) – all NULL values will be shown last in the output. Here’s an example:

SELECT *
FROM paintings
ORDER BY year;

If you use the DESC keyword in ORDER BY to sort values in descending order, you’ll get NULL values at the top of the result table.

SELECT *
FROM paintings
ORDER BY DESC year;

Oracle

Oracle treats NULLs the same way as PostgreSQL. Specifically, Oracle’s documentation states that “if the null ordering is not specified, then the handling of the null values is NULLS LAST if the sort is ASC, NULLS FIRST if the sort is DESC.” In effect, Oracle considers NULL values larger than any non-NULL values.

SQLite

Unlike the above database types, SQLite considers NULLs to be smaller than any other value. If you sort a column with NULL values in ascending order, the NULLs will come first.

SELECT *
FROM paintings
ORDER BY year;

Alternatively, if you add a DESC keyword to get a descending order, NULLs will appear last.

SELECT *
FROM paintings
ORDER BY year DESC;

MySQL

Like SQLite, MySQL considers NULL values lower than any non-NULL value. If you use this database, expect the same treatment of NULL values as illustrated above: NULLs will appear first if the values are sorted in ascending order and last if descending order is used.

SQL Server

SQL Server also treats NULL values as smaller than any non-NULL values. You’ll see the NULLs first when a column is sorted in ascending order and last when the column is sorted in descending order.

Let’s summarize how NULLs are sorted by default in different databases:

How to Change the Default Behavior of ORDER BY

Now that you know the default behavior of various databases in sorting NULL values, you may wonder if it’s possible to change it.

The answer varies with the database type you use. The SQL standard offers NULLS FIRST / NULLS LAST options that change the sorting of NULL values when they’re added to ORDER BY.

Unfortunately, not all databases support this standard. Let’s dive deeper.

PostgreSQL and Oracle

As you recall, PostgreSQL and Oracle treat NULL values as very large and put them at the end of an ascending sort order and at the beginning of a descending sort order. However, you can easily change this behavior by simply adding NULLS FIRST or NULLS LAST to the ORDER BY clause.

SELECT *
FROM paintings
ORDER BY year NULLS FIRST;

Above we have a table that’s sorted in ascending order but with NULLs coming first. Below, we’ll do the reverse – sort in descending order with NULLs coming last:

SELECT *
FROM paintings
ORDER BY year DESC NULLS LAST;

SQLite

In contrast to PostgreSQL and Oracle, SQLite treats NULLs as very small values and puts them first in an ascending sort and last in a descending sort. Starting with SQLite version 3.30.0, this behavior can also be easily changed using the NULLS FIRST / NULLS LAST option.

SELECT *
FROM paintings
ORDER BY year NULLS LAST;

Above, the NULLS LAST keyword gives us an ascending sort order with the NULL values coming last. Let’s reverse this:

SELECT *
FROM paintings
ORDER BY year DESC NULLS FIRST;

Once again, the NULLS FIRST option puts the NULL values at the top of the descending sort order.

MySQL

Similarly to SQLite, MySQL treats NULL values as lower than any non-NULL value; thus, by default, it puts these values first when sorting in ascending order and last when sorting in descending order. However, MySQL does not support the NULLS FIRST / NULLS LAST options, which makes it more challenging to change the default behavior.

However, there are some tricks we can use to get NULL values where we want them:

Using the minus operator. (Note: This only works for numbers or dates.) To sort values in ascending order with NULLs coming last, we can use the following query:

SELECT *
FROM paintings
ORDER BY -year DESC;

Here, the minus operator before the column name makes MySQL sort non-NULL values in reverse order. If we add the DESC keyword, we return to the ascending order of non-NULL values. NULL values are not affected by the minus operator, while the DESC keyword makes them appear last when sorting in the descending order. Thus, this trick works the same way as the NULLS LAST option in SQLite.

To sort values in the descending order but with NULLs coming first, we can use the following query in MySQL:

SELECT *
FROM paintings
ORDER BY -year;

The query will result in the output being ordered by the year column in descending order. Here, the NULLs appear first – the same result we get with the NULLS FIRST option in SQLite.

Using the IS (NOT) NULL operator. (Works for all data types.) In this trick, we can rely on the fact that IS NULL returns a 1 for all NULL expressions and a 0 otherwise. To make NULLs appear last while sorting in ascending order, we can use the following query:

SELECT *
FROM paintings
ORDER BY year IS NULL, year;

Correspondingly, we can also sort the output in the descending order with NULLs appearing first. This time, we’ll use IS NOT NULL:

SELECT *
FROM paintings
ORDER BY year IS NOT NULL, year DESC;

The IS NULL and IS NOT NULL operators can be very handy in changing the MYSQL’s default behavior for sorting NULL values.

Using the COALESCE function. (Works for all data types.) If you are not familiar with this function, read our guide on handling NULL values with COALESCE function. Basically, we can sort NULLs last whilst sorting non-NULL values in ascending order by supplying the highest possible value as a substitute for NULL values:

SELECT *
FROM paintings
ORDER BY COALESCE(year, 2021);

Here, we use 2021 as the highest possible value for the year column. (We can be sure that no paintings in our table are from the future. We could use any number above 2020 to accomplish this.)

Correspondingly, to sort NULLs first whilst ordering non-NULL values in descending order, we can use the following query:

SELECT * 
FROM paintings
ORDER BY COALESCE(year, 2021) DESC;

The output of the above two queries will be identical to using the NULLS FIRST / NULLS LAST options in SQLite.

SQL Server

Like MySQL, SQL Server does not support the NULLS FIRST / NULLS LAST options. However, the tricks with the minus operator and the COALESCE function work in SQL Server the same way as they do in MySQL. You can use these options to change the default behavior of SQL Server when sorting NULL values.

Time to Practice Using ORDER BY with NULL Values!

Now you know that the default behavior of the ORDER BY clause when sorting NULL values varies with the database you’re using. In most cases, you can easily change this default behavior. Use the NULLS FIRST / NULLS LAST option with SQLite, Postgres, and Oracle. For MySQL and SQL, use the other tricks we’ve shown.

To confidently handle NULL values, check out these interactive LearnSQL courses:

• SQL Basics covers NULL values and ordering. Get more information on the content of this course in our specific guide on the SQL Basics course.
• SQL Practice Set provides even more practice on the ORDER BY clause.

Happy learning!