Chapter 3: How to retrieve data from a single table

In this chapter, you’ll learn how to code SELECT statements that retrieve data from a single table.

How to use the IS NULL clause

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 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 clause as shown in figure 3-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.

The syntax of the WHERE clause with the IS NULL clause

WHERE expression IS [NOT] NULL

The contents of the NullSample table

SELECT *
FROM NullSample

A SELECT statement that retrieves rows with zero values

SELECT *
FROM NullSample
WHERE InvoiceTotal = 0

A SELECT statement that retrieves rows with non-zero values

SELECT *
FROM NullSample
WHERE InvoiceTotal <> 0

A SELECT statement that retrieves rows with null values

SELECT *
FROM NullSample
WHERE InvoiceTotal IS NULL

A SELECT statement that retrieves rows without null values

SELECT *
FROM NullSample
WHERE InvoiceTotal IS NOT NULL

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.

---

Figure 3-15 How to use the IS NULL clause