Null Values

Null values are used to indicate that a value for a particular row is not known. Null values are not equal to anything; because their value is unknown, they might be equal to some value or they might not. Null is sometimes used to indicate that a value for a particular column is not meaningful. This is an abstract, and sometimes confusing, idea. What it boils down to, though, is that null values must be treated differently when performing comparisons in a where clause.

As an analogy for nulls, consider a supermarket checkout line. If an item doesn’t have a price tag, is it free? No. Its price is unknown. You won’t be allowed to buy it until a definite price can be found for the item. What’s the price of your shopping list with a null priced item on it? Also unknown, until we determine the value of the item.

If I ask you for a list of items on your shopping list that are under $1, is the null priced item included? Over $1? $1 exactly? Nulls are special cases and need to be treated in special ways.

In order to find a column that contains a null value, use the IS keyword. In the publishers table, a publisher that isn’t in the U.S. doesn’t have a meaningful value for the state column. We can find these publishers with the following query:

select    pub_name, city, state
from      publishers
where     state is null

pub_name                                 city                 state
---------------------------------------- -------------------- -----
GGG&G                                    Munchen              (null)
Lucerne Publishing                       Paris                (null)

(2 row(s) affected)

When Are Null Valued Rows Returned?

Nulls often surprise the novice T-SQL user. Suppose I asked for all the publishers who were based in California. This query is a simple …WHERE state = ‘CA’. If I then wanted the opposite of this, I would ask …WHERE state <> ‘CA’.

The two rows with null states, though, appear in neither result set! This is because the value for this column is not known. As far as the server can tell, those two publishers may or may not be in California. Because it can’t tell for sure—the state column is null—it does not match the row in either of these queries.

This means that when a column can contain a null value, you must go out of your way to include them. Whether or not a column can contain a null value is determined when the table is created. If you request sp_help output for a table, a 1 in the Nulls column indicates the column allows nulls, where a 0 indicates that it does not allow them.

Now, suppose I wanted to get a list of all the publishers that are not in California—and I wanted to include those publishers that had a null state.

select    pub_name, city, state
from      publishers
where     state <> ‘CA’
or state is null

Null Values and order by

For the purposes of sorting, null Values are considered to be the lowest value for that column. In an ascending sort (the default), nulls come first. This still doesn’t mean that … where price < $18 will find nulls—it won’t.

A Final Note on Matching Null

Now that you understand how to match nulls with the IS operator, let me throw in one final note to make your life easier, Microsoft has extended the ANSI SQL standard to allow use of the equals sign to match null values. This means that … where state = null, although not supported by the standard, will, in fact, find rows the same as … where state is null.

Saying is or is not null is good practice, though, and can help you remember how null values are an exceptional circumstance. It will also ease your transition to another database platform that uses SQL, but not Transact-SQL.

Summary

You learned how to use the WHERE clause to limit rows returned in your result sets. You learned how to apply the equality (=) operator and inequality operators to manipulate which rows were returned. You also learned about three special keywords that work with the WHERE clause: LIKE, IN, and BETWEEN. BETWEEN is interpreted by the server as a range between two closed-end values. IN is interpreted as a list of conditions OR ’d together. If you are new to programming, you learned about boolean logic: the AND, OR and NOT keywords.

Q&A

Q When are those special keywords used in the real world?

A LIKE is used for ad-hoc queries or when the data being searched is dirty. BETWEEN is seldom used, since the inequality operators can perform the same functions, with an added degree of control for “less than but not equal to” (<). IN is most often used with subqueries, which are discussed on Day 8. The idea behind subqueries is that you can use a SQL SELECT to return a list of values, which is then used as the IN list inside another SQL SELECT.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  What SQL predicate is used to limit which rows are returned?

2.  What is boolean logic? Why is it relevant to T-SQL?

3.  What does a date field look like in SQL? What happens if you do not specify a time?

4.  What keyword is used to test a list of values?

5.  What are null values?

Exercises

1.  Get a list of authors who live in California.

2.  Get a list of books from the titles table that are priced under $6 or over $18.

3.  Now get a list of books that have not been priced. (Hint: If the price of the book were undetermined or unknown, what value would be in the price column?)

4.  Get a list of author last names who have first names that begin with the letter C, L, or S.