Valori NULL in MariaDB

NULL rappresenta un valore sconosciuto. Esso non è una stringa vuota o un valore zero. Questi sono tutti valori validi, non NULL.

Quando una tabella viene https://mariadb.com/kb/it/create-table/ o modificata, si può specificare che le colonne accettano i valori NULL o che non li accettano, usando rispettivamente le clausole NULL e NOT NULL.

Per esempio, una tabelle cliente può contenere le date di nascita. Per alcuni clienti questa informazione è sconosciuta, perciò il valore può essere NULL.

Lo stesso sistema potrebbe allocare un ID cliente per ogni record, e in questo caso il valore NULL non è ammesso.

CREATE TABLE customer (
 id INT NOT NULL, 
 date_of_birth DATE NULL
...
)

Comparison Operators

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

SELECT NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+

SELECT 99 = NULL;
+-----------+
| 99 = NULL |
+-----------+
|      NULL |
+-----------+

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

SELECT 99 <=> NULL;
+-------------+
| 99 <=> NULL |
+-------------+
|           0 |
+-------------+

Other operators for working with NULLs include IS NULL and IS NOT NULL, ISNULL (for testing an expression) and COALESCE (for returning the first non-NULL parameter).

Ordering

When you order by a field that may contain NULL values, any NULLs are considered to have the lowest value. So ordering in DESC order will see the NULLs appearing last.

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

Functions

In most cases, functions will return NULL any of the parameters is NULL. There are also functions specifically for handling NULLs. These include IFNULL() and NULLIF().

Aggregate functions, such as SUM and AVG ignore NULLs.

CREATE TABLE t(x INT);

INSERT INTO t VALUES (1),(9),(NULL);

SELECT SUM(x) FROM t;
+--------+
| SUM(x) |
+--------+
|     10 |
+--------+

SELECT AVG(x) FROM t;
+--------+
| AVG(x) |
+--------+
| 5.0000 |
+--------+

The one exception is COUNT(*), 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:

SELECT COUNT(x) FROM t;
+----------+
| COUNT(x) |
+----------+
|        2 |
+----------+

SELECT COUNT(*) FROM t;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

AUTO_INCREMENT and TIMESTAMP

MariaDB handles NULL values in a special way if the field is an AUTO_INCREMENT or a TIMESTAMP. 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.

CREATE TABLE t2(id INT PRIMARY KEY AUTO_INCREMENT, letter CHAR(1));

INSERT INTO t2(letter) VALUES ('a'),('b');

SELECT * FROM t2;
+----+--------+
| id | letter |
+----+--------+
|  1 | a      |
|  2 | b      |
+----+--------+

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

CREATE TABLE t3 (x INT, ts TIMESTAMP);

INSERT INTO t3(x) VALUES (1),(2);

After a pause,

INSERT INTO t3(x) VALUES (3);

SELECT* FROM t3;
+------+---------------------+
| x    | ts                  |
+------+---------------------+
|    1 | 2013-09-05 10:14:18 |
|    2 | 2013-09-05 10:14:18 |
|    3 | 2013-09-05 10:14:29 |
+------+---------------------+