HomeOracle Page 2 - Retrieving Table Data with the LIKE Operator
How to use the IS NULL condition - Oracle
In this eighth part of a nine-part article series focusing on the SELECT statement and its usage in retrieving data from tables, you'll learn how to use the LIKE operator and much more. This article is excerpted from chapter three of the book Murach's Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).
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