SZÖVETKEZET (Transact-SQL)

A következőkre vonatkozik:SQL Server Azure SQL Database Felügyelt Azure SQL-példány Azure Synapse Analytics Elemzési platformrendszer (PDW)SQL Analytics-végpont a Microsoft Fabricben Raktár a Microsoft Fabricben SQL-adatbázis a Microsoft Fabricben

Az argumentumokat sorrendben értékeli ki, és az első olyan kifejezés aktuális értékét adja vissza, amely kezdetben nem NULL. Az alábbi példa a harmadik értéket adja vissza, mert a harmadik érték az első érték, amely nem null.

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

Note

Ha sztringeket szeretne összefűzni, használja inkább a STRING_AGG.

Transact-SQL szintaxis konvenciói

Syntax

COALESCE ( expression [ , ...n ] )

Arguments

expression

Bármilyen típusú kifejezés.

Visszatérési típusok

A legmagasabb adattípus-prioritású kifejezés adattípusát adja vissza,. Ha az összes kifejezés nem null értékű, a rendszer nem null értékűként írja be az eredményt.

Remarks

Ha az összes argumentum NULL, COALESCENULLad vissza. A null értékek legalább egyikének beírt NULLkell lennie.

A COALESCE és a CASE összehasonlítása

A COALESCE kifejezés a CASE kifejezés szintaktikai parancsikonja. A lekérdezésoptimalizáló a következő COALESCE(<expression1>, ...n) kifejezésként írja át a CASE kódot:

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

Így a bemeneti értékek (kifejezés1, kifejezés2, expressionNstb.) többszörös kiértékelése történik. Az alqueryt tartalmazó értékkifejezések nemdeterminisztikusnak minősülnek, és a rendszer kétszer értékeli ki az al lekérdezést. Ez az eredmény megfelel az SQL-szabványnak. Mindkét esetben különböző eredményeket lehet visszaadni az első értékelés és a közelgő értékelések között.

A COALESCE((subquery), 1) kód végrehajtásakor például a rendszer kétszer kiértékeli az al lekérdezést. Ennek eredményeképpen a lekérdezés elkülönítési szintjétől függően különböző eredményeket kaphat. A kód például többfelhasználós környezetben NULL elkülönítési szinten READ COMMITTED adhat vissza. A stabil eredmények visszaadásához használja az SNAPSHOT ISOLATION elkülönítési szintet, vagy cserélje le a COALESCE a ISNULL függvényre. Másik lehetőségként újraírhatja a lekérdezést, hogy az al lekérdezést egy aljelölőbe küldje, ahogyan az alábbi példában látható:

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;

A COALESCE és az ISNULL összehasonlítása

A ISNULL függvény és a COALESCE kifejezés hasonló célt szolgál, de eltérően viselkedhet.

Mivel ISNULL függvény, csak egyszer lesz kiértékelve. A korábban ismertetett módon a COALESCE kifejezés bemeneti értékei többször is kiértékelhetők.
Az eredményként kapott kifejezés adattípus-meghatározása eltérő. ISNULL az első paraméter adattípusát használja, és COALESCE a CASE kifejezési szabályokat követi, hogy a legmagasabb prioritású adattípust adja vissza.
Az eredménykifejezés NULLability értéke ISNULL és COALESCEesetében eltérő. A ISNULL visszatérési érték mindig nem null értékű (feltéve, hogy a visszatérési érték nem null értékű). Ezzel szemben a nem null paraméterekkel rendelkezőCOALESCENULL. A ISNULL(NULL, 1) és COALESCE(NULL, 1)kifejezések tehát – bár egyenlőek – különböző nullképességi értékekkel rendelkeznek. Ezek az értékek akkor tesznek különbséget, ha ezeket a kifejezéseket számítási oszlopokban használja, kulcskényszereket hoz létre, vagy egy skaláris, felhasználó által definiált függvény (UDF) visszatérési értékét determinisztikussá teszi, hogy indexelhető legyen az alábbi példában látható módon:
```
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
);
```
A ISNULL és a COALESCE ellenőrzése is eltérő. A NULLISNULL értéke például int lesz, de COALESCEesetében meg kell adnia egy adattípust.
ISNULL csak két paramétert vesz fel. Ezzel szemben COALESCE változó számú paramétert vesz igénybe.

Examples

A cikkben szereplő kódminták a AdventureWorks2025 vagy AdventureWorksDW2025 mintaadatbázist használják, amelyet a Microsoft SQL Server-minták és közösségi projektek kezdőlapjáról tölthet le.

A. Adatok visszaadása az első oszlopból, amely nem null értékű

Az alábbi példa bemutatja, hogy COALESCE hogyan választja ki az adatokat a nem null értékű első oszlopból. Tegyük fel ebben a példában, hogy a Products tábla tartalmazza ezeket az adatokat:

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

Ezután a következő COALESCE lekérdezést futtatjuk:

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

Itt van az eredményhalmaz.

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

Az első sorban a FirstNotNull érték PN1278, nem Socks, Mens. Ez az érték azért van így, mert a Name oszlop nem lett megadva paraméterként a példában szereplő COALESCE számára.

B. A nem null értéket adja vissza egy bértáblában

A következő példában a wages táblázat három oszlopot tartalmaz, amelyek az alkalmazottak éves bérével kapcsolatos információkat tartalmazzák: az órabér, a fizetés és a jutalék. Az alkalmazott azonban csak egy típusú fizetést kap. Az összes alkalmazottnak kifizetett teljes összeg meghatározásához használja a COALESCE, hogy csak a hourly_wage, salaryés commissiontalálható nem null értéket kapja .

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