COALESCE restituisce il primo valore della sua lista che non è NULL. È il modo in cui SQL sostituisce un valore mancante con uno di riserva. Gli esempi funzionano in PostgreSQL, MySQL, SQLite e SQL Server.
Come usare COALESCE
Passa a COALESCE quanti valori vuoi. Li controlla da sinistra a destra e restituisce il primo che non è NULL, oppure NULL se lo sono tutti:
COALESCE(value1, value2, ...)
SELECT COALESCE(NULL, 'second', 'third');
-- Output: second
SELECT COALESCE(NULL, NULL);
-- Output: NULL
In una query reale i valori sono di solito colonne, con un letterale alla fine come ultima risorsa:
-- Input: users
-- id | name | nickname
-- ---+--------------+---------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | NULL
-- 3 | Priya Shah | P
SELECT id, name, COALESCE(nickname, name) AS display_name
FROM users;
-- Output:
-- id | name | display_name
-- ---+--------------+-------------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | Tom Becker
-- 3 | Priya Shah | P
Regole da conoscere
- Si ferma al primo valore non NULL, quindi gli argomenti successivi non vengono valutati una volta trovato. Metti per primo il valore compilato più spesso.
- Ogni argomento deve avere un tipo compatibile. Mescolare un numero con del testo genera un errore in PostgreSQL e SQL Server, quindi fai prima un cast:
-- Input: products
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | NULL
SELECT name, COALESCE(stock, 'N/A') AS stock FROM products;
-- PostgreSQL: ERROR: invalid input syntax for type integer: "N/A"
-- SQL Server: Conversion failed when converting the varchar value 'N/A' to data type int.
-- (in MySQL, cast with CAST(stock AS CHAR))
SELECT name, COALESCE(CAST(stock AS VARCHAR(10)), 'N/A') AS stock FROM products;
-- Output:
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | N/A
-
Una stringa vuota non è NULL. COALESCE lascia passare
''così com'è. Avvolgi la colonna inNULLIF(column, '')per trattare anche le stringhe vuote come valori mancanti:
SELECT COALESCE('', 'No name');
-- Output: '' (an empty string)
SELECT COALESCE(NULLIF('', ''), 'No name');
-- Output: No name
Sintassi alternativa
Troverai anche scorciatoie a due argomenti: IFNULL in MySQL e SQLite, e ISNULL in SQL Server:
SELECT IFNULL(NULL, 'fallback');
-- Output: fallback
Preferisci COALESCE. Accetta un numero qualsiasi di argomenti, mentre ISNULL di SQL Server assegna al risultato il tipo del primo argomento, il che può troncare il tuo valore di riserva:
-- SQL Server
DECLARE @code VARCHAR(2) = NULL;
SELECT ISNULL(@code, 'none') AS with_isnull, COALESCE(@code, 'none') AS with_coalesce;
-- Output:
-- with_isnull | with_coalesce
-- ------------+--------------
-- no | none
Un'espressione CASE può fare lo stesso lavoro, in modo più prolisso: COALESCE(nickname, name) è una forma abbreviata di CASE WHEN nickname IS NOT NULL THEN nickname ELSE name END. Consulta la guida all'istruzione SQL CASE per quando ti serve più potenza.
Quando usare COALESCE?
Usa COALESCE ogni volta che un NULL renderebbe il risultato sbagliato, brutto o vuoto. La maggior parte degli usi rientra in uno di questi quattro casi.
Ripiegare su un'altra colonna
Quando il valore che ti serve si trova in una di più colonne, COALESCE sceglie la prima compilata: un nickname o il nome completo, un cellulare o un fisso, un indirizzo di spedizione o di fatturazione.
Usare un valore predefinito quando mancano tutti
Metti un letterale alla fine della lista e il risultato non sarà mai NULL. È quello che vuoi per report e schermate, dove “Indirizzo sconosciuto” o 0.00 si leggono meglio di uno spazio vuoto.
Colmare i vuoti con un valore calcolato
Il valore di riserva non deve essere per forza una colonna o una costante. Può essere un'espressione, come uno username o un indirizzo email costruito da altre colonne, il che rende COALESCE utile per ripulire i dati importati.
Tenere i NULL fuori dai calcoli
Qualsiasi operazione aritmetica con un NULL dà NULL, quindi uno sconto o un bonus mancante azzera l'intero totale. COALESCE(discount, 0) trasforma il valore mancante in un numero utilizzabile nel calcolo.
Esempi reali di COALESCE
Mostrare un prezzo, qualunque sia registrato
I prodotti possono avere un prezzo scontato, un prezzo normale o (prima del lancio) nessuno dei due. Controllali in ordine e usa zero come predefinito:
-- Input: products
-- product_id | name | sale_price | regular_price
-- -----------+---------------+------------+--------------
-- 1 | Desk Lamp | 19.99 | 24.99
-- 2 | Office Chair | NULL | 149.00
-- 3 | Standing Desk | NULL | NULL
SELECT product_id, name, COALESCE(sale_price, regular_price, 0.00) AS price
FROM products;
-- Output:
-- product_id | name | price
-- -----------+---------------+-------
-- 1 | Desk Lamp | 19.99
-- 2 | Office Chair | 149.00
-- 3 | Standing Desk | 0.00
Scegliere il numero di telefono migliore da chiamare
I contatti hanno fino a tre numeri. Mostra il primo disponibile e segnalalo quando non ce n'è nessuno:
-- Input: contacts
-- name | primary_phone | mobile_phone | work_phone
-- -----+---------------+--------------+-----------
-- Ada | 555-0101 | 555-0199 | NULL
-- Bo | NULL | 555-0142 | 555-0177
-- Cy | NULL | NULL | 555-0123
-- Di | NULL | NULL | NULL
SELECT name, COALESCE(primary_phone, mobile_phone, work_phone, 'No phone') AS phone
FROM contacts;
-- Output:
-- name | phone
-- -----+---------
-- Ada | 555-0101
-- Bo | 555-0142
-- Cy | 555-0123
-- Di | No phone
Generare un indirizzo email segnaposto
I dipendenti senza email registrata ne ricevono una costruita dal loro nome, così ogni riga della rubrica ha un valore:
-- Input: employees
-- employee_id | first_name | last_name | email
-- ------------+------------+-----------+-----------------
-- 1 | Grace | Hopper | ghopper@navy.mil
-- 2 | Alan | Turing | NULL
SELECT
employee_id,
first_name || ' ' || last_name AS full_name,
COALESCE(
email,
LOWER(first_name) || '.' || LOWER(last_name) || '@company.com'
) AS email
FROM employees;
-- Output:
-- employee_id | full_name | email
-- ------------+--------------+------------------------
-- 1 | Grace Hopper | ghopper@navy.mil
-- 2 | Alan Turing | alan.turing@company.com
In MySQL e SQL Server, unisci le stringhe con CONCAT() invece di ||.
Calcolare il totale di un ordine con sconti facoltativi
Senza COALESCE, una sola riga senza sconto rende sbagliato il totale dell'intero ordine, perché SUM ignora completamente le righe NULL:
-- Input: order_items
-- order_id | product | quantity | unit_price | discount
-- ---------+----------+----------+------------+---------
-- 101 | Keyboard | 1 | 49.00 | 5.00
-- 101 | Mouse | 2 | 19.50 | NULL
-- 102 | Monitor | 1 | 189.00 | NULL
-- 102 | Cable | 3 | 6.00 | 2.00
-- Wrong: lines with a NULL discount drop out of the total
SELECT order_id, SUM(quantity * unit_price - discount) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+------
-- 101 | 44.00
-- 102 | 16.00
-- Right: a missing discount counts as 0
SELECT order_id, SUM(quantity * unit_price - COALESCE(discount, 0)) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+-------
-- 101 | 83.00
-- 102 | 205.00
Altre letture su COALESCE
Su questo blog:
-
MySQL COALESCE: COALESCE a confronto con
IFNULLdi MySQL - Guida all'istruzione SQL CASE: logica condizionale oltre i controlli sui NULL
- Guida a SQL not equal: perché i confronti con NULL non corrispondono mai
- Guida a SQL GROUP BY: le aggregazioni che COALESCE alimenta spesso
- Guida a SQL SUBSTRING: estrarre testo da colonne che possono essere NULL
Documentazione ufficiale:
Beekeeper Studio è una GUI per database gratuita & open source
Il miglior strumento per query & editor SQL che abbia mai usato. Offre tutto ciò che mi serve per gestire il mio database. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio è veloce, intuitivo e facile da usare. Beekeeper supporta tantissimi database e funziona alla grande su Windows, Mac e Linux.
Cosa dicono gli utenti di Beekeeper Studio
"Beekeeper Studio ha sostituito completamente il mio vecchio flusso di lavoro SQL. È veloce, intuitivo e rende di nuovo piacevole lavorare con i database."
"Ho provato molte GUI per database, ma Beekeeper trova l'equilibrio perfetto tra funzionalità e semplicità. Funziona e basta."