Come utilizzare la funzione COALESCE() in SQL
Indice
- Cosa fa COALESCE()?
- Conoscere i dati di esempio
-
Esempi di query SQL che utilizzano la funzione COALESCE()
- Esempio 1: Uso di COALESCE() per sostituire NULL con un'etichetta
- Esempio 2: Usare COALESCE() quando si concatenano NULL e stringhe
- Esempio 3: Utilizzare COALESCE() con più argomenti
- Esempio 4: Usare COALESCE() per sostituire NULL con un valore calcolato
- Esempio 5: Uso di COALESCE() con la clausola ROLLUP
- Sfruttare COALESCE() per elaborare i NULL
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.
| first_name | last_name | marital_status |
|---|---|---|
| Charles | Leclerc | single |
| Fernando | Alonso | married |
| George | Graue | NULL |
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.
| product | brand | subcategory | category | family | units | quantity_available | minimum_to_have |
|---|---|---|---|---|---|---|---|
| pork ribs | NULL | pork meat | meat | food | Kilos | 400 | 130 |
| tomatoes | Mr Red | NULL | vegetables | food | Kilos | 280 | 100 |
| lettuce | NULL | Leaf vegetables | NULL | food | Kilos | 280 | 125 |
| bananas | Big Brasil | NULL | vegetables | food | Kilos | 450 | 150 |
| hamburger | MaxBurg | cow meat | meat | food | Box | 245 | 100 |
| hamburger | RoyalBurg | cow meat | meat | food | Box | 125 | NULL |
| hamburger | SuperBurga | NULL | NULL | NULL | Box | 200 | 80 |
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 familyFROM stock |
Utilizziamo la funzione COALESCE() per sostituire i valori NULL con un testo. Il risultato è visibile qui sotto:
| product | subcategory | category | family |
|---|---|---|---|
| pork ribs | pork meat | meat | food |
| tomatoes | No Subcategory | vegetables | food |
| lettuce | Leaf vegetables | No Category | food |
| bananas | No Subcategory | vegetables | food |
| hamburger | cow meat | meat | food |
| hamburger | cow meat | meat | food |
| hamburger | No Subcategory | No Category | No Family |
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:
| example |
|---|
| Hello, how are you Peter ? |
Tuttavia, se utilizziamo un NULL ...
SELECT 'Hello, how are you ' || null || '?' AS example |
... otteniamo:
| example |
|---|
| NULL |
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_brandFROM stock |
Quando una marca è NULL, inseriamo un '--' al posto di un NULL. Si noti il risultato:
| product_brand |
|---|
| pork ribs, brand: -- |
| tomatoes, brand: Mr Red |
| lettuce, brand: -- |
| bananas, brand: Big Brazil |
| hamburger, brand: MaxBurg |
| hamburger, brand: RoyalBurg |
| hamburger, brand: SuperBurga |
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_subcategoryFROM stock |
Stiamo utilizzando la funzione COALESCE() con quattro argomenti; verrà restituito il primo argomento non NULL, come si può vedere nel risultato sottostante:
| product_and_subcategory |
|---|
| pork ribs - pork meat |
| tomatoes - vegetables |
| lettuce - leaf vegetables |
| Bananas - vegetables |
| hamburger - cow meat |
| hamburger - cow meat |
| hamburger - no product description |
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 thresholdFROM 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
| product | quantity_available | minimum_to_have | threshold |
|---|---|---|---|
| pork ribs | 400 | 130 | 130 |
| tomatoes | 280 | NULL | 140 |
| lettuce | 280 | 125 | 125 |
| bananas | 450 | 150 | 150 |
| hamburger | 245 | 100 | 100 |
| hamburger | 125 | 100 | 100 |
| hamburger | 200 | 80 | 80 |
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 stockGROUP 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:
| family | category | subcategory | quantity_in_stock |
|---|---|---|---|
| food | meat | cow meat | 570 |
| food | meat | pork meat | 400 |
| food | meat | NULL | 970 |
| food | vegetables | leaf vegetables | 280 |
| food | vegetables | non leaf vegetables | 730 |
| food | vegetables | NULL | 1010 |
| food | NULL | NULL | 1980 |
| NULL | NULL | NULL | 1980 |
È 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 ...
| food | meat | NULL | 770 |
|---|---|---|---|
| food | vegetables | NULL | 1010 |
... 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 stockGROUP BY ROLLUP(family, category, subcategory)ORDER BY family, category, subcategory |
Il risultato è:
| family | category | subcategory | quantity_in_stock |
|---|---|---|---|
| food | meat | Cow meat | 570 |
| food | meat | Pork meat | 400 |
| food | meat | All Sub-Categories | 970 |
| food | vegetables | Leaf vegetables | 280 |
| food | vegetables | Non leaf vegetables | 730 |
| food | vegetables | All Subcategories | 1010 |
| food | All Categories | All Subcategories | 1980 |
| All Families | All Categories | All Subcategories | 1980 |
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!
