COALESCE (Transact-SQL)

Artikel
01/29/2025

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)SQL Analytics-eindpunt in Microsoft Fabric Warehouse in Microsoft Fabric SQL-database in Microsoft Fabric

Evalueert de argumenten in volgorde en retourneert de huidige waarde van de eerste expressie die in eerste instantie niet resulteert in NULL. In het volgende voorbeeld wordt de derde waarde geretourneerd omdat de derde waarde de eerste waarde is die niet null is.

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

Notitie

Als u tekenreeksen wilt samenvoegen, gebruikt u in plaats daarvan STRING_AGG.

Transact-SQL syntaxisconventies

Syntaxis

COALESCE ( expression [ , ...n ] )

Argumenten

expressie

Een expressie van elk type.

Retourtypen

Retourneert het gegevenstype van expressie met de hoogste prioriteit voor het gegevenstype. Als alle expressies niet nullbaar zijn, wordt het resultaat getypt als niet-nullbaar.

Opmerkingen

Als alle argumenten NULLzijn, geeft COALESCENULLals resultaat. Ten minste één van de null-waarden moet een getypte NULLzijn.

COALESCE en CASE vergelijken

De COALESCE-expressie is een syntactische snelkoppeling voor de CASE-expressie. Dat wil gezegd: de code COALESCE(<expression1>, ...n) wordt herschreven door de queryoptimalisatie als de volgende CASE-expressie:

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

Als zodanig worden de invoerwaarden (expression1, expression2, expressionNenzovoort) meerdere keren geëvalueerd. Een waardeexpressie die een subquery bevat, wordt beschouwd als niet-deterministisch en de subquery wordt tweemaal geëvalueerd. Dit resultaat voldoet aan de SQL-standaard. In beide gevallen kunnen verschillende resultaten worden geretourneerd tussen de eerste evaluatie en toekomstige evaluaties.

Wanneer de code COALESCE((subquery), 1) bijvoorbeeld wordt uitgevoerd, wordt de subquery tweemaal geëvalueerd. Als gevolg hiervan kunt u verschillende resultaten krijgen, afhankelijk van het isolatieniveau van de query. De code kan bijvoorbeeld NULL retourneren onder het READ COMMITTED isolatieniveau in een omgeving met meerdere gebruikers. Gebruik het SNAPSHOT ISOLATION isolatieniveau of vervang COALESCE door de functie ISNULL om ervoor te zorgen dat stabiele resultaten worden geretourneerd. Als alternatief kunt u de query herschrijven om de subquery naar een subselectie te pushen, zoals wordt weergegeven in het volgende voorbeeld:

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;

COALESCE en ISNULL vergelijken

De ISNULL-functie en de COALESCE-expressie hebben een vergelijkbaar doel, maar kunnen zich anders gedragen.

Omdat ISNULL een functie is, wordt deze slechts één keer geëvalueerd. Zoals eerder beschreven, kunnen de invoerwaarden voor de COALESCE-expressie meerdere keren worden geëvalueerd.
De bepaling van het gegevenstype van de resulterende expressie verschilt. ISNULL gebruikt het gegevenstype van de eerste parameter en COALESCE volgt de CASE expressieregels om het gegevenstype van de waarde met de hoogste prioriteit te retourneren.
De nulwaarde van de resultaatexpressie verschilt voor ISNULL en COALESCE. De ISNULL retourwaarde wordt altijd beschouwd als NOT NULLin staat (ervan uitgaande dat de retourwaarde een niet-null-waarde is). COALESCE met niet-null-parameters wordt daarentegen beschouwd als NULL. De expressies ISNULL(NULL, 1) en COALESCE(NULL, 1), hoewel gelijk, hebben verschillende null-waarden. Deze waarden maken een verschil als u deze expressies gebruikt in berekende kolommen, sleutelbeperkingen maakt of de retourwaarde maakt van een scalaire door de gebruiker gedefinieerde functie (UDF), zodat deze kan worden geïndexeerd, zoals wordt weergegeven in het volgende voorbeeld:
```
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
);
```
Validaties voor ISNULL en COALESCE verschillen ook. Een NULL-waarde voor ISNULL wordt bijvoorbeeld geconverteerd naar int, maar voor COALESCEmoet u een gegevenstype opgeven.
ISNULL heeft slechts twee parameters nodig. Daarentegen neemt COALESCE een variabel aantal parameters.

Voorbeelden

De codevoorbeelden in dit artikel gebruiken de AdventureWorks2022 of AdventureWorksDW2022 voorbeelddatabase, die u kunt downloaden van de Microsoft SQL Server-voorbeelden en communityprojecten startpagina.

Een. Gegevens retourneren uit de eerste kolom met een niet-null-waarde

In het volgende voorbeeld ziet u hoe COALESCE de gegevens selecteert uit de eerste kolom met een niet-null-waarde. Stel voor dit voorbeeld dat de Products tabel deze gegevens bevat:

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

Vervolgens voeren we de volgende COALESCE query uit:

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

Dit is de resultatenset.

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

In de eerste rij is de FirstNotNull waarde PN1278, niet Socks, Mens. Deze waarde is op deze manier omdat de kolom Name niet is opgegeven als parameter voor COALESCE in het voorbeeld.

B. De niet-null-waarde in een loontabel retourneren

In het volgende voorbeeld bevat de tabel wages drie kolommen met informatie over het jaarlijkse loon van de werknemers: het uurloon, salaris en commissie. Een werknemer ontvangt echter slechts één type betaling. Als u het totale bedrag wilt bepalen dat aan alle werknemers is betaald, gebruikt u COALESCE om alleen de niet-null-waarde te ontvangen die in hourly_wage, salaryen commissionis gevonden.

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

Dit is de resultatenset.

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

Delen via

COALESCE (Transact-SQL)

Syntaxis

Argumenten

expressie

Retourtypen

Opmerkingen

COALESCE en CASE vergelijken

COALESCE en ISNULL vergelijken

Voorbeelden

Een. Gegevens retourneren uit de eerste kolom met een niet-null-waarde

B. De niet-null-waarde in een loontabel retourneren

Feedback

Aanvullende resources

Delen via

COALESCE (Transact-SQL)

Syntaxis

Argumenten

expressie

Retourtypen

Opmerkingen

COALESCE en CASE vergelijken

COALESCE en ISNULL vergelijken

Voorbeelden

Een. Gegevens retourneren uit de eerste kolom met een niet-null-waarde

B. De niet-null-waarde in een loontabel retourneren

Verwante inhoud

Feedback

Aanvullende resources