Saat menulis query SQL yang sederhana, kita biasanya hanya menggunakan satu atau dua tabel. Namun, ketika database semakin besar dan kebutuhan analisis data semakin kompleks, query SQL juga akan menjadi lebih panjang dan sulit dibaca.
Untuk mengatasi hal tersebut, SQL menyediakan fitur bernama Common Table Expression (CTE). Dengan CTE, Anda dapat membuat query yang lebih rapi, mudah dipahami, dan lebih mudah dikelola.
Pada artikel ini, Anda akan mempelajari Common Table Expression (CTE) pada SQL, mulai dari pengertian, fungsi, cara kerja, kelebihan, hingga contoh penggunaannya.
Apa Itu Common Table Expression (CTE)?
Common Table Expression (CTE) adalah hasil query sementara (temporary result set) yang dibuat menggunakan kata kunci WITH dan hanya berlaku selama proses eksekusi satu query.
Sederhananya, CTE dapat dianggap sebagai tabel sementara yang hanya digunakan di dalam query tersebut. Setelah query selesai dijalankan, CTE akan otomatis hilang dan tidak disimpan di dalam database.
CTE sangat berguna untuk memecah query yang kompleks menjadi beberapa bagian sehingga lebih mudah dibaca dan dipelajari.
Mengapa Menggunakan CTE?
Bayangkan Anda memiliki query dengan banyak JOIN, subquery, dan proses perhitungan.
Tanpa CTE, query bisa menjadi sangat panjang sehingga sulit dipahami.
Dengan CTE, proses tersebut dapat dibagi menjadi beberapa langkah yang lebih sederhana.
Manfaatnya antara lain:
- Query menjadi lebih rapi.
- Mudah dipelihara (maintenance).
- Mengurangi penulisan query yang berulang.
- Mempermudah proses debugging.
- Membantu memahami alur pengolahan data.
Cara Kerja Common Table Expression
CTE diawali dengan kata kunci WITH, kemudian diberi nama.
Struktur dasar penulisannya adalah sebagai berikut.
WITH nama_cte AS (
SELECT ...
)
SELECT *
FROM nama_cte;Alur kerjanya:
- SQL menjalankan query di dalam CTE.
- Hasil query disimpan sementara.
- Query utama menggunakan hasil tersebut.
- Setelah selesai, CTE otomatis dihapus dari memori.
Fungsi Common Table Expression (CTE)
CTE memiliki berbagai fungsi penting dalam pengelolaan database.
1. Membuat Query Lebih Mudah Dibaca
Daripada menuliskan subquery yang panjang, Anda dapat memindahkannya ke dalam CTE.
Contoh:
WITH pelanggan_bandung AS (
SELECT *
FROM pelanggan
WHERE kota = 'Bandung'
)
SELECT *
FROM pelanggan_bandung;Query di atas lebih mudah dipahami dibandingkan menggunakan subquery yang panjang.
2. Mengurangi Penggunaan Subquery Berulang
Tanpa CTE, subquery yang sama sering kali harus ditulis berulang kali.
Dengan CTE, query cukup dibuat satu kali lalu digunakan berkali-kali dalam query utama.
Hal ini membuat kode menjadi lebih singkat dan mudah dikelola.
3. Mempermudah Analisis Data
CTE sangat membantu ketika melakukan:
- laporan penjualan,
- analisis pelanggan,
- perhitungan stok,
- rekap transaksi,
- statistik penjualan.
Karena setiap langkah dapat dipisahkan menjadi beberapa bagian yang lebih jelas.
4. Mendukung Recursive Query
Salah satu kelebihan utama CTE adalah mendukung Recursive CTE.
Recursive CTE digunakan untuk mengambil data yang memiliki hubungan bertingkat, misalnya:
- struktur organisasi perusahaan,
- kategori produk,
- folder dan subfolder,
- silsilah keluarga.
Contoh Penggunaan CTE
Misalkan terdapat tabel produk.
| id | nama_produk | harga |
|---|---|---|
| 1 | Laptop | 9000000 |
| 2 | Printer | 2500000 |
| 3 | Mouse | 150000 |
Menampilkan produk dengan harga di atas Rp2.000.000 menggunakan CTE.
WITH produk_mahal AS (
SELECT *
FROM produk
WHERE harga > 2000000
)
SELECT *
FROM produk_mahal;Hasilnya:
| id | nama_produk | harga |
|---|---|---|
| 1 | Laptop | 9000000 |
| 2 | Printer | 2500000 |
Contoh CTE dengan JOIN
Misalkan terdapat dua tabel.
Tabel Pelanggan
| id | nama |
|---|---|
| 1 | Andi |
| 2 | Budi |
Tabel Pesanan
| id_pesanan | id_pelanggan | total |
|---|---|---|
| 101 | 1 | 450000 |
| 102 | 2 | 700000 |
Menggabungkan kedua tabel menggunakan CTE.
WITH data_pesanan AS (
SELECT
p.nama,
ps.total
FROM pelanggan p
JOIN pesanan ps
ON p.id = ps.id_pelanggan
)
SELECT *
FROM data_pesanan;Query menjadi lebih mudah dibaca dibandingkan jika seluruh proses JOIN ditulis dalam satu blok panjang.
Contoh CTE untuk Perhitungan
CTE juga dapat digunakan untuk menghitung nilai tertentu.
Misalnya menghitung total penjualan setiap pelanggan.
WITH total_penjualan AS (
SELECT
id_pelanggan,
SUM(total) AS total_belanja
FROM pesanan
GROUP BY id_pelanggan
)
SELECT *
FROM total_penjualan;Teknik seperti ini sering digunakan dalam dashboard laporan penjualan.
Recursive CTE
Recursive CTE digunakan ketika data memiliki hubungan bertingkat.
Contohnya:
- atasan dan bawahan,
- kategori produk,
- struktur organisasi,
- folder komputer.
Contoh sederhana:
WITH RECURSIVE angka AS (
SELECT 1 AS nomor
UNION ALL
SELECT nomor + 1
FROM angka
WHERE nomor < 5
)
SELECT *
FROM angka;Hasilnya:
| nomor |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
Recursive CTE banyak digunakan pada database yang mendukung fitur rekursif seperti PostgreSQL, MySQL 8.0+, SQL Server, dan SQLite.
Kelebihan Common Table Expression
Menggunakan CTE memberikan berbagai keuntungan.
Query Lebih Rapi
Kode SQL menjadi lebih mudah dibaca karena dibagi menjadi beberapa bagian.
Mudah Dipelihara
Jika terjadi perubahan query, pengembang cukup mengubah bagian CTE tanpa harus menulis ulang seluruh query.
Mengurangi Duplikasi
CTE dapat digunakan kembali dalam query yang sama sehingga tidak perlu membuat subquery berulang.
Mempermudah Debugging
Setiap CTE dapat diuji secara terpisah sehingga proses menemukan kesalahan menjadi lebih cepat.
Mendukung Query Rekursif
Fitur ini tidak dimiliki oleh subquery biasa.
Perbedaan CTE dan Subquery
| Common Table Expression | Subquery |
|---|---|
| Menggunakan kata kunci WITH | Ditulis langsung di dalam query |
| Lebih mudah dibaca | Lebih sulit dipahami jika kompleks |
| Dapat digunakan beberapa kali dalam satu query | Biasanya hanya digunakan satu kali |
| Mendukung Recursive Query | Tidak mendukung rekursi |
| Cocok untuk query yang kompleks | Cocok untuk query sederhana |
Kapan Sebaiknya Menggunakan CTE?
CTE sangat disarankan ketika:
- Query memiliki banyak JOIN.
- Menggunakan banyak subquery.
- Membuat laporan yang kompleks.
- Mengolah data dalam beberapa tahap.
- Membutuhkan Recursive Query.
- Ingin membuat query lebih mudah dipahami oleh tim.
Untuk query sederhana, penggunaan subquery biasa masih cukup efektif.
Tips Menggunakan CTE
Agar penggunaan CTE lebih optimal, berikut beberapa tips yang bisa diterapkan:
- Gunakan nama CTE yang jelas dan mudah dipahami.
- Hindari membuat terlalu banyak CTE jika tidak diperlukan.
- Pisahkan proses pengolahan data menjadi beberapa langkah logis.
- Gunakan CTE untuk meningkatkan keterbacaan query, bukan sekadar mengganti subquery.
- Pastikan database yang digunakan mendukung fitur CTE (misalnya MySQL 8.0 ke atas).
Kesalahan Umum Saat Menggunakan CTE
Beberapa kesalahan yang sering dilakukan pemula antara lain:
- Menganggap CTE tersimpan permanen di database.
- Memberikan nama CTE yang tidak menggambarkan isi datanya.
- Menggunakan CTE untuk query yang sangat sederhana sehingga justru membuat kode lebih panjang.
- Salah menuliskan sintaks
WITH. - Menggunakan Recursive CTE tanpa kondisi penghentian sehingga menyebabkan perulangan tak berujung.
Dengan memahami fungsi dan batasannya, Anda dapat memanfaatkan CTE secara lebih efektif.
Kesimpulan
Common Table Expression (CTE) pada SQL adalah fitur yang memungkinkan Anda membuat hasil query sementara menggunakan kata kunci WITH. CTE sangat membantu dalam menyusun query yang kompleks menjadi lebih terstruktur, mudah dibaca, dan lebih mudah dipelihara.
Selain meningkatkan keterbacaan kode, CTE juga mendukung query rekursif, sehingga sangat bermanfaat untuk mengelola data yang memiliki hubungan bertingkat. Dengan memahami cara kerja dan penerapannya, Anda dapat menulis query SQL yang lebih profesional, efisien, dan mudah dikembangkan.
