NULL Literals | Database Design | Teradata Vantage - 17.10 - NULL Literals - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
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.