Gestione di NULL nei calcoli con COALESCE()

Oggi ci addentriamo di nuovo nel tema della gestione di NULL e conosciamo una funzione davvero utile — COALESCE(). Questa funzione ti permette di gestire in modo elegante i valori NULL nei tuoi dati.

Vediamo un esempio: hai una tabella con i dati dei dipendenti, ma per alcuni manca l'informazione sullo stipendio. Cosa succede se proviamo ad aumentare tutti gli stipendi? Niente di buono. Non puoi fare operazioni con NULL. E se invece volessimo sostituire gli stipendi NULL con, diciamo, 0? Qui entra in gioco COALESCE().

COALESCE() — è una funzione che restituisce il primo valore non-NULL dalla lista di argomenti che gli passi. Se tutti i valori nella lista sono NULL, la funzione restituisce NULL. In parole povere, è come dire: "Dammi il primo valore decente che riesci a trovare, per favore!"

Sintassi

COALESCE(value1, value2, ..., value_n)

value1, value2, ..., value_n — sono gli argomenti che passi alla funzione. Lei ti restituisce il primo valore che non è NULL.

Esempi di utilizzo di COALESCE()

Vediamo un paio di esempi.

Esempio 1: Sostituire NULL con 0

Supponiamo di avere una tabella salaries:

Vogliamo calcolare la somma totale degli stipendi - è facile:

SELECT SUM(salary) AS total_salary
FROM salaries;

La funzione SUM() ignora i NULL, quindi nessun problema.

Ma poi vogliamo calcolare la somma totale degli stipendi, se diamo a ogni dipendente un bonus di 1000.

SELECT SUM(salary+1000) AS total_salary
FROM salaries;

E il risultato inizia a sballare. Meglio eliminare subito i valori NULL usando la funzione COALESCE e sostituirli con 0. Vediamo come si fa:

SELECT SUM(COALESCE(salary, 0)) AS total_salary
FROM salaries;

Risultato:

Così è meglio e più affidabile.

Esempio 2: Sostituire NULL con un valore di default

Supponiamo di avere una tabella students con nomi e indirizzi:

Vogliamo sostituire NULL negli indirizzi con "Non specificato":

SELECT name, COALESCE(address, 'Non specificato') AS resolved_address
FROM students;

Risultato della query:

Esempio 3: Usare più valori

A volte capita che NULL vada sostituito non con un solo valore, ma con una serie di valori. Per esempio, vogliamo scegliere il nome, il nome per gli amici o usare "Senza nome" se nessuno dei due è impostato. Tabella users:

Query:

SELECT user_id,
       COALESCE(first_name, short_name, 'Senza nome') AS display_name
FROM users;

Risultato:

Applicazione pratica di COALESCE()

Nella pratica COALESCE() — è un vero salvagente quando lavori con dati non perfetti.

Vediamo come aiuta in diversi casi.

Esempio 1: Sostituzione di valori nei campi di testo

Tabella di partenza customers:

Query:

SELECT name, COALESCE(address, 'Non specificato') AS address
FROM customers;

Risultato:

Esempio 2: Preparazione dei dati per i report

Tabella di partenza sales:

Query:

SELECT SUM(COALESCE(price, 0)) AS total_sales
FROM sales;

Risultato:

Errori tipici nell'uso di COALESCE()

Anche se COALESCE() sembra una funzione semplice e universale, ci sono alcuni trabocchetti in cui puoi incappare.

Incompatibilità dei tipi di dato. Tutti gli argomenti che passi a COALESCE() devono essere compatibili come tipo di dato. Ad esempio, non puoi mischiare valori stringa e numerici.

-- Errore
SELECT COALESCE(salary, 'Non specificato') FROM employees; 
-- salary — campo numerico, e 'Non specificato' — testo.

Ignorare l'ordine degli argomenti. COALESCE() restituisce il primo valore non-NULL, quindi l'ordine degli argomenti è fondamentale.