COALESCE (Transact-SQL)

Artikel
01/29/2025

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)SQL-Analyseendpunkt in Microsoft Fabric Warehouse in Microsoft Fabric SQL-Datenbank in Microsoft Fabric

Wertet die Argumente in der vorliegenden Reihenfolge aus und gibt den aktuellen Wert des ersten Ausdrucks zurück, der anfangs nicht NULL ergibt. Im folgenden Beispiel wird der dritte Wert zurückgegeben, da der dritte Wert der erste Wert ist, der nicht NULL ist.

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

Anmerkung

Wenn Sie Zeichenfolgen verketten möchten, verwenden Sie stattdessen STRING_AGG.

Transact-SQL-Syntaxkonventionen

Syntax

COALESCE ( expression [ , ...n ] )

Argumente

expression

Ein Ausdruck eines beliebigen Typs.

Rückgabetypen

Gibt den Datentyp des expression-Ausdrucks zurück, der in der Datentyprangfolge am höchsten steht. Wenn alle Ausdrücke nicht nullfähig sind, wird das Ergebnis als nicht nullwertebar eingegeben.

Bemerkungen

NULL gibt COALESCE zurück, wenn alle Argumente NULL sind. Mindestens einer der NULL-Werte muss ein typisierter NULL-Wert sein.

Vergleich von COALESCE und CASE

Der COALESCE-Ausdruck ist eine syntaktische Kurzform für den CASE-Ausdruck. Das heißt, der code COALESCE(<expression1>, ...n) wird vom Abfrageoptimierer wie der folgende CASE Ausdruck umgeschrieben:

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

Daher werden die Eingabewerte (expression1, expression2, expressionN usw.) mehrmals ausgewertet. Ein Wertausdruck, der eine Unterabfrage enthält, wird als nicht deterministisch betrachtet, und die Unterabfrage wird zweimal ausgewertet. Dieses Ergebnis entspricht dem SQL-Standard. In beiden Fällen können zwischen der ersten Auswertung und kommenden Auswertungen unterschiedliche Ergebnisse zurückgegeben werden.

Beispiel: Wenn der Code COALESCE((subquery), 1) ausgeführt wird, wird die Unterabfrage zweimal ausgewertet. Folglich können Sie abhängig von der Isolationsstufe der Abfrage unterschiedliche Ergebnisse erhalten. Beispielsweise kann der Code auf der NULL-Isolationsstufe in einer Mehrbenutzerumgebung READ COMMITTED zurückgeben. Um sicherzustellen, dass beständige Ergebnisse zurückgegeben werden, verwenden Sie die SNAPSHOT ISOLATION-Isolationsstufe oder ersetzen COALESCE durch die ISNULL-Funktion. Alternativ können Sie die Abfrage neu schreiben, um die Unterabfrage in eine untergeordnete SELECT-Anweisung zu verschieben, wie im folgenden Beispiel gezeigt:

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;

Vergleich von COALESCE und ISNULL

Die ISNULL-Funktion und der COALESCE-Ausdruck dienen einem ähnlichen Zweck, können jedoch ein unterschiedliches Verhalten aufweisen.

Da ISNULL eine Funktion ist, wird sie nur einmal ausgewertet. Wie bereits beschrieben, können die Eingabewerte für den COALESCE Ausdruck mehrmals ausgewertet werden.
Die Datentypen des resultierenden Ausdrucks werden auf unterschiedliche Weise bestimmt. ISNULL verwendet den Datentyp des ersten Parameters, und COALESCE folgt den CASE Ausdrucksregeln, um den Datentyp des Werts mit der höchsten Rangfolge zurückzugeben.
Die NULL-Zulässigkeit des Ergebnisausdrucks ist bei ISNULL und COALESCE unterschiedlich. Der ISNULL Rückgabewert gilt immer als NOT NULLmöglich (vorausgesetzt, der Rückgabewert ist ein nicht nullwertes Wert). Im Gegensatz dazu wird COALESCE mit Parametern ungleich NULL als NULL betrachtet. Daher weisen der ISNULL(NULL, 1)-Ausdruck und der COALESCE(NULL, 1)-Ausdruck unterschiedliche Werte für die NULL-Zulässigkeit auf, obwohl sie gleich sind. Diese Werte machen einen Unterschied, wenn Sie diese Ausdrücke in berechneten Spalten verwenden, Schlüsseleinschränkungen erstellen oder den Rückgabewert einer skalaren benutzerdefinierten Funktion (UDF) deterministisch machen, sodass sie wie im folgenden Beispiel indiziert werden kann:
```
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
);
```
Überprüfungen für ISNULL und COALESCE sind ebenfalls unterschiedlich. Beispielsweise wird ein NULL-Wert für ISNULL in int konvertiert, während Sie für COALESCE einen Datentyp angeben müssen.
ISNULL verwendet nur zwei Parameter. Im Gegensatz dazu ist bei COALESCE die Parameteranzahl variabel.

Beispiele

Die Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022 Beispieldatenbank, die Sie von der Microsoft SQL Server Samples and Community Projects Homepage herunterladen können.

A. Zurückgeben von Daten aus der ersten Spalte mit einem Wert ungleich NULL

Im folgenden Beispiel wird veranschaulicht, wie COALESCE die Daten aus der ersten Spalte auswählt, die einen Wert ungleich NULL aufweist. In diesem Beispiel wird angenommen, dass die Products-Tabelle die folgenden Daten enthält:

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

Anschließend führen wir die folgende COALESCE Abfrage aus:

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

Hier sehen Sie das Ergebnis.

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

In der ersten Zeile ist der FirstNotNull Wert PN1278, nicht Socks, Mens. Grund hierfür ist, dass die Name-Spalte im Beispiel nicht als Parameter für COALESCE angegeben wurde.

B. Zurückgeben des Werts ungleich Null in einer Lohntabelle

Im folgenden Beispiel enthält die wages-Tabelle drei Spalten mit Informationen zu den Jahresgehältern der Angestellten: den Stundensatz, das Gehalt und die Provision. Allerdings wird ein Angestellter nur nach einem dieser Gehaltstypen bezahlt. Um den Gesamtbetrag zu ermitteln, der an alle Mitarbeiter gezahlt wird, verwenden Sie COALESCE, um nur den in hourly_wage, salaryund commissiongefundenen Wert zu erhalten.

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

Hier sehen Sie das Ergebnis.

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

Freigeben über

COALESCE (Transact-SQL)

Syntax

Argumente

expression

Rückgabetypen

Bemerkungen

Vergleich von COALESCE und CASE

Vergleich von COALESCE und ISNULL

Beispiele

A. Zurückgeben von Daten aus der ersten Spalte mit einem Wert ungleich NULL

B. Zurückgeben des Werts ungleich Null in einer Lohntabelle

Feedback

Zusätzliche Ressourcen

Freigeben über

COALESCE (Transact-SQL)

Syntax

Argumente

expression

Rückgabetypen

Bemerkungen

Vergleich von COALESCE und CASE

Vergleich von COALESCE und ISNULL

Beispiele

A. Zurückgeben von Daten aus der ersten Spalte mit einem Wert ungleich NULL

B. Zurückgeben des Werts ungleich Null in einer Lohntabelle

Verwandte Inhalte

Feedback

Zusätzliche Ressourcen