SQL and Relational Theory, 2nd Edition

EMPTY SETS

The empty set is the set containing no elements. This concept is both ubiquitous and extremely important in the relational world, but SQL commits a number of errors in connection with it. Unfortunately there isn’t much you can do about most of those errors, but you should at least be aware of them. Here they are (this is probably not a complete list):

• A VALUES expression isn’t allowed to contain an empty row expression commalist.

• The SQL “set functions” 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.

• If the set of grouping columns and the table being grouped are both empty, GROUP BY produces a result containing just one (necessarily empty) group, whereas it should produce a result containing no groups at all.

• A key can’t be an empty set of columns (nor can a foreign key, a fortiori).

• A table can’t have an empty heading.

• A SELECT item commalist can’t be empty.

• A FROM item commalist can’t be empty.

• The set of common columns in UNION CORRESPONDING, INTERSECT CORRESPONDING, and EXCEPT CORRESPONDING can’t be empty (though it can be for JOIN).

• A row can’t have an empty set of components.