Come utilizzare la funzione COALESCE() in SQL

Gli utenti di SQL si trovano spesso di fronte a valori NULL nelle loro query e devono elaborarli correttamente. La funzione COALESCE() aiuta a gestire i valori NULL. Leggete questo articolo per imparare a usare COALESCE() nelle vostre query.

Le tabelle SQL memorizzano i dati in record e i record sono composti da campi. Possono verificarsi situazioni in cui non si conosce il valore di un campo specifico. Ad esempio, supponiamo di avere una tabella con i dati di persons. Essa contiene i campi first_name, last_name e marital_status. Se non conosciamo il valore di marital_status per una determinata persona, SQL ci permette di assegnare un valore NULL a questo campo. Tuttavia, il valore NULL non significa che la persona non abbia un marital_status; significa solo che "non conosciamo quel valore".

In altre parole, SQL utilizza i valori NULL per rappresentare l'assenza di valore. Tuttavia, i NULL possono essere difficili da gestire. Per questo motivo SQL ha incluso la funzione COALESCE(), di cui parleremo in questo articolo.

Prima di affrontare i dettagli tecnici di NULL e COALESCE(), vi suggerisco di consultare il nostro corso interattivo su Standard SQL Functions. Contiene una rassegna completa dei valori NULL, delle funzioni legate a NULL e di altre funzioni comuni in SQL.

Cosa fa COALESCE()?

Nei database SQL, qualsiasi tipo di dato ammette NULL come valore valido; cioè, qualsiasi colonna può avere un valore NULL, indipendentemente dal tipo di dato. (Ovviamente, alcune colonne saranno obbligatorie (non annullabili), ma questo viene stabilito dal progettista del database, non dal tipo di dati stesso). Mostriamo un semplice esempio utilizzando la tabella persons.

Possiamo utilizzare la funzione SQL COALESCE() per sostituire il valore NULL con un semplice testo:

SELECT 	
  first_name, 
  last_name, 
  COALESCE(marital_status,'Unknown')
FROM persons

Nella query precedente, la funzione COALESCE() viene utilizzata per restituire il valore 'Unknown' solo quando marital_status è NULL. Quando marital_status non è NULL, COALESCE() restituisce il valore della colonna marital_status. In altre parole, COALESCE() restituisce il primo argomento non NULL.

Conoscere i dati di esempio

Per il resto dell'articolo, dimostreremo la funzione COALESCE() utilizzando la tabella stockche viene mostrata di seguito.

Questa tabella memorizza i record dei dati dei prodotti di un mercato e include le colonne product, brand, subcategory, category, family, units, quantity_available (lo stock attuale di questo prodotto) e minimum_to_have (la soglia in cui il mercato deve ordinare questo prodotto dai suoi fornitori).

Si noterà che alcuni prodotti hanno una sottocategoria, ma altri no. Ad esempio, il prodotto "costine di maiale" appartiene alla sottocategoria "carne di maiale" nella categoria "carne" e nella famiglia "alimenti". Il prodotto "Pomodori" appartiene alla categoria "verdure" e alla famiglia "alimenti"; non ha una sottocategoria, quindi c'è un NULL in questo campo.

Esempi di query SQL che utilizzano la funzione COALESCE()

Vediamo ora come utilizzare la funzione COALESCE() in alcuni esempi realistici.

Esempio 1: Uso di COALESCE() per sostituire NULL con un'etichetta

Vogliamo mostrare tutti i prodotti con la loro sottocategoria, categoria e famiglia. Tuttavia, ci sono alcuni prodotti con un NULL nella categoria o nella sottocategoria. Per questi prodotti, vogliamo visualizzare un testo: 'No Category' o 'No Subcategory'. Ecco la query da utilizzare:

SELECT product, 
  COALESCE(subcategory,'No Subcategory') AS subcategory,
  COALESCE(category,'No Category') AS category,
  COALESCE(family,'No Family') AS family
FROM stock

Utilizziamo la funzione COALESCE() per sostituire i valori NULL con un testo. Il risultato è visibile qui sotto:

Esempio 2: Usare COALESCE() quando si concatenano NULL e stringhe

Un problema SQL frequente legato ai valori NULL è la concatenazione di stringhe. Molte operazioni che coinvolgono valori NULL restituiscono un NULL come risultato. Se si vogliono concatenare due stringhe e una di esse è NULL, il risultato della concatenazione sarà NULL. Ecco una semplice concatenazione di testo:

SELECT 'Hello, how are you ' || 'Peter ' || '?'  AS example

Il risultato è NULL:

Tuttavia, se utilizziamo un NULL ...

SELECT 'Hello, how are you ' || null || '?'  AS example

... otteniamo:

Il risultato è NULL perché ogni concatenazione di stringhe di testo con un valore NULL restituisce un valore NULL. Per evitare questo inconveniente, si può usare la funzione COALESCE() per restituire una stringa vuota (o uno spazio) invece di un NULL. Ad esempio, supponiamo di voler ottenere un elenco dei nomi dei prodotti con il nome della marca. Possiamo scrivere la seguente query:

