Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
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
  • + Share This
  • 🔖 Save To Your Account