16.10 - NULL Literals - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

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

The literal NULL can be used in the following ways.

  • 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.