- Retrieving Columns with SELECT and FROM
- Creating Column Aliases with AS
- Eliminating Duplicate Rows with DISTINCT
- Sorting Rows with ORDER BY
- Filtering Rows with WHERE
- Combining and Negating Conditions with AND, OR, and NOT
- Matching Patterns with LIKE
- Range Filtering with BETWEEN
- List Filtering with IN
- Testing for Nulls with IS NULL
This chapter is from the book
This chapter is from the book
This chapter is from the book
Testing for Nulls with IS NULL
Recall from “Nulls” in Chapter 3 that nulls represent missing or unknown values. This situation causes a problem: LIKE, BETWEEN, IN, and other WHERE-clause conditions can’t find nulls because unknown values don’t satisfy specific conditions. A null matches no value—not even other nulls. You can’t use = or <> to test whether a value is null.
In the table publishers, for example, note that publisher P03 has a null in the column state because that column doesn’t apply to Germany (Listing 4.42 and Figure 4.42). I can’t use complementary comparisons to select the null, because null is neither California nor not-California; it’s undefined (Listings 4.43 and 4.44, Figures 4.43 and 4.44).
Listing 4.42. List the locations of all the publishers. See Figure 4.42 for the result.
SELECT pub_id, city, state, country FROM publishers;
Figure 4.42 Result of Listing 4.42. The column state doesn’t apply to the publisher located in Germany.
pub_id city state country ------ ------------- ----- ------- P01 New York NY USA P02 San Francisco CA USA P03 Hamburg NULL Germany P04 Berkeley CA USA
Listing 4.43. List the publishers located in California. See Figure 4.43 for the result.
SELECT pub_id, city, state, country FROM publishers WHERE state = 'CA';
Figure 4.43 Result of Listing 4.43. This result doesn’t include publisher P03.
pub_id city state country ------ ------------- ----- ------- P02 San Francisco CA USA P04 Berkeley CA USA
Listing 4.44. List the publishers located outside California (the wrong way—see Listing 4.45 for the correct way). See Figure 4.44 for the result.
SELECT pub_id, city, state, country FROM publishers WHERE state <> 'CA';
Figure 4.44 Result of Listing 4.44. This result doesn’t include publisher P03 either. The conditions state = 'CA' and state <> 'CA' aren’t complementary after all; nulls don’t match any value and so can’t be selected by using the types of conditions I’ve covered so far.
pub_id city state country ------ -------- ----- ------- P01 New York NY USA
To avert disaster, SQL provides IS NULL to determine whether a given value is null. The IS NULL condition’s important characteristics are:
- IS NULL works for columns of any data type.
- You can negate an IS NULL condition with IS NOT NULL.
- You can combine IS NULL conditions and other conditions with AND and OR.
To retrieve rows with nulls or non-null values:
Type:
SELECT columns FROM table WHERE test_column IS [NOT] NULL;
columns is one or more comma-separated column names, and table is the name of the table that contains columns.
In the search condition, test_column is the name of a column in table. (test_column doesn’t have to be listed in columns.) Specify NOT NULL to match non-null values (Listings 4.45 and 4.46, Figures 4.45 and 4.46).
Listing 4.45. List the publishers located outside California (the correct way). See Figure 4.45 for the result.
SELECT pub_id, city, state, country FROM publishers WHERE state <> 'CA' OR state IS NULL;Figure 4.45 Result of Listing 4.45. Now publisher P03 is in the result.
pub_id city state country ------ -------- ----- ------- P01 New York NY USA P03 Hamburg NULL Germany
Listing 4.46. List the biographies whose (past or future) publication dates are known. See Figure 4.46 for the result.
SELECT title_id, type, pubdate FROM titles WHERE type = 'biography' AND pubdate IS NOT NULL;Figure 4.46 Result of Listing 4.46. Without the IS NOT NULL condition, this result would have included title T10.
title_id type pubdate -------- --------- ---------- T06 biography 2000-07-31 T07 biography 1999-10-01 T12 biography 2000-08-31
Tips
- test_column can be an expression.
- The NOT that can precede NULL is independent of the NOT that can precede test_column; see the Tips in “Matching Patterns with LIKE” earlier in this chapter.
Nulls cause rows to be excluded from results only if a column containing nulls is a test column in a WHERE condition. The following query, for example, retrieves all the rows in the table publishers (refer to Figure 4.42) because the null in the column state isn’t compared with anything:
SELECT pub_id, city, state, country FROM publishers WHERE country <> 'Canada';
To forbid nulls in a column, see “Forbidding Nulls with NOT NULL” in Chapter 11.
It bears repeating that a null isn’t the same as an empty string (''). In the table authors, for example, the column au_fname contains an empty string for author A06 (last name of Kellsey). The WHERE condition to find the first name is
WHERE au_fname = ''
and not
WHERE au_fname IS NULL
Oracle treats an empty string ('') as a null; see the DBMS Tip in “Nulls” in Chapter 3.