Back to articles list June 30, 2020 - 8 minutes read How ORDER BY and NULL Work Together in SQL Kateryna Koidan Kateryna is a data science writer from Kyiv, Ukraine. She worked for BNP Paribas, the leading European banking group, as an internal auditor for more than 6 years. More recently, she decided to pursue only the favorite part of her job—data analysis. Now she is continuing her self-education with deep-learning courses, enjoys coding for data analysis and visualization projects, and writes on the topics of data science and artificial intelligence. Kateryna is also a proud mother of two lovely toddlers, who make her life full of fun. Tags: sql learn sql ORDER BY 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; idpaintingauthoryear 4The Night WatchRembrandt1642 2The Starry NightVincent van Gogh1889 3The ScreamEdvard Munch1893 1Mona LisaLeonardo da VinciNULL 5The Birth of VenusSandro BotticelliNULL 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; idpaintingauthoryear 1Mona LisaLeonardo da VinciNULL 5The Birth of VenusSandro BotticelliNULL 3The ScreamEdvard Munch1893 2The Starry NightVincent van Gogh1889 4The Night WatchRembrandt1642 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; idpaintingauthoryear 1Mona LisaLeonardo da VinciNULL 5The Birth of VenusSandro BotticelliNULL 4The Night WatchRembrandt1642 2The Starry NightVincent van Gogh1889 3The ScreamEdvard Munch1893 Alternatively, if you add a DESC keyword to get a descending order, NULLs will appear last. SELECT * FROM paintings ORDER BY year DESC; idpaintingauthoryear 3The ScreamEdvard Munch1893 2The Starry NightVincent van Gogh1889 4The Night WatchRembrandt1642 5The Birth of VenusSandro BotticelliNULL 1Mona LisaLeonardo da VinciNULL 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: ASCDESC NULLs appear firstSQL Server, MySQL, SQLitePostgreSQL, Oracle NULLs appear lastPostgreSQL, OracleSQL Server, MySQL, SQLite 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; idpaintingauthoryear 1Mona LisaLeonardo da VinciNULL 5The Birth of VenusSandro BotticelliNULL 4The Night WatchRembrandt1642 2The Starry NightVincent van Gogh1889 3The ScreamEdvard Munch1893 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; idpaintingauthoryear 3The ScreamEdvard Munch1893 2The Starry NightVincent van Gogh1889 4The Night WatchRembrandt1642 1Mona LisaLeonardo da VinciNULL 5The Birth of VenusSandro BotticelliNULL 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; idpaintingauthoryear 4The Night WatchRembrandt1642 2The Starry NightVincent van Gogh1889 3The ScreamEdvard Munch1893 1Mona LisaLeonardo da VinciNULL 5The Birth of VenusSandro BotticelliNULL 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; idpaintingauthoryear 1Mona LisaLeonardo da VinciNULL 5The Birth of VenusSandro BotticelliNULL 3The ScreamEdvard Munch1893 2The Starry NightVincent van Gogh1889 4The Night WatchRembrandt1642 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! Tags: sql learn sql ORDER BY You may also like Difference between GROUP BY and ORDER BY in Simple Words For someone who's learning SQL, one of the most common concepts that they get stuck with is the difference between GROUP BY and ORDER BY. Read more NULL Values and the GROUP BY Clause We've already covered how to use the GROUP BY clause but how does SQL's GROUP BY clause work when NULL values are involved? Find out! Read more Useful SQL Patterns: Matching Nulls by Masking Nulls Today, in the first post of the SQL patterns series, we will consider the match by null pattern. It’ll help you deal with tables containing null values. Read more How to Tackle SQL NULLs: COALESCE function Let's introduce the COALESCE postgresql function. It's inevitable that some data in the database has no value. Find out what then with MySQL. Read more Understanding the Use of NULL in SQL Three-Valued Logic NULLs are necessary in databases, learning to use them is fundamental to SQL success. However, NULLs should be handled with care – see how! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.