SELECT 
    product || ', brand: ' || COALESCE(brand, '--') AS product_brand
FROM stock

Quando una marca è NULL, inseriamo un '--' al posto di un NULL. Si noti il risultato:

Esempio 3: Utilizzare COALESCE() con più argomenti

È possibile utilizzare la funzione COALESCE() con più di due argomenti. Supponiamo di volere un report che elenchi i prodotti e le loro sottocategorie. Se la sottocategoria è NULL, vogliamo sostituire la sottocategoria con la categoria. E se sia la sottocategoria che la categoria sono NULL, vogliamo sostituirle con la famiglia del prodotto. Vediamo la query SQL:

SELECT 
   product ||' - '||
   COALESCE(subcategory, category, family, 'no product description ')
   AS product_and_subcategory
FROM stock

Stiamo utilizzando la funzione COALESCE() con quattro argomenti; verrà restituito il primo argomento non NULL, come si può vedere nel risultato sottostante:

Per maggiori dettagli su questo argomento, suggerisco l'articolo Come affrontare i NULL in SQL.

Esempio 4: Usare COALESCE() per sostituire NULL con un valore calcolato

La funzione SQL COALESCE() può essere utilizzata anche per calcolare o stimare un valore quando questo non è presente. Ad esempio, ogni prodotto ha una soglia (rappresentata dalla colonna minimum_to_have) che richiede un nuovo ordine al fornitore. Tuttavia, alcuni record potrebbero avere un valore NULL nella colonna minimum_to_have; in questo caso, possiamo definire che la soglia sarà il 50% della colonna quantity_available. La query per calcolare la stima della soglia è la seguente:

SELECT product,
  quantity_available,
  minimum_to_have,
  COALESCE(minimum_to_have, quantity_available * 0.5) AS threshold
FROM   stock

La funzione COALESCE() restituisce minimum_to_have quando il valore minimum_to_have non è NULL. Se minimum_to_have è NULL, allora COALESCE() restituirà quantity_available * 0.5

Esempio 5: Uso di COALESCE() con la clausola ROLLUP

Nel prossimo esempio, utilizzeremo la clausola ROLLUP (un'estensione di GROUP BY) per ottenere la quantità totale di prodotti per ogni sottocategoria, compreso un subtotale di prodotti per ogni categoria e famiglia. Vediamo la query:

SELECT family,
 category,
 subcategory,
 SUM(quantity_available) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

La clausola ROLLUP presuppone una gerarchia tra le colonne family, category e subcategory. Pertanto, genera tutti gli insiemi di raggruppamenti che hanno senso considerando la gerarchia: GROUP BY family, GROUP BY family, category e GROUP BY family, category, subcategory. Questo è il motivo per cui ROLLUP viene spesso utilizzato per generare subtotali e totali generali nei report.

Vediamo i risultati qui sotto:

È possibile notare alcuni NULL nel risultato precedente. Ogni NULL significa che questa colonna non era presente in GROUP BY per il calcolo della quantità in magazzino. Ad esempio, queste righe ...

... sono il risultato dell'esecuzione di GROUP BY family, category. Questo è il motivo per cui c'è un NULL sotto la colonna subcategory.

Nella prossima query, utilizzeremo la funzione COALESCE() per migliorare la leggibilità del report. Sostituiremo i valori NULL con un testo che chiarisca il motivo del NULL:

SELECT COALESCE(family,'All Families') AS family,
 COALESCE(category,'All Categories') AS category,
 COALESCE(subcategory,'All Subcategories') AS subcategory,
 SUM(quantity_available) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

Il risultato è:

Nel risultato precedente, possiamo osservare come i valori NULL siano sostituiti da testi e le righe dei report con i testi che iniziano con 'All' mostrino un subtotale in quantity_in_stock.

Vorrei suggerire l'articolo La funzione SQL COALESCE: Gestire i valori NULL se volete saperne di più.

Sfruttare COALESCE() per elaborare i NULL

In questo articolo abbiamo mostrato diversi modi per utilizzare la funzione SQL COALESCE(). Abbiamo visto come utilizzare COALESCE() per sostituire i valori NULL, come calcolare un valore alternativo e come combinare COALESCE() con la clausola ROLLUP, oltre ad altri esempi.

Prima di concludere, ho un'altra raccomandazione per voi. Il nostro foglio informativo gratuito Standard SQL Functions vi consente di trovare rapidamente informazioni dettagliate sulle funzioni integrate di SQL, sulle funzioni aggregate e su molto altro ancora. Lo uso quasi ogni giorno quando lavoro con SQL. Potete anche provare il nostro Standard SQL Functions dove potete imparare ed esercitarvi con le funzioni SQL numeriche, di testo, di data e di gestione del NULL. Aumentate le vostre competenze e aumentate le vostre risorse!