Null (SQL) - Data Typing of Null and Unknown

Data Typing of Null and Unknown

See also: Option type and Nullable type

The NULL literal is untyped in SQL, meaning that it is not designated as an integer, character, or any other specific data type. Because of this, it is sometimes mandatory (or desirable) to explicitly convert Nulls to a specific data type. For instance, if overloaded functions are supported by the RDBMS, SQL might not be able to automatically resolve to the correct function without knowing the data types of all parameters, including those for which Null is passed.

Conversion from the NULL literal to a Null of a specific type is possible using the CAST introduced in SQL-92. For example:

CAST (NULL AS INTEGER)

represents an integer which has the Null value.

The actual typing of Unknown (distinct or not from NULL itself) varies between SQL implementations. For example the following

SELECT 'ok' WHERE (NULL <> 1) IS NULL;

parses and executes successfully in some environments (e.g. SQLite or PostgreSQL) which unify a NULL boolean with Unknown but fails to parse in others (e.g. in SQL Server Compact). MySQL behaves similarly to PostgreSQL in this regard (with the minor exception that MySQL regards TRUE and FALSE as no different from the ordinary integers 1 and 0). PostgreSQL additionally implements a IS UNKNOWN predicate, which can be used to test whether a three-value logical outcome is Unknown, although this is merely syntactic sugar.