COALESCE (Transact-SQL)

Articolo
01/29/2025

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure endpoint di analisi SQL di Azure Synapse AnalyticsPlatform System (PDW)in Microsoft FabricWarehouse nel database SQL di Microsoft Fabric in Microsoft Fabric

Valuta gli argomenti seguendo l'ordine e restituisce il valore corrente della prima espressione che inizialmente non restituisce NULL. Nell'esempio seguente viene restituito il terzo valore perché il terzo valore è il primo valore che non è Null.

SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value');

Nota

Se si desidera concatenare stringhe, usare invece STRING_AGG.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

COALESCE ( expression [ , ...n ] )

Argomenti

expression

Espressione di qualsiasi tipo.

Tipi restituiti

Restituisce il tipo di dati dell'espressione con la precedenza del tipo di dati più alta. Se tutte le espressioni non sono nullable, il risultato viene digitato come non nullable.

Osservazioni:

Quando tutti gli argomenti sono NULL, COALESCErestituisce NULL. Almeno uno dei valori Null deve essere un valore NULL tipizzato.

Confrontare COALESCE e CASE

L'espressione COALESCE è una scorciatoia sintattica dell'espressione CASE. Ovvero, il codice COALESCE(<expression1>, ...n) viene riscritto da Query Optimizer come espressione CASE seguente:

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 di valore che contiene 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;

Confrontare 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 usa il tipo di dati del primo parametro e COALESCE segue le regole dell'espressione CASE per restituire 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 ISNULL viene sempre considerato NOT NULLin grado (presupponendo che il valore restituito sia un valore non nullable). 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 nelle colonne calcolate, creando vincoli di chiave o rendendo il valore restituito di una funzione definita dall'utente scalare deterministica, in modo che possa essere indicizzata 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 INT 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 INT 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

Gli esempi di codice in questo articolo usano il database di esempio AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page Microsoft SQL Server Samples and Community Projects.

R. Restituire dati dalla prima colonna con un valore non Null

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

Viene quindi eseguita la query di COALESCE seguente:

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

Il set di risultati è il seguente.

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

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.

B. Restituisce il valore diverso da null in una tabella salari

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, usare COALESCE per ricevere solo il valore non Null trovato in hourly_wage, salarye 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

Il set di risultati è il seguente.

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

Condividi tramite

COALESCE (Transact-SQL)

Sintassi

Argomenti

expression

Tipi restituiti

Osservazioni:

Confrontare COALESCE e CASE

Confrontare COALESCE e ISNULL

Esempi

R. Restituire dati dalla prima colonna con un valore non Null

B. Restituisce il valore diverso da null in una tabella salari

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

COALESCE (Transact-SQL)

Sintassi

Argomenti

expression

Tipi restituiti

Osservazioni:

Confrontare COALESCE e CASE

Confrontare COALESCE e ISNULL

Esempi

R. Restituire dati dalla prima colonna con un valore non Null

B. Restituisce il valore diverso da null in una tabella salari

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive