CTE SQL adalah: WITH dan Contohnya — SQL

CTE (Common Table Expression) adalah query sementara bernama yang dibuat dengan WITH, bikin query kompleks jadi mudah dibaca. Lengkap dengan contoh.

CTE (Common Table Expression) adalah query yang kamu beri nama di awal pakai WITH, lalu kamu pakai di query utama seperti tabel biasa. Bayangin resep masakan. Resep yang susah dibaca menulis semuanya dalam satu kalimat: "tuang ke wajan bumbu yang sudah dihaluskan dari bawang yang sudah diiris". Resep yang enak dibaca memberi nama ke langkah di tengahnya: bikin bumbu halus dulu, baru "tuang bumbu halus ke wajan". CTE melakukan hal yang sama ke query: langkah di tengah diberi nama, dan query-nya dibaca dari atas ke bawah.

🧪 Tebakan cepat: sebuah query ditulis pakai subquery, lalu ditulis ulang pakai CTE. Hasilnya beda, atau sama persis? Tebak dulu, jawabannya ada di bagian "Dari subquery ke CTE".

Dari subquery ke CTE

Di pelajaran Subquery kamu mencari produk yang harganya di atas rata-rata dengan WHERE price > (SELECT AVG(price) FROM products). Sekarang pertanyaannya sama, datanya tabel employees berisi 5 karyawan (id, name, salary): Budi 5.000.000, Siti 7.000.000, Andi 9.000.000, Dewi 12.000.000, dan Rizky 4.000.000.

Versi subquery, yang sudah kamu kenal:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Versi CTE:

WITH avg_salary AS (
  SELECT AVG(salary) AS nilai FROM employees
)
SELECT name, salary
FROM employees
WHERE salary > (SELECT nilai FROM avg_salary)
ORDER BY salary DESC;

Dua-duanya menghasilkan 2 baris yang sama persis: Dewi 12.000.000, lalu Andi 9.000.000. Itu jawaban tebakannya: CTE gak mengubah hasil. Dia cuma memindahkan subquery ke atas dan memberinya nama.

Beberapa CTE berurutan

Pertanyaannya ditambah: berapa orang yang gajinya di atas rata-rata, dan berapa rata-rata gaji mereka? Dengan subquery, jawabannya jadi kurung di dalam kurung: subquery di FROM (dipakai seperti tabel, jadi wajib diberi nama dengan AS) yang di dalamnya ada subquery lagi.

SELECT COUNT(*) AS jumlah, AVG(salary) AS rata_rata
FROM (
  SELECT name, salary FROM employees
  WHERE salary > (SELECT AVG(salary) FROM employees)
) AS high_earners;

Dengan CTE, langkahnya dijejer dari atas ke bawah:

WITH avg_salary AS (
  SELECT AVG(salary) AS nilai FROM employees
),
high_earners AS (
  SELECT name, salary FROM employees
  WHERE salary > (SELECT nilai FROM avg_salary)
)
SELECT COUNT(*) AS jumlah, AVG(salary) AS rata_rata
FROM high_earners;

Hasil keduanya sama: jumlah 2 dan rata_rata 10.500.000 (Dewi dan Andi).

WITH RECURSIVE: menelusuri pohon

Kategori toko online sering berbentuk pohon. Tiap kategori menyimpan parent_id, yaitu id kategori di atasnya (NULL kalau dia paling atas):

Elektronik (1)
  Komputer (2)
    Laptop (4)
    Aksesoris (5)
      Mouse (6)
  Handphone (3)
Fashion (7)
  Kaos (8)

Kamu mau semua sub-kategori di bawah Elektronik, sedalam apa pun. JOIN biasa cuma turun satu tingkat. WITH RECURSIVE mengulang JOIN-nya sampai gak ada anak lagi:

WITH RECURSIVE category_tree AS (
  -- anchor: titik mulai, jalan sekali
  SELECT id, name, parent_id, 0 AS level
  FROM categories
  WHERE id = 1

  UNION ALL

  -- rekursif: anak dari baris yang sudah ketemu
  SELECT c.id, c.name, c.parent_id, ct.level + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

Hasil dari SQLite, ditambah kolom putaran yang menemukan tiap baris:

Putaran id name parent_id level
anchor 1 Elektronik NULL 0
1 3 Handphone 1 1
1 2 Komputer 1 1
2 5 Aksesoris 2 2
2 4 Laptop 2 2
3 6 Mouse 5 3

Kata RECURSIVE wajib ditulis. SQLite memaafkan kalau kamu lupa, tapi PostgreSQL dan MySQL menolak query-nya.

CTE atau subquery?

Subquery CTE
Bentuk di dalam kurung, di tengah query diberi nama di atas, pakai WITH
Cara baca dari dalam ke luar dari atas ke bawah
Dipakai dua kali diketik ulang ditulis sekali
Rekursi gak bisa bisa, pakai WITH RECURSIVE
Kecepatan sama di kebanyakan database sama di kebanyakan database

CTE lebih tepat ketika hasil yang sama dipakai dua kali atau lebih, ketika query mulai susah dibaca, atau ketika kamu butuh rekursi. CTE bukan trik supaya query lebih cepat. Untuk satu angka yang dipakai sekali, subquery pendek sudah cukup.

📌 Beda SQLite, PostgreSQL dan MySQL

Latihan dan Lab di sini memakai SQLite. CTE dan WITH RECURSIVE ada di SQLite (sejak versi 3.8.3), PostgreSQL, dan MySQL 8.0 ke atas. MySQL 5.7 belum punya CTE sama sekali.

⚠️ Jebakan yang sering ketemu

TL;DR: CTE adalah query bernama: WITH nama AS (SELECT ...), lalu langsung query utama yang memakai nama seperti tabel, tanpa titik koma di antaranya. Hasilnya sama dengan subquery, tapi dibaca dari atas ke bawah dan bisa dipakai berkali-kali. Beberapa CTE dipisah koma dengan satu WITH. WITH RECURSIVE berisi anchor yang jalan sekali, UNION ALL, dan bagian rekursif yang memanggil dirinya sendiri sampai satu putaran gak menemukan baris baru, jadi pastikan ada syarat berhentinya. CTE gak bikin query lebih cepat: pakai ketika hasilnya dipakai dua kali, query-nya susah dibaca, atau kamu butuh rekursi.

Yang akan kamu pelajari