COALESCE (Transact-SQL)
Valuta gli argomenti seguendo l'ordine e restituisce il valore corrente della prima espressione che inizialmente non restituisce NULL.
Si applica a: SQL Server (SQL Server 2008 tramite versione corrente), Database SQL di Windows Azure (versione iniziale tramite versione corrente). |
.gif "Icona di collegamento a un argomento")
Convenzioni della sintassi Transact-SQL
Sintassi
COALESCE ( expression [ ,...n ] )
Argomenti
- expression
Espressione di qualsiasi tipo.
Tipi restituiti
Restituisce il tipo di dati di expression con la precedenza del tipo di dati più alta. Se tutte le espressioni non ammettono valori Null, il risultato non ammetterà valori Null.
Osservazioni
Se tutti gli argomenti sono NULL, COALESCE restituisce NULL. Almeno uno dei valori Null deve essere un valore NULL tipizzato.
Confronto tra COALESCE e CASE
L'espressione COALESCE è una scorciatoia sintattica dell'espressione CASE. Il codice di COALESCE(expression1,...n) viene quindi riscritto da Query Optimizer come la seguente espressione CASE:
CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END
Ciò significa che i valori di input (expression1, expression2, expressionN e via di seguito) saranno valutati più volte. Inoltre, in conformità con lo standard SQL, un'espressione di valori che contiene una sottoquery è considerata non deterministica e la sottoquery viene valutata due volte. In entrambi i casi, è possibile che tra la prima valutazione e quelle successive i risultati siano diversi.
Ad esempio, quando viene eseguito il codice COALESCE((subquery), 1), la sottoquery viene valutata due volte. Di conseguenza, è possibile che si ottengano risultati differenti a seconda del livello di isolamento della query. Ad esempio, il codice può restituire NULL con il livello di isolamento READ COMMITTED in un ambiente multiutente. Per assicurare risultati costanti, utilizzare il livello di isolamento SNAPSHOT ISOLATION oppure sostituire COALESE con la funzione ISNULL. In alternativa, è possibile riscrivere la query per eseguire il push della sottoquery in una selezione secondaria, come illustrato nell'esempio seguente.
SELECT CASE WHEN x IS NOT NULL THEN x ELSE 1 END
from
(
SELECT (SELECT Nullable FROM Demo WHERE SomeCol = 1) AS x
) AS T;
Confronto tra COALESCE e ISNULL
Le finalità della funzione ISNULL e dell'espressione COALESCE sono simili, ma i comportamenti differiscono.
Poiché ISNULL è una funzione, viene valutata una sola volta. Come descritto in precedenza, i valori di input per l'espressione COALESCE possono essere valutati più volte.
La determinazione dei tipi di dati dell'espressione risultante è differente. ISNULL utilizza il tipo di dati del primo parametro, COALESCE segue le regole dell'espressione CASE e restituisce il tipo di dati del valore con la precedenza più alta.
Il supporto dei valori NULL dell'espressione risultante è differente per ISNULL e COALESCE. Il valore restituito da ISNULL non ammette mai i valori NULL (supponendo che il valore restituito sia un valore che non ammette valori NULL), mentre COALESCE con parametri non NULL è considerata NULL. Le espressioni ISNULL(NULL, 1) e COALESCE(NULL, 1), quindi, sebbene equivalenti, hanno valori di supporto dei valori NULL differenti. Questa differenza è importante quando si utilizzano queste espressioni nelle colonne calcolate, creando vincoli di chiave o rendendo deterministico il valore restituito di una funzione scalare definita dall'utente in modo che possa essere indicizzato come mostrato nell'esempio che segue.
USE tempdb; GO -- This statement fails because the PRIMARY KEY cannot accept NULL values -- and the nullability of the COALESCE expression for col2 -- evaluates to NULL. CREATE TABLE #Demo ( col1 integer NULL, col2 AS COALESCE(col1, 0) PRIMARY KEY, col3 AS ISNULL(col1, 0) ); -- This statement succeeds because the nullability of the -- ISNULL function evaluates AS NOT NULL. CREATE TABLE #Demo ( col1 integer NULL, col2 AS COALESCE(col1, 0), col3 AS ISNULL(col1, 0) PRIMARY KEY );Anche le convalide di ISNULL e COALESCE sono differenti. Ad esempio, un valore NULL per ISNULL viene convertito in int, mentre per COALESCE è necessario fornire un tipo di dati.
ISNULL accetta solo 2 parametri, mentre COALESCE accetta un numero variabile di parametri.
Esempi
A.Esecuzione di un esempio semplice
Nell'esempio seguente viene illustrato il modo in cui COALESCE seleziona i dati dalla prima colonna in cui è presente un valore non Null. In questo esempio viene utilizzato il database AdventureWorks2012.
SELECT Name, Class, Color, ProductNumber,
COALESCE(Class, Color, ProductNumber) AS FirstNotNull
FROM Production.Product;
B.Esecuzione di un esempio complesso
Nell'esempio seguente viene illustrata una tabella wages che include tre colonne con informazioni sulla retribuzione annua dei dipendenti, ovvero retribuzione oraria, stipendio e commissione. Un dipendente tuttavia riceve un solo tipo di paga. Per determinare l'importo totale pagato a tutti i dipendenti, utilizzare la funzione COALESCE per ottenere solo i valori non Null delle colonne hourly_wage, salary e commission.
SET NOCOUNT ON;
GO
USE tempdb;
IF OBJECT_ID('dbo.wages') IS NOT NULL
DROP TABLE wages;
GO
CREATE TABLE dbo.wages
(
emp_id tinyint identity,
hourly_wage decimal NULL,
salary decimal NULL,
commission decimal NULL,
num_sales tinyint NULL
);
GO
INSERT dbo.wages (hourly_wage, salary, commission, num_sales)
VALUES
(10.00, NULL, NULL, NULL),
(20.00, NULL, NULL, NULL),
(30.00, NULL, NULL, NULL),
(40.00, NULL, NULL, NULL),
(NULL, 10000.00, NULL, NULL),
(NULL, 20000.00, NULL, NULL),
(NULL, 30000.00, NULL, NULL),
(NULL, 40000.00, NULL, NULL),
(NULL, NULL, 15000, 3),
(NULL, NULL, 25000, 2),
(NULL, NULL, 20000, 6),
(NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 40 * 52,
salary,
commission * num_sales) AS money) AS 'Total Salary'
FROM dbo.wages
ORDER BY 'Total Salary';
GO
Set di risultati:
Total Salary
------------
10000.00
20000.00
20800.00
30000.00
40000.00
41600.00
45000.00
50000.00
56000.00
62400.00
83200.00
120000.00
(12 row(s) affected)