COALESCE (Transact-SQL)COALESCE (Transact-SQL)

SI APPLICA A: sìSQL Server (a partire dalla versione 2008) sìDatabase SQL di AzuresìAzure SQL Data Warehouse sìParallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Valuta gli argomenti seguendo l'ordine e restituisce il valore corrente della prima espressione che inizialmente non restituisce NULL.Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. Ad esempio, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); restituisce il terzo valore perché il terzo valore è il primo valore non Null.For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that is not null.

Icona di collegamento a un argomentoConvenzioni della sintassi Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintassiSyntax

COALESCE ( expression [ ,...n ] )   

ArgomentiArguments

expressionexpression
Espressione di qualsiasi tipo.Is an expression of any type.

Tipi restituitiReturn Types

Restituisce il tipo di dati dell'espressione con la precedenza del tipo di dati più alta.Returns the data type of expression with the highest data type precedence. Se tutte le espressioni non ammettono valori Null, il risultato non ammetterà valori Null.If all expressions are nonnullable, the result is typed as nonnullable.

RemarksRemarks

Quando tutti gli argomenti sono NULL, COALESCErestituisce NULL.If all arguments are NULL, COALESCE returns NULL. Almeno uno dei valori Null deve essere un valore NULL tipizzato.At least one of the null values must be a typed NULL.

Confronto tra COALESCE e CASEComparing COALESCE and CASE

L'espressione COALESCE è una scorciatoia sintattica dell'espressione CASE.The COALESCE expression is a syntactic shortcut for the CASE expression. Il codice COALESCE(expression1,...n) viene quindi riscritto da Query Optimizer come la seguente espressione CASE:That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:

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, expressionNe così via) vengono valutati più volte.This means that the input values (expression1, expression2, expressionN, etc.) are evaluated multiple times. 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.Also, in compliance with the SQL standard, a value expression that contains a subquery is considered non-deterministic and the subquery is evaluated twice. In entrambi i casi, è possibile che tra la prima valutazione e quelle successive i risultati siano diversi.In either case, different results can be returned between the first evaluation and subsequent evaluations.

Ad esempio, quando viene eseguito il codice COALESCE((subquery), 1), la sottoquery viene valutata due volte.For example, when the code COALESCE((subquery), 1) is executed, the subquery is evaluated twice. Di conseguenza, è possibile che si ottengano risultati differenti a seconda del livello di isolamento della query.As a result, you can get different results depending on the isolation level of the query. Ad esempio, il codice può restituire NULL con il livello di isolamento READ COMMITTED in un ambiente multiutente.For example, the code can return NULL under the READ COMMITTED isolation level in a multi-user environment. Per assicurare risultati costanti, utilizzare il livello di isolamento SNAPSHOT ISOLATION oppure sostituire COALESCE con la funzione ISNULL.To ensure stable results are returned, use the SNAPSHOT ISOLATION isolation level, or replace COALESCE with the ISNULL function. In alternativa, è possibile riscrivere la query per eseguire il push della sottoquery in una selezione secondaria, come illustrato nell'esempio seguente:Alternatively, you can rewrite the query to push the subquery into a subselect as shown in the following example:

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 ISNULLComparing COALESCE and ISNULL

Le finalità della funzione ISNULL e dell'espressione COALESCE sono simili, ma i comportamenti differiscono.The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

  1. Poiché ISNULL è una funzione, viene valutata una sola volta.Because ISNULL is a function, it is evaluated only once. Come descritto in precedenza, i valori di input per l'espressione COALESCE possono essere valutati più volte.As described above, the input values for the COALESCE expression can be evaluated multiple times.

  2. La determinazione dei tipi di dati dell'espressione risultante è differente.Data type determination of the resulting expression is different. 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.ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

  3. Il supporto dei valori NULL dell'espressione risultante è differente per ISNULL e COALESCE.The NULLability of the result expression is different for ISNULL and 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.The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. Le espressioni ISNULL(NULL, 1) e COALESCE(NULL, 1), quindi, sebbene equivalenti, hanno valori di supporto dei valori NULL differenti.So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equivalent, have different nullability values. 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:This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example:

    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   
    );  
    
  4. Anche le convalide per ISNULL e COALESCE sono diverse.Validations for ISNULL and COALESCE are also different. Ad esempio, un valore NULL per ISNULL viene convertito in int, mentre per COALESCE è necessario fornire un tipo di dati.For example, a NULL value for ISNULL is converted to int whereas for COALESCE, you must provide a data type.

  5. ISNULL accetta solo due parametri, mentre COALESCE accetta un numero variabile di parametri.ISNULL takes only two parameters whereas COALESCE takes a variable number of parameters.

EsempiExamples

A.A. Esecuzione di un esempio sempliceRunning a simple example

Nell'esempio seguente viene illustrato il modo in cui COALESCE seleziona i dati dalla prima colonna in cui è presente un valore non Null.The following example shows how COALESCE selects the data from the first column that has a nonnull value. In questo esempio viene utilizzato il database AdventureWorks2012AdventureWorks2012.This example uses the AdventureWorks2012AdventureWorks2012 database.

SELECT Name, Class, Color, ProductNumber,  
COALESCE(Class, Color, ProductNumber) AS FirstNotNull  
FROM Production.Product;  

B.B. Esecuzione di un esempio complessoRunning a complex example

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.In the following example, the wages table includes three columns that contain information about the yearly wages of the employees: the hourly wage, salary, and commission. Un dipendente tuttavia riceve un solo tipo di paga.However, an employee receives only one type of pay. 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.To determine the total amount paid to all employees, use COALESCE to receive only the nonnull value found in hourly_wage, salary, and 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:Here is the result set.

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 sempliceC: Simple Example

Nell'esempio seguente viene illustrato come COALESCE seleziona i dati dalla prima colonna in cui è presente un valore non Null.The following example demonstrates how COALESCE selects the data from the first column that has a non-null value. Si supponga per questo esempio che la tabella Products contenga i dati seguenti:Assume for this example that the Products table contains this data:

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

è quindi possibile eseguire la seguente query COALESCE:We then run the following COALESCE query:

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

Set di risultati:Here is the result set.

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.Notice that in the first row, the FirstNotNull value is PN1278, not Socks, Mens. Ciò è dovuto al fatto che la colonna Namenon è stata specificata come parametro per COALESCE nell'esempio.This is because the Name column was not specified as a parameter for COALESCE in the example.

D: esempio complessoD: Complex Example

L'esempio seguente usa COALESCE per confrontare i valori in tre colonne e restituire solo il valore non Null trovato nelle colonne.The following example uses COALESCE to compare the values in three columns and return only the non-null value found in the columns.

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;  

Set di risultati:Here is the result set.

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

Vedere ancheSee Also

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