How To Receive Data from a Single Table, concluded - How to use the IS NULL clause

(Page 3 of 5 )


 
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 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.

Figure 3-15.  How to use the IS NULL clause

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.

Next: How to code the ORDER BY clause >>
 

More MS SQL Server Articles
More By Murach Publishing

Comments On: How To Receive Data from a Single Table, concluded

· This article is an excerpt from the book "Murach's SQL for SQL Server," published by...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter three of the book Murach's SQL for SQL Server, written by Bryan Sylverson (Murach; ISBN: 1890774162). Check it out today at your favorite bookstore. Buy this book now.