COALESCE (Transact-SQL)

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)koncový bod SQL Analytics ve službě Microsoft FabricWarehouse v Microsoft Fabricdatabáze SQL v Microsoft Fabric

Vyhodnotí argumenty v pořadí a vrátí aktuální hodnotu prvního výrazu, který se zpočátku nevyhodnocuje jako NULL. Následující příklad vrátí třetí hodnotu, protože třetí hodnota je první hodnota, která nemá hodnotu null.

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

Poznámka

Pokud chcete zřetězení řetězců, použijte místo toho STRING_AGG.

Transact-SQL konvence syntaxe

Syntax

COALESCE ( expression [ , ...n ] )

Argumenty

výrazu

Výraz libovolného typu.

Návratové typy

Vrátí datový typ výrazu s nejvyšší prioritou datového typu. Pokud všechny výrazy nejsou nullable, výsledek se zadá jako nenulový.

Poznámky

Pokud jsou všechny argumenty NULL, vrátí COALESCENULL. Nejméně jedna z hodnot null musí být typ NULL.

Porovnání funkce COALESCE a CASE

Výraz COALESCE je syntaktická zkratka výrazu CASE. To znamená, že optimalizátor dotazů přepíše kód COALESCE(<expression1>, ...n) jako následující výraz CASE:

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

Vstupní hodnoty (výraz1, výraz2, výrazNatd.) se vyhodnocují vícekrát. Výraz hodnoty, který obsahuje poddotaz, se považuje za nedeterministický a poddotaz se vyhodnotí dvakrát. Tento výsledek je v souladu se standardem SQL. V obou případech se mezi prvním vyhodnocením a nadcházejícími vyhodnoceními můžou vrátit různé výsledky.

Například při spuštění kódu COALESCE((subquery), 1) se poddotaz vyhodnotí dvakrát. V důsledku toho můžete získat různé výsledky v závislosti na úrovni izolace dotazu. Kód může například vracet NULL pod úrovní izolace READ COMMITTED v prostředí s více uživateli. Pokud chcete zajistit vrácení stabilních výsledků, použijte úroveň izolace SNAPSHOT ISOLATION nebo nahraďte COALESCE funkcí ISNULL. Alternativně můžete dotaz přepsat tak, aby se poddotaz nasdílel do dílčího výběru, jak je znázorněno v následujícím příkladu:

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;

Porovnání funkce COALESCE a ISNULL

Funkce ISNULL a výraz COALESCE mají podobný účel, ale může se chovat jinak.

1. Protože ISNULL je funkce, vyhodnotí se pouze jednou. Jak jsme popsali dříve, vstupní hodnoty pro výraz COALESCE lze vyhodnotit vícekrát.

2. Určení datového typu výsledného výrazu se liší. ISNULL používá datový typ prvního parametru a COALESCE se řídí pravidly výrazu CASE k vrácení datového typu hodnoty s nejvyšší prioritou.

3. NULLability výsledného výrazu se liší pro ISNULL a COALESCE. Návratová hodnota ISNULL se vždy považuje za NOT NULLschopnou (za předpokladu, že návratová hodnota je nenulová). Naproti tomuCOALESCE s nenulovými parametry se považuje za NULL. Výrazy tedy ISNULL(NULL, 1) a COALESCE(NULL, 1), i když jsou stejné, mají různé hodnoty nullability. Tyto hodnoty se liší, pokud tyto výrazy používáte ve počítaných sloupcích, vytváříte klíčová omezení nebo vytváříte návratovou hodnotu skalární uživatelem definované funkce (UDF), aby bylo možné je indexovat, jak je znázorněno v následujícím příkladu:

   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
   );
   

4. Ověřování pro ISNULL a COALESCE se také liší. Například hodnota NULL pro ISNULL se převede na int ale pro COALESCE, musíte zadat datový typ.

5. ISNULL přijímá pouze dva parametry. Naproti tomu COALESCE přebírá proměnlivý počet parametrů.

Příklady

Ukázky kódu v tomto článku používají ukázkovou databázi AdventureWorks2022 nebo AdventureWorksDW2022, kterou si můžete stáhnout z domovské stránky ukázky a komunitní projekty Microsoft SQL Serveru.

A. Vrácení dat z prvního sloupce, který má nenulovou hodnotu

Následující příklad ukazuje, jak COALESCE vybere data z prvního sloupce, který má nenulovou hodnotu. V tomto příkladu předpokládejme, že tabulka Products obsahuje tato data:

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

Potom spustíme následující dotaz COALESCE:

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

Tady je sada výsledků.

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

V prvním řádku je hodnota FirstNotNullPN1278, nikoli Socks, Mens. Tato hodnota je tímto způsobem, protože sloupec Name nebyl zadán jako parametr pro COALESCE v příkladu.

B. Vrácení nenulové hodnoty v tabulce mezd

V následujícím příkladu tabulka wages obsahuje tři sloupce, které obsahují informace o roční mzdě zaměstnanců: hodinová mzda, mzda a provize. Zaměstnanec však obdrží pouze jeden typ platby. Chcete-li určit celkovou částku zaplacenou všem zaměstnancům, použijte COALESCE k získání pouze nenulové hodnoty nalezené v hourly_wage, salarya 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

Tady je sada výsledků.

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

Související obsah

• ISNULL (Transact-SQL)
• CASE (Transact-SQL)
• STRING_AGG (Transact-SQL)