COALESCE (Transact-SQL)

Artikel
01/29/2025

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) titik akhir analitik SQL di Microsoft Fabric Warehouse dalam database Microsoft Fabric SQL di Microsoft Fabric

Mengevaluasi argumen secara berurutan dan mengembalikan nilai ekspresi pertama saat ini yang awalnya tidak dievaluasi ke NULL. Contoh berikut mengembalikan nilai ketiga karena nilai ketiga adalah nilai pertama yang tidak null.

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

Nota

Jika Anda ingin menggabungkan string, gunakan STRING_AGG sebagai gantinya.

Konvensi sintaks transact-SQL

Sintaks

COALESCE ( expression [ , ...n ] )

Argumen

expression

Ekspresi dari jenis apa pun.

Jenis pengembalian

Mengembalikan tipe data ekspresi dengan prioritas tipe data tertinggi. Jika semua ekspresi tidak dapat diubah ke null, hasilnya dititikkan sebagai tidak dapat diubah ke null.

Keterangan

Jika semua argumen adalah NULL, COALESCE mengembalikan NULL. Setidaknya salah satu nilai null harus diketik NULL.

Membandingkan COALESCE dan CASE

Ekspresi COALESCE adalah pintasan sintik untuk CASE ekspresi. Artinya, kode COALESCE(<expression1>, ...n) ditulis ulang oleh pengoptimal kueri sebagai ekspresi CASE berikut:

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

Dengan demikian, nilai input (ekspresi1, ekspresi2, ekspresiN, dan sebagainya) dievaluasi beberapa kali. Ekspresi nilai yang berisi subkueri dianggap tidak deterministik dan subkueri dievaluasi dua kali. Hasil ini sesuai dengan standar SQL. Dalam kedua kasus, hasil yang berbeda dapat dikembalikan antara evaluasi pertama dan evaluasi yang akan datang.

Misalnya, ketika kode COALESCE((subquery), 1) dijalankan, subkueri dievaluasi dua kali. Akibatnya, Anda bisa mendapatkan hasil yang berbeda tergantung pada tingkat isolasi kueri. Misalnya, kode dapat kembali NULL di READ COMMITTED bawah tingkat isolasi di lingkungan multi-pengguna. Untuk memastikan hasil yang stabil dikembalikan, gunakan SNAPSHOT ISOLATION tingkat isolasi, atau ganti COALESCE dengan ISNULL fungsi . Sebagai alternatif, Anda dapat menulis ulang kueri untuk mendorong subkueri ke subpilih seperti yang ditunjukkan dalam contoh berikut:

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;

Membandingkan COALESCE dan ISNULL

Fungsi ISNULL dan COALESCE ekspresi memiliki tujuan yang sama tetapi dapat berperilaku berbeda.

Karena ISNULL merupakan fungsi, fungsi ini hanya dievaluasi sekali. Seperti yang dijelaskan sebelumnya, nilai input untuk ekspresi COALESCE dapat dievaluasi beberapa kali.
Penentuan jenis data dari ekspresi yang dihasilkan berbeda. ISNULL menggunakan jenis data parameter pertama, dan COALESCE mengikuti aturan ekspresi CASE untuk mengembalikan tipe data nilai dengan prioritas tertinggi.
Nullabilitas ekspresi hasil berbeda untuk ISNULL dan COALESCE. Nilai pengembalian ISNULL selalu dianggap NOT NULLmampu (dengan asumsi nilai yang dikembalikan adalah nilai yang tidak dapat diubah ke null). Sebaliknya,COALESCE dengan parameter non-null dianggap sebagai NULL. Jadi ekspresi ISNULL(NULL, 1) dan COALESCE(NULL, 1), meskipun sama, memiliki nilai nullability yang berbeda. Nilai-nilai ini membuat perbedaan jika Anda menggunakan ekspresi ini dalam kolom komputasi, membuat batasan kunci, atau membuat nilai pengembalian fungsi skalar yang ditentukan pengguna (UDF) deterministik, sehingga dapat diindeks seperti yang ditunjukkan dalam contoh berikut:
```
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
);
```
Validasi untuk ISNULL dan COALESCE juga berbeda. Misalnya, NULL nilai untuk ISNULL dikonversi menjadi int meskipun untuk COALESCE, Anda harus menyediakan jenis data.
ISNULL hanya mengambil dua parameter. COALESCE Sebaliknya mengambil jumlah parameter variabel.

Contoh

Sampel kode dalam artikel ini menggunakan database sampel AdventureWorks2022 atau AdventureWorksDW2022, yang dapat Anda unduh dari halaman beranda Sampel dan Proyek Komunitas Microsoft SQL Server.

J. Mengembalikan data dari kolom pertama yang memiliki nilai non-null

Contoh berikut menunjukkan cara COALESCE memilih data dari kolom pertama yang memiliki nilai non-null. Asumsikan untuk contoh ini bahwa Products tabel berisi data ini:

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

Kami kemudian menjalankan kueri COALESCE berikut:

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

Berikut set hasilnya.

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

Di baris pertama, nilai FirstNotNullPN1278, bukan Socks, Mens. Nilai ini adalah cara ini karena Name kolom tidak ditentukan sebagai parameter untuk COALESCE dalam contoh.

B. Mengembalikan nilai non-null dalam tabel upah

Dalam contoh berikut, wages tabel menyertakan tiga kolom yang berisi informasi tentang upah tahunan karyawan: upah per jam, gaji, dan komisi. Namun, seorang karyawan hanya menerima satu jenis gaji. Untuk menentukan jumlah total yang dibayarkan kepada semua karyawan, gunakan COALESCE untuk hanya menerima nilai non-null yang ditemukan di hourly_wage, salary, dan 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

Berikut set hasilnya.

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

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

Bagikan melalui

COALESCE (Transact-SQL)

Sintaks

Argumen

expression

Jenis pengembalian

Keterangan

Membandingkan COALESCE dan CASE

Membandingkan COALESCE dan ISNULL

Contoh

J. Mengembalikan data dari kolom pertama yang memiliki nilai non-null

B. Mengembalikan nilai non-null dalam tabel upah

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

COALESCE (Transact-SQL)

Sintaks

Argumen

expression

Jenis pengembalian

Keterangan

Membandingkan COALESCE dan CASE

Membandingkan COALESCE dan ISNULL

Contoh

J. Mengembalikan data dari kolom pertama yang memiliki nilai non-null

B. Mengembalikan nilai non-null dalam tabel upah

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: