How to Retrieve Data from a Single Table - How to Use the IS NULL Clause
(Page 15 of 17 )
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 ( ' ' ). Instead, a null value indicates that the information is not applicable, not available, or unknown. When you allow null values in one or more columns, you'll need to know how to test for them in search conditions. To do that, you use the IS NULL clause as shown in figure 15.
This figure uses a table named NullSample to illustrate how to search for null values. This table contains two columns. The first column, InvoiceID, is an identity column. The second column, InvoiceTotal, contains the total for the invoice, which can be a null value. As you can see in the first example, the invoice with InvoiceID 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 that has 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 clause to retrieve rows with null values, as shown in the fourth example.
You can also use the NOT operator with the IS NULL clause 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.
Figure 15: The syntax of the WHERE clause with the IS NULL clause
WHERE expression IS [NOT] NULL
The contents of the NullSample table:
A SELECT statement that retrieves rows with zero values:
A SELECT statement that retrieves rows with non-zero values:
A SELECT statement that retrieves rows with null values:
A SELECT statement that retrieves rows without null values:
Description
- A null value represents a value that's unknown, unavailable, or not applicable. It isn't the same as a zero, a blank space ( ' ' ), or an empty string ( ' ' ).
- To test for a null value, you can use the IS NULL clause. You can also use the NOT keyword with this clause to test for values that aren't null.
- The definition of each column in a table indicates whether or not it can store null values. Before you work with a table, you should identify those columns that allow null values so you can accommodate them in your queries.
Note: SQL Server provides an extension that lets you use = NULL to test for null values. For this to work, however, the ANSI_NULLS system option must be set to OFF. For more information on this option, see Books Online.
This is from chapter three of Murach's SQL for SQL Server by Syverson (Murach, ISBN 1-890774-16-2, 2002). Grab a copy at your favorite book store today! Buy this book now. |
Next: How to Code the ORDER BY Clause >>
More MS SQL Server Articles
More By Bryan Syverson
