COALESCE (Transact-SQL)

Articolo
05/23/2023

Si applica a:SQL Server database SQL di Azure Istanza gestita di SQL di Azure Azure Synapse Analytics Analytics Platform System (PDW)SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Valuta gli argomenti seguendo l'ordine e restituisce il valore corrente della prima espressione che inizialmente non restituisce NULL. Ad esempio, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); restituisce il terzo valore perché il terzo valore è il primo non Null.

Convenzioni di sintassi Transact-SQL

Sintassi

COALESCE ( expression [ ,...n ] )

Argomenti

expression
Espressione di qualsiasi tipo.

Nota

Per visualizzare la sintassi Transact-SQL per SQL Server 2014 (12.x) e versioni precedenti, vedere la documentazione delle versioni precedenti.

Tipi restituiti

Restituisce il tipo di dati dell'espressione con la precedenza del tipo di dati più alta. Se tutte le espressioni non ammettono valori Null, il risultato non ammetterà valori Null.

Osservazioni:

Quando tutti gli argomenti sono NULL, COALESCErestituisce 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 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

In questo modo, i valori di input (expression1, expression2, expressionN e così via) vengono valutati più volte. Un'espressione valore contenente una sottoquery viene considerata non deterministica e la sottoquery viene valutata due volte. Questo risultato è conforme allo standard SQL. In entrambi i casi, tra la prima valutazione e le successive possono essere restituiti risultati 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 COALESCE con la funzione ISNULL. In alternativa, è possibile riscrivere la query in modo da inserire la sottoquery in una sub-SELECT, 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.

Dato che ISNULL è una funzione, la valutazione viene eseguita 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 viene sempre considerato come non nullable, supponendo che il valore restituito non ammetta valori Null. Al contrario, l'espressione COALESCE con parametri non Null viene considerata NULL. Nonostante siano uguali, le espressioni ISNULL(NULL, 1) e COALESCE(NULL, 1) hanno quindi valori diversi in termini di supporto dei valori Null. Questi valori fanno la differenza se si usano queste espressioni in colonne calcolate, creando vincoli di chiave o rendendo deterministico il valore restituito di una funzione definita dall'utente scalare 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 due parametri. COALESCE accetta invece un numero variabile di parametri.

Esempi

R. 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 AdventureWorks2022.

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

Questo è il 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)

C: esempio semplice

Nell'esempio seguente viene illustrato come COALESCE seleziona i dati dalla prima colonna in cui è presente un valore non Null. Si supponga per questo esempio che la tabella Products contenga i dati seguenti:

Name         Color      ProductNumber  
------------ ---------- -------------  
Socks, Mens  NULL       PN1278  
Socks, Mens  Blue       PN1965  
NULL         White      PN9876

è quindi possibile eseguire la seguente query COALESCE:

SELECT Name, Color, ProductNumber, COALESCE(Color, ProductNumber) AS FirstNotNull   
FROM Products ;

Questo è il set di risultati.

Name         Color      ProductNumber  FirstNotNull  
------------ ---------- -------------  ------------  
Socks, Mens  NULL       PN1278         PN1278  
Socks, Mens  Blue       PN1965         Blue  
NULL         White      PN9876         White

Si noti che nella prima riga, il valore FirstNotNull è PN1278, non Socks, Mens. Questo valore è determinato dal fatto che la colonna Namenon è stata specificata come parametro per COALESCE nell'esempio.

D: esempio complesso

L'esempio seguente usa COALESCE per confrontare i valori in tre colonne e restituire solo il valore non null trovato nelle colonne.

CREATE TABLE dbo.wages  
(  
    emp_id        TINYINT   NULL,  
    hourly_wage   DECIMAL   NULL,  
    salary        DECIMAL   NULL,  
    commission    DECIMAL   NULL,  
    num_sales     TINYINT   NULL  
);  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (1, 10.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (2, 20.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (3, 30.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (4, 40.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (5, NULL, 10000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (6, NULL, 20000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (7, NULL, 30000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (8, NULL, 40000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (9, NULL, NULL, 15000, 3);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (10,NULL, NULL, 25000, 2);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (11, NULL, NULL, 20000, 6);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (12, NULL, NULL, 14000, 4);  
  
SELECT CAST(COALESCE(hourly_wage * 40 * 52,   
   salary,   
   commission * num_sales) AS DECIMAL(10,2)) AS TotalSalary   
FROM dbo.wages  
ORDER BY TotalSalary;

Questo è il 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

Vedi anche

ISNULL (Transact-SQL)
CASE (Transact-SQL)

COALESCE (Transact-SQL)

Sintassi

Argomenti

Tipi restituiti

Osservazioni:

Confronto tra COALESCE e CASE

Confronto tra COALESCE e ISNULL

Esempi

R. Esecuzione di un esempio semplice

B. Esecuzione di un esempio complesso

C: esempio semplice

D: esempio complesso

Vedi anche

Commenti e suggerimenti

Commenti e suggerimenti

Risorse aggiuntive