NexusDB Guide > SQL Reference > SQL Language Elements

NexusDB V3 Manual

Null Values

NexusDB Manual V3 > SQL Reference > SQL Language Elements

SQL uses a special value, the null value, to indicate that a data element does not have a value. Every data element, such as table columns, variables, parameters, value expressions, the result of functions, etc., can have a null value. An exception to this rule are columns with the NOT NULL constraint, which prohibits columns from containing the null value in any row.

The null value is different from any valid value, and is therefore not the same as zero, blanks or an empty string. This concept is often confusing to programmers more familiar with programming languages like C and Pascal that define a "null string" to mean a string with length 0. In SQL, an empty string is a known value (it has no characters), while null is an unknown value.

SQL provides the keyword NULL to represent a null value in SQL statements. However, since the keyword does not associate a data type with the null value, SQL also provides a syntactic ability to associate a data type with the null value by using an explicit cast:

CAST( NULL AS data-type )

As a consequence of the null value concept, SQL uses a three-valued logic (TRUE, FALSE, UNKNOWN) when evaluating boolean expressions.

The main characteristics of the null value are summarized below:

§	The null value is an unknown value.

§	Null values are not distinct, meaning that it's not possible to distinguish between two null values.

§	Null values are treated as a single group in grouping operations.

§	Null values are sorted before all non-null values by default. The null ordering sort option may however specify that nulls are sorted last.

§	Null values as operands of arithmetic operations and string concatenations, or as function arguments, will cause the result to be null.

§	Some of the predicates, but not all, will evaluate to UNKNOWN if one of the predicands is the null value.

Conformance

SQL:2003 standard

Core SQL

nexus_logo