COALESCE (transact-SQL)

Artikel
07/20/2022
5 menit untuk membaca

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Mengevaluasi argumen secara berurutan dan mengembalikan nilai saat ini dari ekspresi pertama yang awalnya tidak dievaluasi ke NULL. Misalnya, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); mengembalikan nilai ketiga karena nilai ketiga adalah nilai pertama yang bukan null.

Topic link icon Konvensi Sintaksis T-SQL

Sintaks

COALESCE ( expression [ ,...n ] )

Argumen

ekspresi
Adalah ekspresi dari jenis apa pun.

Catatan

Untuk melihat sintaks transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

Jenis Pengembalian

Mengembalikan jenis ekspresi data dengan prioritas tipe data tertinggi. Jika semua ekspresi tidak dapat diulang, hasilnya dititikkan sebagai tidak dapat diulang.

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 sintaksis untuk CASE ekspresi. Artinya, kode COALESCE(ekspresi1,... n) ditulis ulang oleh pengoptimal kueri sebagai ekspresi berikut CASE :

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

Dengan demikian, nilai input (expression1, expression2, expressionN, 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 bawah READ COMMITTED 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 bisa menulis ulang kueri untuk mendorong subkueri ke subpilih seperti yang diperlihatkan 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 dievaluasi hanya sekali. Seperti yang dijelaskan di atas, nilai input untuk COALESCE ekspresi dapat dievaluasi beberapa kali.
Penentuan jenis data dari ekspresi yang dihasilkan berbeda. ISNULL menggunakan jenis data parameter pertama, COALESCE mengikuti CASE aturan ekspresi dan mengembalikan jenis data nilai dengan prioritas tertinggi.
Nullabilitas ekspresi hasil berbeda untuk ISNULL dan COALESCE. Nilai ISNULL yang dikembalikan selalu dianggap TIDAK DAPAT DIUBAH (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 di kolom komputasi, membuat batasan kunci atau membuat nilai pengembalian deterministik UDF skalar 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 INTEGER 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 INTEGER 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 ke int meskipun untuk COALESCE, Anda harus memberikan jenis data.
ISNULL hanya mengambil dua parameter. COALESCE Sebaliknya mengambil jumlah parameter variabel.

Contoh

A. Menjalankan contoh sederhana

Contoh berikut menunjukkan cara COALESCE memilih data dari kolom pertama yang memiliki nilai nonnull. Contoh ini menggunakan database AdventureWorks2012.

SELECT Name, Class, Color, ProductNumber,  
COALESCE(Class, Color, ProductNumber) AS FirstNotNull  
FROM Production.Product;

B. Menjalankan contoh yang kompleks

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

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  
  
(12 row(s) affected)

C: Contoh Sederhana

Contoh berikut menunjukkan cara COALESCE memilih data dari kolom pertama yang memiliki nilai bukan 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 adalah hasil yang ditetapkan.

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

Perhatikan bahwa di baris pertama, nilainya FirstNotNull adalah PN1278, bukan Socks, Mens. Nilai ini adalah cara ini karena Name kolom tidak ditentukan sebagai parameter untuk COALESCE dalam contoh.

D: Contoh Kompleks

Contoh berikut menggunakan COALESCE untuk membandingkan nilai dalam tiga kolom dan hanya mengembalikan nilai non-null yang ditemukan di kolom.

CREATE TABLE dbo.wages  
(  
    emp_id        TINYINT   NULL,  
    hourly_wage   DECIMAL   NULL,  
    salary        DECIMAL   NULL,  
    commission    DECIMAL   NULL,  
    num_sales     TINYINT   NULL  
);  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (1, 10.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (2, 20.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (3, 30.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (4, 40.00, NULL, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (5, NULL, 10000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (6, NULL, 20000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (7, NULL, 30000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (8, NULL, 40000.00, NULL, NULL);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (9, NULL, NULL, 15000, 3);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (10,NULL, NULL, 25000, 2);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (11, NULL, NULL, 20000, 6);  
  
INSERT INTO dbo.wages (emp_id, hourly_wage, salary, commission, num_sales)  
VALUES (12, NULL, NULL, 14000, 4);  
  
SELECT CAST(COALESCE(hourly_wage * 40 * 52,   
   salary,   
   commission * num_sales) AS DECIMAL(10,2)) AS TotalSalary   
FROM dbo.wages  
ORDER BY TotalSalary;

Berikut adalah hasil yang ditetapkan.

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

Lihat juga

ISNULL (SQL Transaksi)
CASE (transact-SQL)