La funzione SQL Coalesce: Gestione dei valori nulli

Forse sapete già come restituire valori nulli in SQL. Ora impareremo a fare il contrario. Anche se la funzione SQL COALESCE può sembrare complessa, in realtà è molto semplice e utile. Vediamo alcuni esempi di come la funzione SQL COALESCE può essere utilizzata per lavorare con i valori NULL in SQL.

La necessità di Coalesce in SQL

Prima di analizzare in dettaglio la funzione SQL COALESCE, è necessario capire come si comportano i valori NULL nelle espressioni. In parole povere, un valore NULL indica che al momento non esiste alcun valore per una particolare voce di una colonna di una tabella.

Qualsiasi espressione che coinvolga numeri, date, stringhe o booleani e un valore NULL restituirà a sua volta NULL. Questo perché NULL rappresenta un valore sconosciuto. Poiché qualsiasi espressione che coinvolga un valore sconosciuto non può essere determinata completamente, deve naturalmente restituire NULL!

Si vedano i seguenti esempi di espressioni:

	2  +  NULL 			returns		 NULL

	2  *  NULL 			returns		 NULL

	'hello'  ||  NULL 		returns		 NULL

	NULL  +  interval '1 day' 	returns		 NULL

	TRUE and NULL	                returns		 NULL

Lavorare con le multe di transito

Per spiegare come funziona la funzione SQL COALESCE, utilizzeremo l'esempio guidato delle multe di transito. La tabella seguente memorizza l'ID dell'autista, la multa ricevuta, la data e l'ora in cui la multa è stata comminata, il livello/grado dell'infrazione commessa dall'autista e l'importo delle precedenti multe non pagate accumulate dall'autista.

Si noti che la colonna offense_level può avere valori di "Fascia A", "Fascia B" o "Fascia C". Un livello di infrazione di Fascia B indica che l'automobilista dovrà pagare 1,5 volte l'importo della multa se non paga la multa originale entro 30 giorni. La fascia C raddoppia l'importo della multa dopo 30 giorni. Un livello di infrazione di fascia A non influisce sull'importo della multa se non viene pagata entro 30 giorni.

Il mistero della multa che scompare

In primo luogo, consideriamo le multe che rientrano nel periodo di pagamento di 30 giorni. In un esempio successivo esamineremo le spese aggiuntive che possono essere addebitate all'automobilista dopo il termine di 30 giorni.

In teoria, la seguente query dovrebbe restituire il valore di ciascuna multa e il valore totale delle multe che l'automobilista deve pagare (comprese le multe precedenti non pagate).

SELECT 	driver_id,
		fine_value 			as "fine_amount", 
		fine_value + unpaid_fines 	as "total_due" 
FROM 	fines;

Tuttavia, se osserviamo l'output della query nella tabella successiva, noteremo che il primo record non contiene alcun valore per la colonna total_due! Cosa è andato storto?

Il motivo per cui ciò accade è piuttosto semplice. Poiché l'automobilista con ID 16877165 non aveva multe non pagate, il valore memorizzato nella colonna unpaid_fines per questo automobilista è stato trattato per default come NULL.

Come già detto, qualsiasi espressione che preveda un valore NULL restituisce NULL. Pertanto, l'aggiunta di fine_value a unpaid_fines produceva inavvertitamente NULL, che si traduceva in una cella vuota nella tabella dei risultati.

SQL Coalesce in soccorso

Per risolvere questo problema, utilizzeremo la funzione SQL COALESCE, che restituisce il primo valore non NULL da un elenco di argomenti. La funzione può accettare tutti gli argomenti necessari. In questo caso, però, passeremo solo due argomenti alla chiamata di funzione: unpaid_fines e il valore 0. Ecco la query:

SELECT 	driver_id,
		fine_value 						as "fine_amount", 
		fine_value + COALESCE(unpaid_fines,0)	as "total_due" 
FROM 	fines;

Spieghiamo ora come e perché funziona.

Quando viene valutato COALESCE(unpaid_fines, 0), se unpaid_fines è NULL per un particolare record della tabella, SQL salta quel valore e passa all'argomento successivo che abbiamo passato alla funzione. Ricordiamo che in questo contesto, un valore NULL indica che un conducente non ha multe non pagate.

