Conditional

COALESCE

DuckDB

Mengembalikan nilai non-NULL pertama dari daftar argumen. Sangat berguna untuk menangani NULL values.

Tipe hasil: Tipe dari nilai non-NULL pertamaDiperbarui: 6 Jan 2026

Syntax

SQL

COALESCE(value1, value2, ...)

Parameter

value1, value2, ...ANYwajib

Nilai-nilai yang akan dievaluasi

Contoh Penggunaan

Default Value untuk NULL

SQL

1 SELECT
2   customer_name,
3   COALESCE(phone, email, 'No contact') AS contact
4 FROM customers;

Menggunakan fallback untuk kontak.

Handling NULL dalam Perhitungan

SQL

1 SELECT
2   product_name,
3   COALESCE(discount, 0) AS discount,
4   price * (1 - COALESCE(discount, 0) / 100) AS final_price
5 FROM products;

Default 0 untuk discount NULL.

Multiple Fallbacks

SQL

1 SELECT
2   COALESCE(
3     preferred_name,
4     nickname,
5     first_name || ' ' || last_name,
6     'Anonymous'
7   ) AS display_name
8 FROM users;

Chain of fallbacks untuk display name.

COALESCE dengan Subquery

SQL

1 SELECT
2   p.product_name,
3   COALESCE(
4     (SELECT AVG(rating) FROM reviews r WHERE r.product_id = p.id),
5     0
6   ) AS avg_rating
7 FROM products p;

Default untuk produk tanpa review.

Fungsi Terkait

NULLIFNVLNVL2IFNULL

Kembali ke DuckDB Functions

1	SELECT
2	customer_name,
3	COALESCE(phone, email, 'No contact') AS contact
4	FROM customers;

1	SELECT
2	product_name,
3	COALESCE(discount, 0) AS discount,
4	price * (1 - COALESCE(discount, 0) / 100) AS final_price
5	FROM products;

1	SELECT
2	COALESCE(
3	preferred_name,
4	nickname,
5	first_name \|\| ' ' \|\| last_name,
6	'Anonymous'
7	) AS display_name
8	FROM users;

1	SELECT
2	p.product_name,
3	COALESCE(
4	(SELECT AVG(rating) FROM reviews r WHERE r.product_id = p.id),
5	0
6	) AS avg_rating
7	FROM products p;