COALESCE (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)punkt końcowy analizy SQL w usłudze Microsoft FabricWarehouse w usłudze Microsoft FabricSQL Database w usłudze Microsoft Fabric

Oblicza argumenty w kolejności i zwraca bieżącą wartość pierwszego wyrażenia, które początkowo nie daje wartości NULL. Poniższy przykład zwraca trzecią wartość, ponieważ trzecia wartość jest pierwszą wartością, która nie ma wartości null.

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

Nuta

Jeśli chcesz połączyć ciągi, użyj STRING_AGG zamiast tego.

Transact-SQL konwencje składni

Składnia

COALESCE ( expression [ , ...n ] )

Argumenty

wyrażenia

Wyrażenie dowolnego typu.

Typy zwracane

Zwraca typ danych wyrażenia z najwyższym pierwszeństwem typu danych. Jeśli wszystkie wyrażenia nie są dopuszczane do wartości null, wynik jest wpisywany jako niepusty.

Uwagi

Jeśli wszystkie argumenty są NULL, COALESCE zwraca wartość NULL. Co najmniej jedna z wartości null musi być wpisana NULL.

Porównanie TECHNOLOGII COALESCE i CASE

Wyrażenie COALESCE to skrót składniowy dla wyrażenia CASE. Oznacza to, że kod COALESCE(<expression1>, ...n) jest przepisany przez optymalizator zapytań jako następujące wyrażenie CASE:

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

W związku z tym wartości wejściowe ( wyrażenie1, wyrażenie 2, wyrażenie Nitd.) są obliczane wiele razy. Wyrażenie wartości zawierające podzapytywanie jest uznawane za nieokreślone, a podzapytywanie jest obliczane dwukrotnie. Ten wynik jest zgodny ze standardem SQL. W obu przypadkach różne wyniki można zwrócić między pierwszą oceną a nadchodzącymi ocenami.

Na przykład po wykonaniu COALESCE((subquery), 1) kodu podzapytywanie jest oceniane dwa razy. W związku z tym można uzyskać różne wyniki w zależności od poziomu izolacji zapytania. Na przykład kod może zwrócić NULL na poziomie izolacji READ COMMITTED w środowisku wielu użytkowników. Aby zapewnić zwracanie stabilnych wyników, użyj poziomu izolacji SNAPSHOT ISOLATION lub zastąp COALESCE funkcją ISNULL. Alternatywnie możesz ponownie napisać zapytanie, aby wypchnąć podzapytanie do podwybierz, jak pokazano w poniższym przykładzie:

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;

Porównanie TECHNOLOGII COALESCE i ISNULL

Funkcja ISNULL i wyrażenie COALESCE mają podobny cel, ale mogą zachowywać się inaczej.

1. Ponieważ ISNULL jest funkcją, jest obliczana tylko raz. Jak opisano wcześniej, wartości wejściowe wyrażenia COALESCE można wielokrotnie oceniać.

2. Określanie typu danych wyrażenia wynikowego jest inne. ISNULL używa typu danych pierwszego parametru, a COALESCE jest zgodna z regułami wyrażeń CASE, aby zwrócić typ danych wartości o najwyższym prioryencie.

3. NULLability wyrażenia wyniku różni się w przypadku ISNULL i COALESCE. Wartość zwracana ISNULL jest zawsze uważana za NOT NULLw stanie (przy założeniu, że wartość zwracana jest niepusta). NatomiastCOALESCE z parametrami innych niż null jest uważana za NULL. Dlatego wyrażenia ISNULL(NULL, 1) i COALESCE(NULL, 1), chociaż równe, mają różne wartości nullability. Te wartości mają znaczenie w przypadku używania tych wyrażeń w kolumnach obliczeniowych, tworzeniu kluczowych ograniczeń lub tworzeniu wartości zwracanej przez deterministyczną funkcję zdefiniowaną przez użytkownika (UDF), aby można było ją indeksować, jak pokazano w poniższym przykładzie:

   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. Walidacje ISNULL i COALESCE również różnią się. Na przykład wartość NULL dla ISNULL jest konwertowana na int jednak w przypadku COALESCEnależy podać typ danych.

5. ISNULL przyjmuje tylko dwa parametry. Natomiast COALESCE przyjmuje zmienną liczbę parametrów.

Przykłady

Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2022 lub AdventureWorksDW2022, którą można pobrać ze strony głównej Przykładów programu Microsoft SQL Server i projektów społeczności.

A. Zwraca dane z pierwszej kolumny, która ma wartość inną niż null

W poniższym przykładzie pokazano, jak COALESCE wybiera dane z pierwszej kolumny, która ma wartość inną niż null. Załóżmy, że w tym przykładzie tabela Products zawiera następujące dane:

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

Następnie uruchomimy następujące zapytanie COALESCE:

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

Oto zestaw wyników.

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

W pierwszym wierszu wartość FirstNotNull jest PN1278, a nie Socks, Mens. Ta wartość jest taka, ponieważ kolumna Name nie została określona jako parametr dla COALESCE w przykładzie.

B. Zwracanie wartości innej niż null w tabeli płac

W poniższym przykładzie tabela wages zawiera trzy kolumny zawierające informacje o rocznej płacy pracowników: płacy godzinowej, wynagrodzenia i prowizji. Jednak pracownik otrzymuje tylko jeden rodzaj wynagrodzenia. Aby określić łączną kwotę zapłaconą wszystkim pracownikom, użyj COALESCE, aby otrzymywać tylko wartość niepustą znalezioną w hourly_wage, salaryi 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

Oto zestaw wyników.

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

Powiązana zawartość

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