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

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

Database Design

Product

Advanced SQL Engine

Teradata Database

Release Number

17.05

17.00

Published

June 2020

Language

English (United States)

Last Update

2021-01-22

dita:mapPath

qby1588121512748.ditamap

dita:ditavalPath

lze1555437562152.ditaval

dita:id

B035-1094

lifecycle

Product Category

Teradata Vantage™

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

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.