How to Sort NULLs Last in SQL Server: A Simple Approach

When working with SQL, sorting your data is a common task. But what happens when some records contain NULL values? By default, SQL treats NULL as the lowest value. This means that when you sort in ascending order, all rows with NULL values appear at the top of your result set. While some databases offer an easy way to sort NULLs last, SQL Server doesn't have a built-in solution. But don't worry—there's a simple workaround.

Default Handling of NULLs in SQL

Before we dive into solving the problem, let's look at how SQL handles NULL values.

In an ascending sort order, NULLs are treated as the smallest value. This means they show up first in the result set, even if this isn't what you want.

For example, imagine a table with department names:

Department Name
Sales
NULL
HR
NULL
IT

If you run a query like this:

SELECT DepartmentName  
FROM Departments  
ORDER BY DepartmentName ASC;

Your result will look like this:

Department Name
NULL
NULL
HR
IT
Sales

But what if you want the NULL values at the bottom of the list? Unfortunately, SQL Server doesn't provide a direct way to force NULLs to appear last.

Custom Sorting to Move NULLs Last

To sort NULLs last in SQL Server, you'll need to write a custom sorting condition. The idea is to evaluate whether a column contains NULL and assign it a custom sorting value.

Here’s how it works:

Check if the value in the column is NULL.
Assign a value of 1 to NULL.
Assign a value of 0 to non-NULL values.
Use this computed value to control the sort order.

Here's the modified query:

SELECT DepartmentName  
FROM Departments  
ORDER BY  
    CASE WHEN DepartmentName IS NULL THEN 1 ELSE 0 END ASC,  
    DepartmentName ASC;

Breaking Down the Query

The CASE statement checks if DepartmentName is NULL.
If it is NULL, the query returns 1. Otherwise, it returns 0.
In the ORDER BY clause, rows with a value of 0 (non-NULL) are sorted first, followed by rows with a value of 1 (NULLs).

The second part of the ORDER BY clause ensures that non-NULL values are sorted alphabetically after handling the NULLs.

What the Result Looks Like

Running the above query on the same table would produce this result:

Department Name
HR
IT
Sales
NULL
NULL

Now, the NULL values appear at the bottom as you wanted.

Why Does This Work?

SQL Server processes the CASE expression first, using the results to steer the sort order. By assigning a higher numeric value to NULLs, you push them to the bottom when sorting in ascending order. The rest of the data is sorted normally based on your desired column.

Wrapping Up

While SQL Server doesn't have a direct way to sort NULLs last, this simple solution resolves the issue. By adding a CASE statement to your ORDER BY clause, you gain full control over how NULLs are treated in your result set.

This technique isn't just a quick fix—it’s a useful trick for adding flexibility when sorting data. Whether you're prepping for a SQL interview or solving a real-world problem, knowing this approach can come in handy.

Want to learn more essential SQL tips? Check out these resources:

For more queries, feel free to contact us via email or visit KnowStar online!