SQL and Relational Theory, 2nd Edition

TYPE CHECKING AND COERCION IN SQL

SQL supports only a weak form of strong typing (if you see what I mean). To be specific:

• BOOLEAN values can be assigned only to BOOLEAN variables and compared only with BOOLEAN values.

• Numeric values can be assigned only to numeric variables and compared only with numeric values (where “numeric” means INTEGER, SMALLINT, BIGINT, NUMERIC, DECIMAL, or FLOAT).

• Character string values can be assigned only to character string variables and compared only with character string values (where “character string” means CHAR, VARCHAR, or CLOB).

• Bit string values can be assigned only to bit string variables and compared only with bit string values (where “bit string” means BINARY, BINARY VARYING, or BLOB).

Thus, for example, an attempt to compare a number and a character string is illegal. However, an attempt to compare (say) two numbers is legal, even if those numbers are of different types—say INTEGER and FLOAT, respectively (in this example, the INTEGER value will be coerced to type FLOAT before the comparison is done). Which brings me to the question of type coercion ... It’s a widely accepted principle in computing in general that coercions are best avoided, because they’re error prone. In SQL in particular, one bizarre consequence of permitting coercions is that certain unions, intersections, and differences can yield a result with rows that don’t appear in either operand! For example, consider the SQL tables T1 and T2 shown in Figure 2-3 below. Let column X ...