Enterprise Software

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 percents 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.

Bob Watkins (OCP, MCITP, MCDBA, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. He is a Senior Consultant and Managing Partner at B. Watkins, a database consulting and training firm in the Dallas / Fort Worth area. Visit Bob's site.

--------------------------------------------------------------------------------------

Get Oracle tips in your inbox

TechRepublic's free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

7 comments
respinoza55
respinoza55

Thanks for this!  I needed to sort NULL as the lowest possible value and this did the trick!

b_vijaykarthik
b_vijaykarthik

Excellent piece of information. b_vijaykarthik@hotmail.com

msi77
msi77

You can solve the problem in the scope of SQL-92 Standard: SELECT employee_id, last_name, first_name, commission_pct FROM employees ORDER BY CASE WHEN commission_pct IS NULL THEN 0 ELSE commission_pct END DESC; http://www.sql-ex.ru/

psinger1
psinger1

On a related point, I used to have problems distinguising between result sets with spaces in certain columns, and nulls in those columns (esp. when I wasn't ordering by them). Then I actually read Johnathan Gennick's book, and discovered that all I had to do was put SET NULL '' (or anything else I wanted a null to look like) and then where ever I had a null, would appear instead.

noacct
noacct

How about WHERE ... IS NOT NULL, OR A "MINUS" QUERY SELECT ... FROM ... MINUS SELECT ... FROM ... WHERE ... IS NULL ORDER BY ...;

spam
spam

because they actually WANT those rows in the result set? Your suggestion excludes them.

Editor's Picks