Una query viene eseguita senza errori e restituisce un numero apparentemente sensato, ma mancano alcune righe e la media è errata. Valori SQL NULL sono molto spesso la causa. NULL non si comporta come un valore normale e ciò attira sia i principianti che gli sviluppatori esperti.

Questa guida mostra esattamente come si comporta NULL. Abbiamo eseguito ogni query in SQLite 3.50 e i risultati mostrati provengono da quell’esecuzione. La maggior parte di questo comportamento è SQL standard e notiamo dove i database differiscono, come l’ordinamento NULL e il trattamento di Oracle delle stringhe vuote.

Cos’è NULL?

NULL è un indicatore che indica che un valore è mancante o sconosciuto. Non è un valore nel senso comune del termine, quindi queste sono tutte cose diverse:

La differenza conta soprattutto per gli importi. Un bonus pari a 0 significa che la persona non ha ricevuto nulla. Un bonus NULL significa che nessuno lo sa ancora.

La tabella dei campioni

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Nota che Sara ha una stringa vuota per il telefono, mentre Arjun e Ravi hanno NULL. I due vengono trattati in modo molto diverso.

Test per NULL

Ecco l’errore che quasi tutti commettono all’inizio:

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

La prima query è SQL valida e non restituisce errori. Semplicemente non restituisce nulla, perché confrontare qualsiasi cosa con NULL non è mai vero. Sara appare sotto IS NOT NULL, poiché una stringa vuota è un valore reale. In Oracle, tuttavia, una stringa vuota viene trattata come NULL, il che rappresenta un’eccezione notevole.

Logica a tre valori

Nella maggior parte dei linguaggi di programmazione, una condizione è vera o falsa. SQL aggiunge un terzo risultato: sconosciuto. Se non conosci un valore, non puoi dire se è uguale a 5. E non è noto che due valori sconosciuti siano uguali, motivo per cui NULL = NULL non è vero. Nel nostro test, tutti questi hanno restituito NULL:

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

La combinazione delle condizioni segue regole attente. Ecco i risultati della nostra esecuzione, dove 1 è vero e 0 è falso:

Righe che svaniscono da DOVE

WHERE mantiene una riga solo se la sua condizione è vera. Le righe in cui il risultato è sconosciuto vengono eliminate, insieme a quelle false. Ciò porta a risultati sorprendenti:

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

Potresti aspettarti che appaiano Arjun e Ravi, dal momento che il loro telefono certamente non è quel numero. Ma il loro telefono è sconosciuto, quindi il risultato è sconosciuto e svaniscono. Per includerli, ditelo esplicitamente: WHERE phone <> '555-0101' OR phone IS NULL. Anche la negazione non aiuta:

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

Sara e Ravi non hanno bonus, quindi non compaiono né in bonus > 400 né nel suo opposto. Questa è una cosa fondamentale da ricordare: una condizione e la sua negazione non sempre coprono ogni riga.

COALESCE: fornire un fallback

COALESCE restituisce il primo valore non NULL nel suo elenco e funziona su tutti i principali database:

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

Sara appare ancora vuota, perché una stringa vuota non è NULL. Esistono funzioni simili, come IFNULL in MySQL e SQLite, ISNULL in SQL Server e NVL in Oracle. COALESCE è la scelta portatile. Anche qui vale il nostro punto precedente sui calcoli. bonus + 100 dà NULL per chiunque non abbia un bonus, quindi scrivi COALESCE(bonus, 0) + 100 se trattare un bonus mancante come zero è corretto per la tua attività.

NULLIF: trasforma un valore in NULL

NULLIF(a, b) restituisce NULL se i due valori sono uguali, altrimenti restituisce a. Due usi sono particolarmente utili. Può ripulire i valori segnaposto:

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

La seconda query è un modo pratico per trovare valori “mancanti o vuoti”. Può anche evitare la divisione per zero, che genera un errore in molti database:

SELECT total / NULLIF(quantity, 0) FROM sales;

