Dov’è il NULL?

Nell’esaminare dati creati da altri, o dati creati da noi stessi in tempi lontani, spesso accade di volerne ottimizzare la struttura, per migliorare l’efficienza. Un caso frequente è la ricerca di campi di chiavi esterne con valori NULL, per poter normalizzare le dipendenze da altre tabelle.

Ma comunque i campi NULL possono, quando usati in maniera irragionevole, influenzare negativamente la precisione o l’efficienza di una query. Quindi succede spesso che un programmatore si chieda: “in quali campi di questa tabella c’è almeno un NULL?”

Se la tabella è piccola, basta uno sguardo.


mysql> select * from persone;
+----+--------+---------+--------+
| id | nome | cognome | id_dep |
+----+--------+---------+--------+
| 1 | Fabio | Rossi | NULL |
| 2 | Mario | Bianchi | NULL |
| 3 | Carlo | Verdi | NULL |
| 4 | Gino | Neri | NULL |
| 5 | Ugo | Rossi | 2 |
| 6 | Piero | Verdi | 1 |
| 7 | Fulvio | Neri | NULL |
| 8 | NULL | Rossi | NULL |
| 9 | NULL | Bianchi | NULL |
+----+--------+---------+--------+
9 rows in set (0.02 sec)

Ma se la tabella ha decine di colonne e centinaia di record, il compito di esaminarla a mano è proibitivo. Tuttavia, possiamo agevolarci il compito con qualche query ad hoc. Sappiamo che la funzione COUNT restituisce un conteggio dell’elemento richiesto. Nella tabella sopra una query


mysql> SELECT count(id_dep) FROM persone;

restituisce 2. Questo ci dice che il conteggio salta i valori NULL. Se diamo alla funzione l’elemento ‘*’, però, ci viene restituito un conteggio dei record, a prescindere dal contenuto. Verifichiamo questo fatto.


mysql> select * from tuttinull;
+------+------+
| id | nome |
+------+------+
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
| NULL | NULL |
+------+------+
4 rows in set (0.01 sec)

Questa tabella ha quattro record, con NULL in tutti i campi.


mysql> select count(*) from tuttinull;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.01 sec)

Il conteggio, come annunciato, è riferito ai record. Quindi possiamo sfruttare questo fatto per vedere quali campi contengono un NULL, esaminando il conteggio dei singoli campi, e comparandolo al conteggio totale:


mysql> select count(*), count(nome), count(cognome), count(id_dep) from persone;
+----------+-------------+----------------+---------------+
| count(*) | count(nome) | count(cognome) | count(id_dep) |
+----------+-------------+----------------+---------------+
| 9 | 7 | 9 | 2 |
+----------+-------------+----------------+---------------+
1 row in set (0.02 sec)

Questo ci dice che ci sono 7 nomi su 9 record, 9 cognomi su 9, e 2 id_dep su 9. Ancora meglio, possiamo chiedere al database di fare il calcolo per noi, e darci direttamente il numero di record mancanti:


mysql> select count(*),
-> count(nome) as nome,
-> count(*) - count(nome) as `NULL nome`,
-> count(cognome) as cognome,
-> count(*) - count(cognome) as `NULL cognome`,
-> count(id_dep) as id_dep,
-> count(*) - count(id_dep) as `NULL id_dep`
-> from persone;
+----------+------+-----------+---------+--------------+--------+-------------+
| count(*) | nome | NULL nome | cognome | NULL cognome | id_dep | NULL id_dep |
+----------+------+-----------+---------+--------------+--------+-------------+
| 9 | 7 | 2 | 9 | 0 | 2 | 7 |
+----------+------+-----------+---------+--------------+--------+-------------+
1 row in set (0.02 sec)

Questo metodo funziona, ma se abbiamo una tabella con tanti campi, non è più un metodo efficiente. È possibile però ottenere lo stesso risultato con un approccio programmatico, come questo esempio in Perl dimostra:


#!/usr/bin/perl -w
use strict;
use DBI;


my $dbh = DBI->connect('dbi:mysql:test', 'utente','password')
or die "problema di connessione\n";
my $colonne = $dbh->selectcol_arrayref(qq{describe persone})
or die "colonne non trovate";


my $query = qq{select count(*), }
. join(",", map {qq{count(`$_`) as `$_`,
count(*) - count(`$_`) as `NULL $_`} } @$colonne)
. qq{ from persone};


my $sth = $dbh->prepare($query);
$sth->execute() or die "problema di esecuzione\n";


my $row = $sth->fetchrow_hashref;
printf "%-28s => %s \n", $_, $row->{$_} for @{$sth->{NAME}};
$sth->finish();

Col risultato:


count(*) => 9
id => 9
NULL id => 0
nome => 7
NULL nome => 2
cognome => 9
NULL cognome => 0
id_dep => 2
NULL id_dep => 7

NULL e non più NULL

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.