NULL Values

NULL represents an unknown value. It is not an empty string (by default), or a zero value. These are all valid values, and are not NULL values.

When a table is created or the format altered, columns can be specified as accepting NULL values, or not accepting them, with the NULL and NOT NULL clauses respectively.

For example, a customer table could contain dates of birth. For some customers, this information is unknown, so the value could be NULL.

The same system could allocate a customer ID for each customer record, and in this case a NULL value would not be permitted.

are NULL until a value is explicitly assigned.

parameters and can always be set to NULL. If no DEFAULT value is specified for a local variable, its initial value will be NULL. If no value is assigned to an OUT parameter in a stored procedure, NULL is assigned at the end of the procedure.

The case of NULL is not relevant. (uppercase) is an alias for NULL.

The operator accepts UNKNOWN as an alias for NULL, which is meant for .

NULL values cannot be used with most . For example, , , , , , or cannot be used, as any comparison with a NULL always returns a NULL value, never true (1) or false (0).

To overcome this, certain operators are specifically designed for use with NULL values. To cater for testing equality between two values that may contain NULL values, there's , NULL-safe equal.

Other operators for working with NULL values include and , (for testing an expression) and (for returning the first non-NULL parameter).

When you order by a field that may contain NULL values, any NULL values are considered to have the lowest value. So ordering in DESC order will see the NULL values appearing last. To force NULL values to be regarded as highest values, one can add another column which has a higher value when the main field is NULL. Example:

Descending order, with NULL values first:

All NULL values are also regarded as equivalent for the purposes of the DISTINCT and GROUP BY clauses.

In most cases, functions will return NULL if any of the parameters are NULL. There are also functions specifically for handling NULL values. These include , and .

Aggregate functions, such as and ignore NULL values.

The one exception is , which counts rows, and doesn't look at whether a value is NULL or not. Compare for example, COUNT(x), which ignores the NULL, and COUNT(*), which counts it:

MariaDB handles NULL values in a special way if the field is an , a or a . Inserting a NULL value into a numeric AUTO_INCREMENT column will result in the next number in the auto increment sequence being inserted instead. This technique is frequently used with AUTO_INCREMENT fields, which are left to take care of themselves.

Similarly, if a NULL value is assigned to a TIMESTAMP field, the current date and time is assigned instead.

After a pause:

If a NULL is assigned to a VIRTUAL or PERSISTENT column, the default value is assigned instead.

In all these special cases, NULL is equivalent to the DEFAULT keyword.

If a NULL value is single-row inserted into a column declared as NOT NULL, an error will be returned. However, if the is not (strict is the default), if a NULL value is multi-row inserted into a column declared as NOT NULL, the implicit default for the column type will be inserted (and NOT the default value in the table definition). The implicit defaults are an empty string for string types, and the zero value for numeric, date and time types.

By default, both cases will result in an error.

Single-row insert:

Multi-row insert with not :

The specified defaults have not been used; rather, the implicit column type defaults have been inserted.

UNIQUE indexes can contain multiple NULL values. Primary keys are never nullable.

In , NULL can be used as a statement:

In , and the ignore .

When setting , empty strings and NULL values are the same thing.

For example:

_{This page is licensed: CC BY-SA / Gnu FDL}