Database Reference
In-Depth Information
6.2
Sorting and Null Values
Null values, by definition, have an unknown value, so they cannot be logi-
cally placed in any order. To handle null values, Oracle Database 10
g
has
established the following default sorting rules for null values:
If the column containing null values is being sorted in ascending
order, the rows with null values are listed at the end.
If the column containing null values is being sorted in descending
order, the rows with null values are listed at the beginning.
You can reverse either of these rules by using the NULLS FIRST or
NULLS LAST keywords in your ORDER BY clause. Let's show how null
values and sorting interact. This query retrieves songs with titles that start
with A, B, or C and sorts by the playing time.
SELECT RECORDING_DATE, PLAYING_TIME, TITLE
FROM SONG
WHERE TITLE BETWEEN 'A' and 'C'
ORDER BY PLAYING_TIME;
Figure 6.4 shows the result. Notice that the rows with null values in
PLAYING_TIME appear last in the list, using the default ascending (ASC)
order.
Now we can add the NULLS FIRST parameter to the ORDER BY
clause and run the query again.
ORDER BY PLAYING_TIME NULLS FIRST;
Figure 6.5 shows the result. Notice that the rows with null values in
PLAYING_TIME now appear first in the list.
Remember that when you add comparisons in the WHERE clause, null
values do not match unless you specifically handle them. For example, the
next variation adds a WHERE clause to the query that looks for songs with
playing times of less than four minutes.
