COALESCE (Transact-SQL)

Artikel
01/29/2025

gäller för:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)SQL-analysslutpunkt i Microsoft Fabric Warehouse i Microsoft Fabric SQL-databas i Microsoft Fabric

Utvärderar argumenten i ordning och returnerar det aktuella värdet för det första uttrycket som ursprungligen inte utvärderas till NULL. I följande exempel returneras det tredje värdet eftersom det tredje värdet är det första värdet som inte är null.

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

Not

Om du vill sammanfoga strängar använder du STRING_AGG i stället.

Transact-SQL syntaxkonventioner

Syntax

COALESCE ( expression [ , ...n ] )

Argument

uttryck

Ett uttryck av vilken typ som helst.

Returtyper

Returnerar datatypen för uttryck med högsta prioritet för datatypen. Om alla uttryck inte är nullbara skrivs resultatet som icke-nullbart.

Anmärkningar

Om alla argument är NULLreturnerar COALESCENULL. Minst ett av null-värdena måste vara ett skrivet NULL.

Jämför COALESCE och CASE

Uttrycket COALESCE är en syntaktisk genväg för CASE-uttrycket. Det vill: koden COALESCE(<expression1>, ...n) skrivs om av frågeoptimeraren som följande CASE uttryck:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END

Därför utvärderas indatavärdena (expression1, expression2, expressionNoch så vidare) flera gånger. Ett värdeuttryck som innehåller en underfråga anses vara nondeterministiskt och underfrågan utvärderas två gånger. Det här resultatet är i överensstämmelse med SQL-standarden. I båda fallen kan olika resultat returneras mellan den första utvärderingen och kommande utvärderingar.

När koden COALESCE((subquery), 1) till exempel körs utvärderas underfrågan två gånger. Därför kan du få olika resultat beroende på frågans isoleringsnivå. Koden kan till exempel returnera NULL under READ COMMITTED isoleringsnivå i en miljö med flera användare. För att säkerställa att stabila resultat returneras använder du SNAPSHOT ISOLATION isoleringsnivå eller ersätter COALESCE med funktionen ISNULL. Alternativt kan du skriva om frågan för att skicka underfrågan till ett underval enligt följande exempel:

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;

Jämför COALESCE och ISNULL

Funktionen ISNULL och uttrycket COALESCE har ett liknande syfte men kan bete sig annorlunda.

Eftersom ISNULL är en funktion utvärderas den bara en gång. Som tidigare beskrivits kan indatavärdena för COALESCE-uttrycket utvärderas flera gånger.
Datatypens bestämning av det resulterande uttrycket skiljer sig. ISNULL använder datatypen för den första parametern och COALESCE följer CASE uttrycksregler för att returnera datatypen för värdet med högsta prioritet.
NULLability för resultatuttrycket skiljer sig åt för ISNULL och COALESCE. Det ISNULL returvärdet anses alltid vara NOT NULLkunna (förutsatt att returvärdet inte är nullbart). Däremot ansesCOALESCE med parametrar som inte är null vara NULL. Så uttrycken ISNULL(NULL, 1) och COALESCE(NULL, 1), även om de är lika, har olika nullabilitetsvärden. Dessa värden gör skillnad om du använder dessa uttryck i beräknade kolumner, skapar viktiga begränsningar eller gör returvärdet för en skalbar användardefinierad funktion (UDF) deterministisk, så att den kan indexeras enligt följande exempel:
```
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
);
```
Valideringar för ISNULL och COALESCE skiljer sig också. Ett NULL värde för ISNULL konverteras till int men för COALESCEmåste du ange en datatyp.
ISNULL tar bara två parametrar. Däremot tar COALESCE ett variabelt antal parametrar.

Exempel

Kodexemplen i den här artikeln använder AdventureWorks2022- eller AdventureWorksDW2022-exempeldatabasen, som du kan ladda ned från startsidan Microsoft SQL Server Samples och Community Projects.

A. Returnera data från den första kolumnen som har ett värde som inte är null

I följande exempel visas hur COALESCE väljer data från den första kolumnen som har ett värde som inte är null. Anta för det här exemplet att tabellen Products innehåller följande data:

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

Sedan kör vi följande COALESCE fråga:

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

Här är resultatuppsättningen.

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

På den första raden är värdet FirstNotNullPN1278, inte Socks, Mens. Det här värdet är på det här sättet eftersom kolumnen Name inte angavs som en parameter för COALESCE i exemplet.

B. Returnera värdet som inte är null i en lönetabell

I följande exempel innehåller tabellen wages tre kolumner som innehåller information om de anställdas årslöner: timlönen, lönen och provisionen. En anställd får dock bara en typ av lön. Om du vill fastställa det totala beloppet som betalats ut till alla anställda använder du COALESCE för att endast ta emot värdet som inte är null som finns i hourly_wage, salaryoch 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

Här är resultatuppsättningen.

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

Dela via

COALESCE (Transact-SQL)

Syntax

Argument

uttryck

Returtyper

Anmärkningar

Jämför COALESCE och CASE

Jämför COALESCE och ISNULL

Exempel

A. Returnera data från den första kolumnen som har ett värde som inte är null

B. Returnera värdet som inte är null i en lönetabell

Feedback

Ytterligare resurser

Dela via

COALESCE (Transact-SQL)

Syntax

Argument

uttryck

Returtyper

Anmärkningar

Jämför COALESCE och CASE

Jämför COALESCE och ISNULL

Exempel

A. Returnera data från den första kolumnen som har ett värde som inte är null

B. Returnera värdet som inte är null i en lönetabell

Relaterat innehåll

Feedback

Ytterligare resurser