SQL: Visual QuickStart Guide

Nulls in Subqueries

Beware of nulls; their presence complicates subqueries greatly. If you don’t eliminate them when they’re present, you may get an unexpected answer.

A subquery can hide a comparison to a null. Recall from “Nulls” in Chapter 3 that nulls don’t equal each other and that you can’t determine whether a null matches any other value. I’ll use an example that involves a NOT IN subquery (see “Testing Set Membership with IN” later in this chapter.) Consider the following two tables, each with one column. The first table is named table1:

col
----
   1
   2

The second table is named table2:

col
----
   1
   2
   3

If I run Listing 8.16 to list the values in table2 that aren’t in table1, I get Figure 8.16a, as expected. Now I’ll add a null to table1 ...