SQL and Relational Theory, 2nd Edition

AVOIDING NULLS IN SQL

The relational model prohibits nulls; to use SQL relationally, therefore, steps must be taken to prevent them from occurring. First of all, a NOT NULL constraint should be specified, explicitly or implicitly, for every column in every base table (see Chapter 5); then nulls will never occur in base tables as such. Unfortunately, however, certain SQL expressions can still yield result tables containing nulls. Here are some of the situations in which nulls can be produced:

• The SQL “set functions” such as SUM all return null if their argument is empty (except for COUNT and COUNT(*), which correctly return zero in such a situation).

• If a scalar subquery evaluates to an empty table, that empty table is coerced to a null.

• If a row subquery evaluates to an empty table, that empty table is coerced to a row of all nulls. Note: A row of all nulls and a null row aren’t the same thing at all, logically speaking (another logical difference here, in fact)—yet SQL does think they’re the same thing, at least some of the time. But it would take us much too far afield to get into the detailed implications of this state of affairs here.

• Outer joins and “union joins” are expressly designed to produce nulls in their result.^[57]

• If the ELSE clause is omitted from a CASE expression, an ELSE clause of the form ELSE NULL is assumed.

• The expression NULLIF(x,y) returns null if x = y evaluates to TRUE.

• The “referential triggered actions” ON DELETE SET NULL and ON UPDATE SET NULL can both generate ...