CTE (Common Table Expression) — SQL

CTE (Common Table Expression) adalah query sementara yang kamu beri nama, untuk dipakai dalam query utama. Sintaksnya pakai keyword WITH. CTE bikin query komple

CTE (Common Table Expression) adalah query sementara yang kamu beri nama, untuk dipakai dalam query utama. Sintaksnya pakai keyword WITH. CTE bikin query kompleks lebih mudah dibaca — seperti variabel lokal di SQL.

Sintaks Dasar

WITH nama_cte AS (
    SELECT ...dari query apapun...
)
SELECT * FROM nama_cte WHERE ...;

Contoh: Replace Subquery yang Berulang

Sebelum (subquery duplikat, sulit dibaca):

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
  AND department_id IN (
    SELECT department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)
  );

Sesudah (dengan CTE, jauh lebih jelas):

WITH avg_salary AS (
    SELECT AVG(salary) AS avg_val FROM employees
),
high_earners AS (
    SELECT * FROM employees WHERE salary > (SELECT avg_val FROM avg_salary)
)
SELECT name, salary FROM high_earners;

Query dipecah jadi langkah-langkah yang bisa di-follow dari atas ke bawah.

Multi-CTE dalam Satu Query

Kamu bisa definisikan beberapa CTE sekaligus, pisah dengan koma:

WITH
    active_users AS (
        SELECT id, name FROM users WHERE status = 'active'
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) as order_count
        FROM orders
        GROUP BY user_id
    )
SELECT au.name, COALESCE(uo.order_count, 0) as total_orders
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id
ORDER BY total_orders DESC;

Recursive CTE — Query Hierarkis

CTE rekursif bisa memanggil dirinya sendiri — sangat berguna untuk data berbentuk tree (kategori bersarang, org chart, path traversal).

-- Cari semua descendant dari kategori dengan id = 1
WITH RECURSIVE category_tree AS (
    -- Anchor: kategori root
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE id = 1

    UNION ALL

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

Hasilnya semua kategori dari root sampai daun paling dalam, lengkap dengan kedalaman level.

CTE vs Subquery — Kapan Pakai Apa?

Subquery CTE
Sintaks Inline di dalam query Dinamai dulu, lalu dipakai
Reusability Copy-paste kalau mau dipakai 2x Didefinisikan sekali, dipakai berulang
Readability Nested, sulit dibaca kalau dalam Linear, mudah dibaca
Performance Sama (kebanyakan DB) Sama (kebanyakan DB)
Recursive Tidak bisa Bisa (WITH RECURSIVE)

Rule of thumb: pakai subquery untuk hal sederhana 1-liner, pakai CTE begitu query mulai butuh dokumentasi untuk dimengerti.

Dukungan Database

Tips

Yang akan kamu pelajari