In questo caso, il valore successivo a unpaid_fines è 0. Poiché questo è il primo valore non NULL che abbiamo incontrato muovendoci da sinistra a destra per il primo automobilista, la chiamata alla funzione restituirà semplicemente 0. Questo è esattamente ciò che vogliamo: se il valore non è NULL, la chiamata alla funzione restituirà il valore NULL. Questo è esattamente ciò che vogliamo: se un automobilista non ha multe non pagate, allora dobbiamo semplicemente considerare le sue multe non pagate come zero!

Tuttavia, se un automobilista ha avuto multe non pagate, il valore memorizzato in unpaid_fines per quel particolare record non sarà NULL. In questo caso, la chiamata a COALESCE(unpaid_fines, 0) restituirà il valore memorizzato in unpaid_fines, consentendo di aggiungere un importo non pagato non nullo alla multa corrente dell'automobilista per ottenere l'importo totale dovuto.

Scadenze di pagamento non rispettate: Addebito supplementare

Ricordiamo che se la multa non viene pagata entro 30 giorni, l'importo da pagare sarà moltiplicato per il tasso di infrazione. Nella prossima query che utilizzerà la funzione SQL COALESCE, terremo conto di offense_rate nei nostri calcoli. Guardate:

SELECT driver_id,
	     fine_value as "fine_amount", 
				COALESCE(unpaid_fines,0) as "unpaid_fines",
	     fine_value + COALESCE(unpaid_fines,0)	as "total_due" ,
	     fine_value * ofense_rate + COALESCE(unpaid_fines,0)	as "total due_after_30_days" 
FROM  fines;

Questa volta abbiamo ottenuto un valore vuoto nella quarta riga della colonna total_due_after_30_days. Si spera di aver capito che cosa dobbiamo aggiungere alla nostra query per farla funzionare correttamente.

Come in precedenza, dobbiamo fare una chiamata a COALESCE. Questa volta, dobbiamo usare l'SQL COALESCE per calcolare il moltiplicatore fine. In questo modo si terrà conto della possibilità che l'infrazione del conducente non sia stata specificata, nel qual caso l'ammenda dovrà essere trattata come se l'infrazione fosse stata classificata come Fascia A (nessuna sanzione).

Naturalmente, come secondo argomento si passerà il valore 1. In questo modo, se il valore di offense_level è NULL per un determinato automobilista, il valore dell'ammenda rimarrà semplicemente invariato (sarà moltiplicato per uno).

Ecco la query aggiornata:

SELECT driver_id,
	     fine_value 	as "fine_amount", 
                COALESCE(unpaid_fines,0)	as "upaid_fines",
	     fine_value + COALESCE(unpaid_fines,0)	as "total_due" ,
	     fine_value * COALESCE(offense_level, 1) + COALESCE(unpaid_fines,0)	as "total_due_after_30_days" 
FROM  fines;

Per l'ultimo esempio, faremo una concatenazione di stringhe per osservare cosa succede quando una delle colonne con cui lavoriamo contiene il valore NULL. Ecco la query:

SELECT	driver_id,
		reason ||' - '|| offense_level  			as “reason_and_severity”,
		fine_value + COALESCE(unpaid_fines,0)	as "total_due" 
FROM		fines

Se diamo un'occhiata al quarto record, noteremo ancora una volta un valore vuoto, questa volta nella colonna reason_and_severity. Ancora una volta, ciò è dovuto al tentativo di valutare un'espressione che contiene un valore NULL.

Se invece utilizziamo la funzione SQL COALESCE, possiamo specificare un valore da utilizzare in caso di NULL. Per i nostri scopi, passeremo la stringa 'No Band' come valore predefinito. Ecco la query aggiornata, che viene eseguita come previsto:

SELECT	driver_id,
		reason ||' - '|| COALESCE(offense_level, 'No Band')  	as "reason_and_severity",
		fine_value + COALESCE(unpaid_fines,0)		as "total_due" 
FROM		fines

Riflessioni finali sulla funzione SQL COALESCE

Non tutti i gestori di database hanno accesso alla funzione SQL COALESCE, ma tutti i database offrono funzioni simili per lavorare con i valori NULL. Funzioni come IFNULL(), NVL(), e ISNULL(), tra le altre, consentono di rilevare, sostituire o trasformare i valori NULL.

Se volete saperne di più sulle funzioni SQL, date un'occhiata al nostro corso LearnSQL.it Standard SQL Functions , in cui trattiamo molte operazioni e funzioni SQL essenziali.