Valori NULL e la clausola GROUP BY

Abbiamo già visto come utilizzare la clausola GROUP BY e alcune funzioni di aggregazione come SUM(), AVG(), MAX(), MIN(), COUNT(). In questo articolo spiegheremo come funziona la clausola GROUP BY quando sono coinvolti valori NULL. Verrà inoltre spiegato come utilizzare i NULLcon la clausola ORDER BY.

Il modo migliore per padroneggiare GROUP BY e NULL in SQL è la pratica. Raccomando la SQL Practice a LearnSQL.it. Contiene oltre 600 esercizi pratici che vi aiuteranno a prendere confidenza con le vostre capacità.

In SQL, NULL è un indicatore speciale utilizzato per indicare che un valore di dati non esiste nel database. Per maggiori dettagli, consultate la spiegazione di Wikipedia su NULL in SQL.

Utilizzeremo la seguente employee per illustrare il funzionamento della clausola GROUP BY funziona con i valori NULL.

La clausola GROUP BY e i valori NULL

Eseguiamo una semplice query SQL con la clausola GROUP BY e i valori NULL:

SELECT department 
FROM employee 
GROUP BY department;

RISULTATI

department
1.	
2.	IT
3.	FINANCES

Nota: per chiarezza ho aggiunto un elenco numerato; di solito i risultati vengono mostrati come un elenco non numerato.

Possiamo notare che il primo valore dei risultati è un NULL rappresentato da una stringa vuota (la riga vuota prima del reparto IT). Questo spazio vuoto rappresenta tutti i valori NULL restituiti dalla clausola GROUP BY, quindi possiamo concludere che GROUP BY tratta i NULL come valori validi.

Nella prossima query, conteremo quanti dipendenti ci sono in ogni reparto, compreso il reparto "NULL":

SELECT department, count(*) 
FROM employee 
GROUP BY department;

RISULTATI

department		count(*)
1. 			2
2. IT        		3
3. FINANCES 		1

Nota: per chiarezza ho aggiunto un elenco numerato; di solito i risultati vengono mostrati come un elenco non numerato.

Analizzando i risultati precedenti da una "GROUP BY possiamo concludere che tutti i valori NULL sono raggruppati in un unico valore o bucket. In questo modo sembra che NULL sia un reparto con due dipendenti. Tuttavia, trattare i NULL in questo modo - raggruppando molti NULL in un unico bucket - non è in linea con il concetto che un valore NULL non è uguale a nessun altro valore, nemmeno a un altro NULL.

Per spiegare il motivo per cui i NULL sono raggruppati in un unico bucket, dobbiamo rivedere lo standard SQL. SQL definisce "non distinti" due valori uguali tra loro o due NULL. Questa definizione di "non distinto" consente a SQL di raggruppare e ordinare i NULL quando viene utilizzata la clausola GROUP BY (o altre parole chiave che eseguono il raggruppamento).

C'è un altro punto di confusione nel risultato precedente: il modo in cui viene rappresentato il NULL (con una riga vuota) non è chiaro. Un modo interessante per risolvere questo problema è utilizzare la funzione COALESCE, che converte i NULL in un valore specifico, ma lascia invariati gli altri valori. Vediamo la seguente query:

SELECT coalesce(department,'Unassigned department'), count(*) 
FROM employee 
GROUP BY 1;

RISULTATI

department			count(*)
IT				3
Unassigned department	        2
FINANCES			1

Funzioni aggregate e valori nulli

Finora abbiamo lavorato con i valori NULL della colonna reparto e abbiamo usato solo la clausola GROUP BY. Proviamo a eseguire alcune query utilizzando i valori NULL come parametri nelle funzioni aggregate. Per prima cosa, utilizzeremo la funzione COUNT():

SELECT COUNT(salary) as "Salaries"
FROM employee

RISULTATI

Salaries
     4

Senza la clausola DISTINCT, COUNT(salary) restituisce il numero di record con valori non NULL (2000, 2500, 2000, 1000) nella colonna stipendio. Possiamo quindi concludere che COUNT non include valori NULL.

Proviamo a utilizzare la funzione COUNT(distinct column) che conta tutti i diversi valori di una colonna. Come vengono trattati i valori NULL?

SELECT COUNT(distinct salary) as "Different Salaries"
FROM employee

RISULTATI

Different Salaries
	3

La query ha restituito un "3", ma ci sono quattro stipendi diversi: 2000, 2500, 1000 e NULL. Anche in questo caso, possiamo concludere che il NULL non è incluso nel valore risultante.

Vediamo un altro esempio, questa volta utilizzando la funzione aggregata AVG():

SELECT coalesce(department,'Unassigned department'), AVG(salary) 
FROM employee 
GROUP BY 1

RISULTATI

department			count(*)
Unassigned department    	2500
IT				1500
FINANCES			2000

Analizziamo se i valori NULL sono inclusi nella funzione AVG(). Il reparto IT ha tre dipendenti con i seguenti valori di stipendio: 2000, 1000 e NULL. Il risultato di AVG per il reparto IT è 1500, quindi è chiaro che il valore NULL non viene considerato nel calcolo della media. (Perché (1000 + 2000 ) / 2 = 1500).

La conclusione è che le medie vengono calcolate solo con valori non NULL. La regola generale è che i valori NULL non vengono considerati in nessuna funzione aggregata come SUM(), AVG(), COUNT(), MAX() e MIN(). L'eccezione a questa regola è la funzione COUNT(*) che conta tutte le righe, anche quelle con valori NULL. Ecco un esempio:

SELECT COUNT(*) as "Total Records"
FROM employee

RISULTATI

Total Records
         6

Come si vede, la funzione COUNT(*) restituisce il numero totale di record della tabella "employee", anche quelli con valori NULL in alcuni o tutti i campi.

La clausola ORDER BY e i valori NULL

Lo standard SQL non definisce esplicitamente un ordine predefinito per i valori NULL. Alcuni database come Oracle e PostgreSQL utilizzano una specifica NULLS FIRST o NULLS LAST per indicare la posizione del valore NULL. L'esempio seguente mostra questa caratteristica:

SELECT department, COUNT(*) as "Num of employees" , AVG(salary) as "Avg Dept. Salary"
FROM employee
GROUP BY department
ORDER BY department NULLS LAST

RISULTATI

department			Num of employees		Avg Dept. Salary
FINANCES			1				2000
IT				3				1500
				2				2500

Espressioni booleane che coinvolgono i NULL

Normalmente il risultato booleano è VERO o FALSO, ma è normale che le espressioni o le condizioni che includono un NULL restituiscano un risultato UNKNOWN. Il risultato UNKNOWN è trattato in dettaglio in un altro articolo pubblicato in precedenza sul nostro blog.

PROVATE VOI STESSI!

Esistono molte funzioni e caratteristiche dei database relazionali che producono un comportamento specifico quando è coinvolto un valore NULL. Potete saperne di più nel corso LearnSQL's Standard SQL Functions. Provatelo gratuitamente!