Database Reference
In-Depth Information
Figure 6.6
Null Values Are
Never Greater or
Less Than Any
Value.
SELECT RECORDING_DATE, PLAYING_TIME, TITLE
FROM SONG
WHERE TITLE BETWEEN 'A' and 'C'
AND PLAYING_TIME < '4:00'
ORDER BY PLAYING_TIME NULLS FIRST;
Figure 6.6 shows the result of the previous query. All of the rows con-
taining a null value in the PLAYING_TIME column were eliminated
because a null value cannot be compared successfully to a non-null value.
There are two ways to handle null values in the WHERE clause:
Use the IS NULL comparison operator to retrieve null value rows.
Use the NVL function to convert null values before comparing them.
Let's try them both. First, we add another comparison to the WHERE
clause to handle null values:
WHERE TITLE BETWEEN 'A' and 'C'
AND (PLAYING_TIME < '4:00' OR PLAYING_TIME IS NULL)
Figure 6.7 shows the result such that one row has a playing time less
than four minutes, and five rows have null values in the PLAYING_TIME
column.
