Conditional

CASE

PostgreSQL

Ekspresi kondisional yang mengembalikan nilai berbeda berdasarkan kondisi. Ada dua bentuk: simple CASE (membandingkan expression) dan searched CASE (evaluasi kondisi boolean).

Tipe hasil: varies

Syntax

SQL

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

Parameter

WHENcondition/valuewajib

Kondisi atau nilai yang diuji

THENanywajib

Nilai yang dikembalikan jika kondisi terpenuhi

ELSEanyopsional

Nilai default jika tidak ada kondisi terpenuhi

Contoh Penggunaan

Simple CASE

SQL

1 SELECT 
2   name,
3   status,
4   CASE status
5     WHEN active THEN Aktif
6     WHEN inactive THEN Tidak Aktif
7     WHEN pending THEN Menunggu
8     ELSE Unknown
9   END AS status_label
10 FROM users;

Mengubah kode status menjadi label yang mudah dibaca.

Hasil

name	status	status_label
John	active	Aktif
Jane	pending	Menunggu

Searched CASE

SQL

1 SELECT 
2   name,
3   score,
4   CASE
5     WHEN score >= 90 THEN A
6     WHEN score >= 80 THEN B
7     WHEN score >= 70 THEN C
8     WHEN score >= 60 THEN D
9     ELSE F
10   END AS grade
11 FROM students;

Mengkonversi skor numerik menjadi grade.

Hasil

name	score	grade
Andi	95	A
Budi	78	C

CASE dalam Aggregate

SQL

1 SELECT 
2   COUNT(CASE WHEN status = active THEN 1 END) AS active_count,
3   COUNT(CASE WHEN status = inactive THEN 1 END) AS inactive_count
4 FROM users;

Conditional counting dalam satu query.

Fungsi Equivalen di Database Lain

CASE

MySQL

CASE

Snowflake

CASE

BigQuery

CASE

DuckDB

Fungsi Terkait

DECODE

Kembali ke PostgreSQL Functions

1	SELECT
2	name,
3	status,
4	CASE status
5	WHEN active THEN Aktif
6	WHEN inactive THEN Tidak Aktif
7	WHEN pending THEN Menunggu
8	ELSE Unknown
9	END AS status_label
10	FROM users;

1	SELECT
2	name,
3	score,
4	CASE
5	WHEN score >= 90 THEN A
6	WHEN score >= 80 THEN B
7	WHEN score >= 70 THEN C
8	WHEN score >= 60 THEN D
9	ELSE F
10	END AS grade
11	FROM students;

1	SELECT
2	COUNT(CASE WHEN status = active THEN 1 END) AS active_count,
3	COUNT(CASE WHEN status = inactive THEN 1 END) AS inactive_count
4	FROM users;