While writing SQL queries, one of the fundamental operations is to sort the data, which is often done using the “ORDER BY” clause. However, in certain situations, we deal with “NULL” values and wonder if they come first or last when we use the ORDER BY clause. In this tutorial, we will learn about how “ORDER BY” and “NULL” work together and how we can control their behaviour.
Basics of ORDER BY Clause
The ORDER BY clause in SQL allows us to sort the result set in either ascending or descending order. It offers a way to organise data in a way that could turn out as meaningful to the developer or application.
Syntax:
SELECT column01, column02
FROM table01
ORDER BY column01 [ASC | DESC], column02 [ASC | DESC];Code language: SQL (Structured Query Language) (sql)Example:
Below is a table named SALE:
Let’s say we want to sort the SALES_DONE by the employees in a way that the ones who made more sales, their NAMES appear first.
SELECT NAMES, SALES_DONE
FROM SALE
ORDER BY SALES_DONE DESC;Code language: SQL (Structured Query Language) (sql)
Understanding NULL Values
In SQL, NULL represents the absence of a value. It’s important to know that NULL doesn’t mean zero or an empty string, it represents the lack of a value completely.
While sorting the data which includes NULL values, SQL offers options to control the behaviour of the way these NULL values are treated in the sorting process.
Also Read: Understanding SQL Query Order of Operations
Default Behavior of ORDER BY with NULL
By default, NULL values are sorted at the beginning of the result set while using the ORDER BY clause in ascending order and last when sorted in descending order in SQL Server. It means that rows that contain NULL values in the sorted column in descending order will appear at the last.
Example:
Below is the table named record, which we will use for the rest of this tutorial.
As we can see, there are NULL values in the age column, let’s run a query to see how they appear:
SELECT name, age
FROM record
ORDER BY age;Code language: SQL (Structured Query Language) (sql)
In the above table, the NULL values are sorted such that they appear at the beginning.
Controlling the Placement of NULL Values with ORDER BY
While sorting the data, SQL offers ways to control the placement of NULL values. We can specify whether we want NULL values at the beginning or the end of the sorted result set.
1. Using ASC and DESC Keywords
ASC(Ascending) is the default sorting order. When we are sorting the data values in ascending order, NULL values will appear at the beginning of the result set.
Example of ASC:
Let’s say we want to sort the ages of the people in the record table in ascending order such that the NULL values appear at the beginning.
SELECT name, age
FROM record
ORDER BY age ASC;Code language: SQL (Structured Query Language) (sql)
DESC(Descending) is used when we want to sort the data values in descending order, where the NULL values will appear at the end of the result set.
Example of DESC:
Let’s say we want to sort the ages of the people in the record table in descending order such that the NULL values appear at the end.
SELECT name, age
FROM record
ORDER BY age DESC;Code language: SQL (Structured Query Language) (sql)
2. Using Minus Operator
If we want to sort the data values in ascending order such that NULL comes at the end of the result set, we can put the minus operator before the column name which is to be sorted using DESC keyword (This only works for numbers or dates).
Example:
Let’s say we want to sort the ages of the people in the record table in ascending order such that the NULL values appear at the end.
SELECT name, age
FROM record
ORDER BY -age DESC;Code language: SQL (Structured Query Language) (sql)
Let’s say we want to sort the ages of the people in the record table in descending order such that the NULL values appear at the beginning.
SELECT name, age
FROM record
ORDER BY -age;Code language: SQL (Structured Query Language) (sql)
Note: The minus operator which is written before the column name makes SQL sort non-NULL in reverse order. If we will add the DESC keyword, we will fetch the ascending order of non-NULL values.
3. Using the COALESCE Function
We can ensure that the NULL values appear at the end of the sorted list while arranging non-NULL values in ascending order. This can be done by providing the maximum possible value as a replacement for the NULL value.
Example:
If we aim to prioritise sorting NULL values ahead of non-Null values in descending order, we can use:
SELECT name, age
FROM record
ORDER BY COALESCE( age, 50) DESC;Code language: SQL (Structured Query Language) (sql)
Similarly, if we want to make NULL value appear at last while sorting in ascending order:
SELECT name, age
FROM record
ORDER BY COALESCE( age, 50);Code language: SQL (Structured Query Language) (sql)
Conclusion
In this tutorial, we have explored how the ORDER BY clause deals with NULL values and how we can control this behaviour using multiple SQL operators. It is important to know how to efficiently sort data in ascending or descending order and treat the NULL values.