NULL Keyword as Literal | SQL Fundamentals | Teradata Vantage - NULL Keyword as a Literal - Advanced SQL Engine - Teradata Database

SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
zwv1557098532464.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1141
lifecycle
previous
Product Category
Teradata Vantageâ„¢
A null represents either:
  • An empty column
  • An unknown value
  • An unknowable value

Nulls are neither values nor do they signify values; they represent the absence of value. A null is a place holder indicating that no value is present.

NULL Keyword

The keyword NULL represents null, and is sometimes available as a special construct similar to, but not identical with, a literal.

ANSI Compliance

NULL is ANSI/ISO SQL:2011-compliant with extensions.

Using NULL as a Literal

Use NULL as a literal in the following ways:
  • A CAST source operand, for example:
    SELECT CAST (NULL AS DATE);
  • A CASE result, for example.
    SELECT CASE WHEN orders = 10 THEN NULL END FROM sales_tbl;
  • An insert item specifying a null is to be placed in a column position on INSERT.
  • An update item specifying a null is to be placed in a column position on UPDATE.
  • A default column definition specification, for example:
    CREATE TABLE European_Sales
       (Region INTEGER DEFAULT 99
       ,Sales Euro_Type DEFAULT NULL);
  • An explicit SELECT item, for example:
    SELECT NULL

    This is a Teradata extension to ANSI.

  • An operand of a function, for example:
    SELECT TYPE(NULL)

    This is a Teradata extension to ANSI.

Data Type of NULL

When you use NULL as an explicit SELECT item or as the operand of a function, its data type is INTEGER. In all other cases NULL has no data type because it has no value.

For example, if you perform SELECT TYPE(NULL), then INTEGER is returned as the data type of NULL.

To avoid type issues, cast NULL to the desired type.

Related Topics

For information on the behavior of nulls and how to use them in data manipulation statements, see Manipulating Nulls.