O'Reilly logo

SQL: Visual QuickStart Guide by Chris Fehily

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required