Un NULL in SQL è un valore speciale che indica l’assenza di valore. Per esempio, se un campo “figli” contiene 0 (zero), significa che non ci sono figli, mentre se il campo contiene NULL significa che non sappiamo se ci siano figli o meno. Un campo “età” a 0 indica una persona appena nata, mentre lo stesso campo a NULL indica che la persona non ha dichiarato l’età.
Stabilita questa differenza semantica, occorre anche sapere che in SQL il campo NULL è virale, nel senso che trasforma in NULL tutto ciò con cui viene a contatto.
mysql> SELECT 1 + NULL, 1 * NULL, 1 / NULL, 1 - NULL;
+----------+----------+----------+----------+
| 1 + NULL | 1 * NULL | 1 / NULL | 1 - NULL |
+----------+----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+----------+----------+----------+
1 row in set (0.03 sec)
Esiste una sintassi speciale per confrontare i campi con un valore NULL. Non si può usare l’operatore “=”, ma si usa la sintassi IS [NOT] NULL.
mysql> SELECT * FROM prova;
+------+------+
| i | a |
+------+------+
| 1 | a |
| 2 | NULL |
| 3 | b |
+------+------+
3 rows in set (0.03 sec)
mysql> SELECT * FROM prova where a = NULL;
Empty set (0.02 sec)
Notate che la sintassi “a = NULL” non sortisce i risultati voluti. Usando il gergo appropriato, però, otteniamo quel che ci serve.
mysql> SELECT * FROM prova where a IS NULL;
+------+------+
| i | a |
+------+------+
| 2 | NULL |
+------+------+
1 row in set (0.38 sec)
mysql> SELECT * FROM prova where a IS NOT NULL;
+------+------+
| i | a |
+------+------+
| 1 | a |
| 3 | b |
+------+------+
2 rows in set (0.02 sec)
Fin qui, è SQL standard. Potrebbe sembrare strano a chi è digiuno del linguaggio, ma per gli abitués del database è pane quotidiano. Però MySQL ha alcune estensioni nel trattamento del NULL che mettono in difficoltà anche gli esperti di altri dialetti SQL.
La prima anomalia si ha nei campi AUTO_INCREMENT e TIMESTAMP. Una tabella può avere un campo dichiarato AUTO_INCREMENT, il che significa che, in assenza di un valore esplicito, il campo assume il primo valore disponibile al di sopra del valore più alto già presente nella tabella. Molto comodo per creare un campo con valore univoco, ma la sorpresa arriva quando si considera un codice come questo:
mysql> CREATE TABLE prova (
i INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a CHAR(10)
);
mysql> INSERT INTO prova VALUES (NULL, 'abc');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO prova VALUES (NULL, 'abc');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM prova;
+---+------+
| i | a |
+---+------+
| 1 | abc |
| 2 | abc |
+---+------+
2 rows in set (0.01 sec)
Benché la colonna “i” sia dichiarata “NOT NULL”, noi ci abbiamo inserito deliberatamente un campo NULL, e il risultato è che MySQL ha creato per noi un campo con un valore crescente. Avremmo ottenuto lo stesso risultato con questa sintassi:
INSERT INTO prova (a) VALUES ('abc');
Infatti, NULL è l’assenza di valore, pertanto l’omissione del campo “i” dalla lista dei campi fa scattare il meccanismo di AUTO_INCREMENT. Si noti che io posso anche inserire un valore esplicito
mysql> INSERT INTO prova VALUES (100, 'abc');
Query OK, 1 row affected (0.01 sec)
Se questo valore è più alto del massimo valore presente nella tabella, verrà alterato il meccanismo di generazione del numero incrementato.
mysql> INSERT INTO prova VALUES (NULL, 'abc');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM prova;
+-----+------+
| i | a |
+-----+------+
| 1 | abc |
| 2 | abc |
| 100 | abc |
| 101 | abc |
+-----+------+
4 rows in set (0.01 sec)
Dopo che è stato inserito il numero 100, l’inserimento successivo prende il valore 101. Se invece inseriamo un numero inferiore al massimo, il meccanismo di generazione non viene alterato, come mostrato dalla sequenza che segue.
mysql> INSERT INTO prova VALUES (50, 'abc');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO prova VALUES (NULL, 'abc');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM prova;
+-----+------+
| i | a |
+-----+------+
| 1 | abc |
| 2 | abc |
| 100 | abc |
| 101 | abc |
| 50 | abc |
| 102 | abc |
+-----+------+
6 rows in set (0.01 sec)
Un meccanismo simile esiste per i campi TIMESTAMP che, quando ricevono un NULL, generano il valore della data e ora attuale.
mysql> INSERT INTO prova VALUES (50, 'abc');
mysql> CREATE TABLE provaora (
a char(10),
ts TIMESTAMP
);
mysql> INSERT INTO provaora VALUES ('abc', NULL);
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO provaora (a) VALUES ('abc');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM provaora;
+------+---------------------+
| a | ts |
+------+---------------------+
| abc | 2004-02-06 17:48:38 |
| abc | 2004-02-06 17:48:39 |
+------+---------------------+
2 rows in set (0.01 sec)
(Ho esitato un attimo fra i due inserimenti, per dar modo al sistema di differenziare i due record, che altrimenti avrebbero avuto lo stesso timestamp).
Anche nei campi TIMESTAMP si può inserire un valore esplicito, ma a differenza di quanto avviene per i campi AUTO_INCREMENT, questo fatto non ha influenza sulle successive generazioni di orario. Ogni valore NULL (o assenza di valore) passato a quel campo causerà la registrazione dell’orario attuale.
Ci sono altri sottili trabocchetti legati ai NULL, ma ne parleremo in un’altra occasione.