MariaDB - Null Values

Advertisements

Previous Page
Next Page  

When working with NULL values, remember they are unknown values. They are not empty strings or zero, which are valid values. In table creation, column specifications allow for setting them to accept null values, or reject them. Simply utilize a NULL or NOT NULL clause. This has applications in cases of missing record information like an ID number.

User-defined variables have a value of NULL until explicit assignment. Stored routine parameters and local variables allow setting a value of NULL. When a local variable has no default value, it has a value of NULL.

NULL is case-insensitive, and has the following aliases −

  • UNKNOWN (a boolean value)
  • \N

NULL Operators

Standard comparison operators cannot be used with NULL (e.g., =, >, >=, <=, <, or !=) because all comparisons with a NULL value return NULL, not true or false. Comparisons with NULL or possibly containing it must use the “<=>” (NULL-SAFE) operator.

Other available operators are −

  • IS NULL − It tests for a NULL value.

  • IS NOT NULL − It confirms the absence of a NULL value.

  • ISNULL − It returns a value of 1 on discovery of a NULL value, and 0 in its absence.

  • COALESCE − It returns the first non-NULL value of a list, or it returns a NULL value in the absence of one.

Sorting NULL Values

In sorting operations, NULL values have the lowest value, so DESC order results in NULL values at the bottom. MariaDB allows for setting a higher value for NULL values.

There are two ways to do this as shown below −

SELECT column1 FROM product_tbl ORDER BY ISNULL(column1), column1;

The other way −

SELECT column1 FROM product_tbl ORDER BY IF(column1 IS NULL, 0, 1), column1 DESC;

NULL Functions

Functions generally output NULL when any parameters are NULL. However, there are functions specifically designed for managing NULL values. They are −

  • IFNULL() − If the first expression is not NULL it returns it. When it evaluates to NULL, it returns the second expression.

  • NULLIF() − It returns NULL when the compared expressions are equal, if not, it returns the first expression.

Functions like SUM and AVG ignore NULL values.

Inserting NULL Values

On insertion of a NULL value in a column declared NOT NULL, an error occurs. In default SQL mode, a NOT NULL column will instead insert a default value based on data type.

When a field is a TIMESTAMP, AUTO_INCREMENT, or virtual column, MariaDB manages NULL values differently. Insertion in an AUTO_INCREMENT column causes the next number in the sequence to insert in its place. In a TIMESTAMP field, MariaDB assigns the current timestamp instead. In virtual columns, a topic discussed later in this tutorial, the default value is assigned.

UNIQUE indices can hold many NULL values, however, primary keys cannot be NULL.

NULL Values and the Alter Command

When you use the ALTER command to modify a column, in the absence of NULL specifications, MariaDB automatically assigns values.

Advertisements