The keyword NULL is sometimes available as a special construct similar to, but not identical with, a literal. In this case, the keyword NULL represents the SQL null placeholder for a value logically in an SQL request, but is not the same marker that the system stores to indicate missing information.
Rules for Using NULL as a Literal
- As a CAST source operand, for example.
CAST (NULL AS value) - As a CASE result, for example.
CASE expression THEN NULL END
or
CASE expression THEN expression ELSE NULL END
- As an item specifying a null is to be placed in a column on INSERT or UPDATE.
- As a default column definition specification, for example.
DEFAULT NULL
- As an explicit SELECT item, for example.
SELECT NULL
This usage is a Teradata extension to the ANSI/ISO SQL-2008 standard because it does not specify a FROM clause.
- As an operand of a function, for example.
SELECT TYPE(NULL)
This usage is a Teradata extension to the ANSI/ISO SQL-2008 standard because it does not specify a FROM clause.
Data Type of NULL Literals
When you use NULL as an explicit SELECT item or as the operand of a function, its data type is INTEGER. For example, if you perform SELECT TYPE(NULL), then the data type of NULL is returned as INTEGER.
In all other cases, NULL has no data type because it has no value.