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