COALESCE (Transact-SQL)
Restituisce gli argomenti nell'ordine e restituisce il valore corrente della prima espressione che inizialmente non restituisce NULL.
Si applica a: SQL Server (da SQL Server 2008 alla versione corrente), Database SQL di Windows Azure (dalla versione iniziale alla 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 è un collegamento sintattico per l'espressione CASE. Ciò significa che il codice COALESCE(expression1,...n) viene riscritto da Query Optimizer come l'espressione CASE seguente:
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 così via) verranno restituiti più volte. Inoltre, in conformità con lo standard SQL, un'espressione valore che contiene una sottoquery viene considerata non deterministica e la sottoquery viene restituita due volte. In entrambi i casi, è possibile che vengano restituiti risultati diversi tra la prima valutazione e quelle successive.
Ad esempio, quando il codice COALESCE((subquery), 1) viene eseguito, la sottoquery viene valutata due volte. Di conseguenza, è possibile ottenere risultati diversi 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 garantire risultati stabili, utilizzare il livello di isolamento SNAPSHOT ISOLATION oppure sostituire COALESE con la funzione ISNULL. In alternativa, è possibile riscrivere la query per inserire la sottoquery in un'istruzione subselect 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
La funzione ISNULL e l'espressione COALESCE hanno uno scopo simile, ma possono comportarsi in modo diverso.
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 del tipo di dati dell'espressione risultante è diversa. 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ù elevata.
Il supporto di valori NULL dell'espressione risultato è diverso per ISNULL e COALESCE. Il valore restituito di ISNULL NON ammette mai valori NULL (supponendo che il valore restituito non ammetta valori Null), mentre COALESCE con parametri non Null viene considerato NULL. Pertanto, le espressioni ISNULL(NULL, 1) e COALESCE(NULL, 1), sebbene equivalenti, presentano valori di supporto dei valori Null diversi. Questo fa la differenza quando queste espressioni vengono utilizzate nelle colonne calcolate, per creare vincoli chiave o per rendere deterministico il valore restituito di una funzione scalare definita dall'utente in modo che possa essere indicizzato come illustrato nell'esempio seguente.
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 per ISNULL e COALESCE sono diverse. Ad esempio, un valore NULL per ISNULL viene convertito in int, mentre per COALESCE, è necessario specificare 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 la tabella wages che include tre colonne contenenti 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)