Sebagian besar laporan memerlukan logika: memberi label pesanan sebagai besar atau kecil, menghitung pesanan yang telah dibayar dan tertunda secara berdampingan, atau menampilkan item mendesak terlebih dahulu. SQL CASE WHEN menangani semua ini langsung di dalam kueri, dan berfungsi di semua database utama.

Kami menjalankan setiap kueri di sini di SQLite 3.50, dan hasil yang ditampilkan berasal dari proses tersebut. CASE adalah bagian dari SQL standar, sehingga berperilaku sama di PostgreSQL, MySQL, SQL Server, dan Oracle untuk contoh ini.

Apa itu KASUS KAPAN?

CASE adalah ekspresi, bukan perintah terpisah. Ia mengevaluasi kondisi secara berurutan dan mengembalikan nilai, sehingga dapat dibawa ke mana pun nilai diperbolehkan: di SELECT, WHERE, GROUP BY, ORDER BY, atau di dalam fungsi. Itu selalu diakhiri dengan END.

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

Tabel sampel

CREATE TABLE orders (id INT, customer TEXT, total INT, status TEXT);

INSERT INTO orders VALUES
 (1,'Asha',1200,'paid'), (2,'Ben',300,'pending'),
 (3,'Asha',800,'paid'), (4,'Chitra',2500,'shipped'),
 (5,'Dev',150,'cancelled'), (6,'Ben',NULL,'pending');

Perhatikan bahwa pesanan 6 tidak memiliki total. Pertengkaran itu penting di kemudian hari.

KASUS yang Dicari

Ini adalah bentuk yang lebih fleksibel, karena masing-masing WHEN mempunyai kondisi penuhnya sendiri:

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'large'
         WHEN total >= 500 THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders;

-- 1 | 1200 | large
-- 2 |  300 | small
-- 3 |  800 | medium
-- 4 | 2500 | large
-- 5 |  150 | small
-- 6 | NULL | small

Lihatlah baris terakhir. Order 6 tidak ada totalnya, namun diberi label “kecil”. Kedua perbandingan dengan NULL tersebut tidak benar, sehingga jatuh ke ELSE. Kami menunjukkan cara memperbaikinya di bagian NULL. Selalu beri nama hasil menggunakan AS, atau Anda mungkin mendapatkan nama kolom yang aneh.

KASUS Sederhana

Saat Anda membandingkan satu kolom dengan nilai tetap, formulir yang lebih pendek akan terbaca lebih baik:

SELECT id,
       CASE status
         WHEN 'paid' THEN 'Complete'
         WHEN 'shipped' THEN 'Complete'
         WHEN 'pending' THEN 'Waiting'
       END AS label
FROM orders;

-- 1 | Complete
-- 2 | Waiting
-- 3 | Complete
-- 4 | Complete
-- 5 | NULL
-- 6 | Waiting

Ketertiban itu penting

CASE berhenti pada kondisi pertama yang benar, dan ini adalah bug logika yang paling umum. Tukar dua kondisi dari kueri kami sebelumnya:

SELECT id, total,
       CASE
         WHEN total >= 500 THEN 'medium'
         WHEN total >= 1000 THEN 'large'
         ELSE 'small'
       END AS size
FROM orders WHERE id IN (1, 4);

-- 1 | 1200 | medium
-- 4 | 2500 | medium

Sekarang tidak ada yang bisa menjadi “besar”. Total 2500 juga minimal 500, jadi cabang pertama menang dan cabang kedua tidak pernah tercapai. Tidak ada kesalahan yang muncul, hanya label yang salah. Untuk rentang, masukkan kondisi yang paling ketat terlebih dahulu, atau tulis rentang lengkap, misalnya total >= 500 AND total < 1000.

Apa yang terjadi tanpa ELSE?

Dalam contoh KASUS sederhana kita di atas, pesanan 5 dibatalkan, dan tidak ada cabang yang menanganinya, sehingga hasilnya adalah NULL. Terkadang itulah yang Anda inginkan, seperti yang ditunjukkan bagian selanjutnya. Namun ketika membuat label, ELSE 'other' yang jelas biasanya lebih baik, karena membuat nilai yang tidak diharapkan terlihat daripada membiarkan kosong dalam laporan.

Laporan bergaya pivot dengan SUM

Ini adalah salah satu pola paling berguna dalam SQL sehari-hari. Membungkus CASE secara agregat mengubah baris menjadi kolom, dengan satu baris per pelanggan:

SELECT customer,
       SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY customer
ORDER BY customer;

-- Asha   | 2000 | 0
-- Ben    |    0 | 2
-- Chitra |    0 | 0
-- Dev    |    0 | 0

Satu kueri kini memberikan pendapatan berbayar dan jumlah pesanan yang tertunda, tanpa memerlukan kueri atau penggabungan terpisah. Pola ini berfungsi di hampir setiap database, tidak seperti sintaks PIVOT khusus yang hanya ditawarkan oleh beberapa database. Jika GROUP BY baru bagi Anda, baca SQL GROUP BY dan HAVING.

Jumlah bersyarat

COUNT melewatkan NULL, jadi CASE tanpa ELSE hanya menghitung baris yang cocok:

SELECT COUNT(*) AS all_orders,
       COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
       COUNT(CASE WHEN total >= 1000 THEN 1 END) AS large_orders
FROM orders;

-- 6 | 2 | 2

