SQL Tutorial – Order NULL Values First or Last in SQL

Suppose you have a columns LifeExpectancy which contains Null Values and You want to either sort the null values first or last without interfering with the Non-Null values.

And If you sort the this column in ascending order, you will see that NULL values will comes first then Non values and Vice-versa. But sometimes may be you don’t want this. May be you want to Sort the Non-Null values in ascending or descending Order and Null values at first or You want to sort Non-Null values in ascending or descending Order but the Null Values at last. Using the below query like this won’t get you the desired result that you want.

SELECT
    NAME as Country,
    LifeExpectancy
FROM
    country
ORDER BY
 2 

Solution –

The easiest way to solve this problem is to use a CASE Expression to Flag whether a values is null or not. Once you have that it is very easy to sort the Null values first or last without interfering with the Non-Null values using an subquery.

let’s see how to do that.

Non-Null values in Ascending and Null values at last –

SELECT
    country,
    LifeExpectancy
FROM
    (
        SELECT
            NAME as Country,
            LifeExpectancy,
            CASE
                WHEN LifeExpectancy IS NULL then 0
                ELSE 1
            END as is_null
        FROM
            country
    ) x
ORDER BY
    is_null DESC,
    LifeExpectancy

Here, we wanted the Null values at last and we know that it is denoted as 0 in our CASE expression, so we order the is_null column is descending order and then we ordered the LifeExpectancy column in ascending order as we want the Non-Null values in ascending order.

Non-Null Values in Descending and Null values at last –

SELECT
    country,
    LifeExpectancy
FROM
    (
        SELECT
            NAME as Country,
            LifeExpectancy,
            CASE
                WHEN LifeExpectancy IS NULL then 0
                ELSE 1
            END as is_null
        FROM
            country
    ) x
ORDER BY
    is_null DESC,
    LifeExpectancy DESC

Here, we kept the is_null DESC as it is as we want the null values at last but we changed the order of LifeExpectancy column from ASC to DESC as we want our non-null values to be in descending order.

Non-Null Values in Ascending and Null values at First –

SELECT
    country,
    LifeExpectancy
FROM
    (
        SELECT
            NAME as Country,
            LifeExpectancy,
            CASE
                WHEN LifeExpectancy IS NULL then 0
                ELSE 1
            END as is_null
        FROM
            country
    ) x
ORDER BY
    is_null,
    LifeExpectancy

Here, we wanted the Null values at first so we sort the is_null in ascending order.

Non-Null values in Descending and Null values at First –

SELECT
    country,
    LifeExpectancy
FROM
    (
        SELECT
            NAME as Country,
            LifeExpectancy,
            CASE
                WHEN LifeExpectancy IS NULL then 0
                ELSE 1
            END as is_null
        FROM
            country
    ) x
ORDER BY
    is_null,
    LifeExpectancy DESC

Here, we kept the is_null as it is as we want null values first but changed the second column in descending order as we want our non-null values in descending order.

Note – Be careful how you describe the CASE Expression for the is_null column, if you change the logic then your answer should also change according to it. So, please read it carefully and try to code it yourself if the solution is not very clear.

If you like this post then please share it with others and subscribe to our blog below.