Control null data in Oracle using the ORDER BY clause

NULL data, which is sometimes called "absent" data, can be difficult to work with in relational databases.

When a query contains the ORDER BY clause to sort the output, NULL data will sort to the bottom if the sort is in ascending order (ASC) and to the top if the sort is in descending order (DESC). In effect, NULL is treated as a very large value by Oracle.

This can create reports that are difficult to read. Consider a simple query in Oracle's HR sample schema. Let's say you want to list the names and commission percentages of all employees in descending order, including those who get no commission (commission_pct is NULL). The following simple query does this:

SELECT employee_id, last_name, first_name, commission_pct

FROM employees

ORDER BY commission_pct DESC;

The problem is that all employees with no commission come out on top. You have to read through them all to find those who actually have a commission.

Starting with Oracle 8i, there is a little known syntax in the ORDER BY clause that fixes this. All you have to do is change the last line above to the following:

ORDER BY commission_pct DESC NULLS LAST;

The null rows will sort to the bottom after all the rows that contain commission data. You can also use NULLS FIRST when you're sorting in ascending order, and you want the NULL rows to appear at the top of the report.

If you're still supporting Oracle 8.0 or 7.3 databases, you can achieve the same effect using the Null Values function (NVL). Use something like the following in your ORDER BY:

ORDER BY NVL(commission_pct, -1);

This forces the NULL rows to be sorted as if they had the value (-1) in them, and they will appear at the bottom of the output. You won't see the (-1) values because the query only sorts by the NVL function -- it doesn't display it in the SELECT list.