Hati-hati: jika Anda menambahkan ELSE 0 di dalam COUNT, setiap baris akan dihitung, karena 0 bukan NULL. Gunakan ELSE 0 dengan SUM, dan tinggalkan dengan COUNT. PostgreSQL dan SQLite juga mendukung COUNT(*) FILTER (WHERE ...), yang terbaca dengan baik, tetapi tidak tersedia di semua tempat.

Pengelompokan berdasarkan KASUS

Anda dapat membuat keranjang Anda sendiri, lalu meringkasnya:

SELECT CASE WHEN total >= 1000 THEN 'large' ELSE 'other' END AS bucket,
       COUNT(*)
FROM orders
GROUP BY bucket
ORDER BY bucket;

-- large | 2
-- other | 4

Pengelompokan berdasarkan alias berfungsi di SQLite, PostgreSQL, dan MySQL. SQL Server tidak mengizinkannya, jadi Anda harus mengulangi seluruh ekspresi, atau memasukkan kueri ke dalam CTE atau subkueri terlebih dahulu.

Penyortiran khusus dengan ORDER BY

Urutan abjad jarang diinginkan oleh bisnis. CASE memungkinkan Anda menentukan pesanan Anda sendiri:

SELECT id, status FROM orders
ORDER BY CASE status
           WHEN 'pending' THEN 1
           WHEN 'paid' THEN 2
           ELSE 3
         END, id;

-- 2 | pending
-- 6 | pending
-- 1 | paid
-- 3 | paid
-- 4 | shipped
-- 5 | cancelled

Pesanan yang tertunda didahulukan, yang berguna untuk antrian kerja. Kolom pengurutan kedua, id, menjaga baris-baris dalam urutan yang dapat diprediksi dalam setiap grup.

KASUS dalam UPDATE

CASE memungkinkan satu pernyataan menerapkan perubahan berbeda pada baris berbeda. Di sini, pesanan kecil yang tertunda ditandai untuk ditinjau:

UPDATE orders
SET status = CASE
               WHEN status = 'pending' AND total < 500 THEN 'review'
               ELSE status
             END;

SELECT id, status FROM orders WHERE customer = 'Ben';

-- 2 | review
-- 6 | pending

ELSE status membuat baris lainnya tidak berubah. Mengabaikannya berbahaya, karena baris yang tidak cocok akan disetel ke NULL. Tanpa klausa WHERE, pernyataan ini juga menyentuh setiap baris, jadi pada tabel besar tambahkan filter, dan pertimbangkan untuk membungkus perubahan dalam transaksi sehingga Anda dapat memeriksa hasilnya sebelum Anda melakukan. Perhatikan juga bahwa order 6 tidak berubah, karena total NULL-nya tidak memuaskan total < 500.

jebakan NULL

NULL menyebabkan dua masalah terpisah dengan CASE. Pertama, perbandingan bentuk sederhana menggunakan persamaan, dan tidak ada yang sama dengan NULL, bahkan NULL itu sendiri:

SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'not matched' END;

-- not matched

Jadi CASE col WHEN NULL tidak pernah cocok. Gunakan formulir yang dicari dengan IS NULL sebagai gantinya. Kedua, seperti yang kita lihat pada urutan 6, nilai NULL lolos dari perbandingan dan masuk ke ELSE. Cara mengatasinya adalah dengan mengeceknya terlebih dahulu:

SELECT id,
       CASE
         WHEN total IS NULL THEN 'unknown'
         WHEN total >= 1000 THEN 'large'
         ELSE 'other'
       END AS size
FROM orders WHERE id IN (1, 2, 6);

-- 1 | large
-- 2 | other
-- 6 | unknown

Pesan 6 sekarang mendapat label jujur. Panduan kami untuk nilai SQL NULL mencakup perilaku ini secara lengkap.

KASUS vs opsi lain

Pilihan terakhir itu patut untuk dipikirkan. Jika CASE berkembang menjadi tiga puluh cabang dan muncul dalam lima kueri, pindahkan pemetaan ke dalam tabel dan gabung ke dalamnya. Maka perubahannya adalah pembaruan satu baris, bukan perubahan kode di lima tempat.

Kesalahan umum

• Urutan kondisi salah. Kondisi yang luas terlebih dahulu menyembunyikan kondisi yang lebih sempit.
• Lupa END. Ini adalah kesalahan sintaksis yang paling sering terjadi.
• Menggunakan WHEN NULL. Tidak pernah cocok, jadi gunakan WHEN col IS NULL.
• Menambahkan ELSE 0 di dalam COUNT. Ini menghitung semua baris.
• Menghilangkan ELSE dalam UPDATE. Ini dapat menghapus nilai dengan NULL.
• Pemfilteran berdasarkan CASE di WHERE. Ini dapat menghentikan penggunaan indeks. Lihat panduan kami untuk optimasi kueri SQL.

Penutup

CASE WHEN adalah fitur kecil yang melakukan banyak pekerjaan. Ingat tiga aturan: cabang pertama yang benar menang, baris yang tidak cocok menjadi NULL tanpa ELSE, dan NULL memerlukan pemeriksaannya sendiri. Untuk berlatih, ambil laporan yang sedang Anda buat dengan beberapa kueri, dan cobalah membuatnya sebagai satu kueri menggunakan SUM(CASE ...).

Pertanyaan yang sering diajukan

Teruslah belajar