How to use the IS NULL condition - Oracle

In chapter 1, you learned that a column can contain a null value. A null isn’t the same as zero, a blank string that contains one or more spaces (‘ ’), or an empty string that doesn’t contain any spaces (‘’). Instead, a null value indicates that the data is not applicable, not available, or unknown. When you allow null values in one or more columns, you need to know how to test for them in search conditions. To do that, you can use the IS NULL condition, as shown in figure 3-16.

This figure uses a table named null_sample to illustrate how to search for null values. This table contains two columns. The first column, invoice_id, is an identification column. The second column, invoice_total, contains the total for the invoice, which can be a null value. As you can see in the first example, the invoice with an invoice_id of 3 contains a null value.

The second example in this figure shows what happens when you retrieve all the invoices with invoice totals equal to zero. Notice that the row with a null invoice total isn’t included in the result set. Likewise, it isn’t included in the result set that contains all the invoices with invoices totals that aren’t equal to zero, as illustrated by the third example. Instead, you have to use the IS NULL condition to retrieve rows with null values, as shown in the fourth example.

You can also use the NOT operator with the IS NULL condition as illustrated in the last example in this figure. When you use this operator, all of the rows that don’t contain null values are included in the query results.

The syntax of the WHERE clause with the IS NULL condition

WHERE expression IS [NOT] NULL

The contents of the Null_Sample table

SELECT *
FROM null_sample

A SELECT statement that retrieves rows with zero values

SELECT *
FROM null_sample
WHERE invoice_total = 0

A SELECT statement that retrieves rows with non-zero values

SELECT *
FROM null_sample
WHERE invoice_total <> 0

A SELECT statement that retrieves rows with null values

SELECT *
FROM null_sample
WHERE invoice_total IS NULL

A SELECT statement that retrieves rows without null values

SELECT *
FROM null_sample
WHERE invoice_total IS NOT NULL

--------------------------------------------Figure 3-16 How to use the IS NULL condition