Quando quantity è 0, ciò fornisce NULL anziché fallire. Nel nostro test, SELECT 100 / NULLIF(0, 0) ha restituito NULL.

Gli aggregati saltano NULL

È qui che NULL causa il maggior danno ai report:

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

Allora qual è il bonus medio, 400 o 200? Dipende da cosa significa NULL nei tuoi dati. Se NULL significa “nessun bonus è stato pagato”, allora 200 è corretto. Se significa “non ancora deciso”, allora 400 potrebbe essere giusto. Il database non può prendere questa decisione per te, quindi la domanda deve essere rivolta a qualcuno che conosce l’attività.

Una trappola correlata: SUM senza valori è NULL anziché 0. Quando abbiamo sommato solo le righe senza bonus, abbiamo ottenuto NULL, mentre COUNT ha restituito 0. Il codice dell’applicazione che prevede un numero può bloccarsi su questo, quindi racchiudilo: COALESCE(SUM(bonus), 0).

GRUPPO PER, ordinamento e DISTINTO

Sebbene i NULL siano “non uguali” nei confronti, queste operazioni li trattano come un unico gruppo:

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

Vedi SQL GROUP BY e HAVING per ulteriori informazioni sul raggruppamento. L’ordinamento è il punto in cui i database differiscono davvero. SQLite e MySQL trattano NULL come il valore più piccolo, mentre PostgreSQL e Oracle lo trattano come il più grande. Quindi lo stesso ORDER BY bonus DESC può mettere i NULL per ultimi in uno e per primi in un altro. PostgreSQL, Oracle e SQLite moderno ti consentono di affermarlo direttamente, utilizzando NULLS FIRST o NULLS LAST.

Si unisce

Una condizione di join che utilizza = non corrisponde mai a NULL, quindi le righe con una chiave NULL escono da un inner join:

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera non ha un manager, quindi un inner join elimina silenziosamente quella riga. A LEFT JOIN lo mantiene e crea anche NULL per il lato mancante. Ulteriori informazioni sono disponibili in Spiegazione dei join SQL.

La trappola del NON IN

Un singolo NULL in un elenco NOT IN cancella ogni risultato:

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

La seconda forma significa manager_id <> 1 AND manager_id <> NULL e l’ultima parte non potrà mai essere vera. Ciò è particolarmente pericoloso con le subquery, dove un NULL può arrivare inosservato. Utilizza invece NOT EXISTS, come spiegato nel nostro tutorial sulle sottoquery SQL. Anche Meera manca dal primo risultato, poiché manager_id di quella riga è NULL.

Progettare con NULL

• Utilizzare NOT NULL per impostazione predefinita. Consenti NULL solo quando un valore può veramente mancare.
• Aggiungi impostazioni predefinite sensate. Ad esempio, quantity INT NOT NULL DEFAULT 0.
• Non utilizzare valori magici. Una data 01-01-1900 o un’età di -1 al posto di “sconosciuto” corromperanno i tuoi calcoli.
• Conosci il comportamento UNIQUE. La maggior parte dei database consente diversi NULL in una colonna UNIQUE e il nostro test ne ha inseriti due senza errori. SQL Server rappresenta un’eccezione, poiché normalmente ne consente una.
• Ricorda che una chiave primaria non può essere NULL. Vedi Chiave primaria e chiave esterna.
• Test con NULL. I dati di test senza NULL nascondono questi bug. Includili di proposito, come faresti con altri casi limite.

Considerazioni finali

Tratta NULL come “sconosciuto” e la maggior parte del suo comportamento inizia ad avere senso: sconosciuto più uno è sconosciuto e sconosciuto uguale sconosciuto non è noto per essere vero. Controlla uno dei tuoi rapporti questa settimana. Controlla le colonne nullable utilizzando COUNT(*) contro COUNT(column), cerca qualsiasi NOT IN e decidi cosa dovrebbe significare NULL in ciascuna media.

Domande frequenti

Continua a imparare