Common Table Expression (CTE) di SQL: Tutorial Lengkap

Apa Itu CTE (Common Table Expression)?

Pernah bikin query SQL yang panjang banget sampe kamu sendiri bingung bacanya? Nested subquery di mana-mana, alias yang ga jelas, dan pas mau debug rasanya mau nangis?

Nah, CTE itu solusinya.

CTE atau Common Table Expression adalah cara buat bikin "temporary result set" yang bisa kamu pake dalam satu query. Mirip bikin variabel dulu sebelum dipake. Query jadi lebih structured dan gampang dibaca.

Kenapa sih CTE penting banget?

• Query jadi lebih readable
• Gampang di-debug (bisa test per bagian)
• Bisa dipake berkali-kali dalam satu query
• Support recursive buat data hierarki

Apa yang Akan Kamu Pelajari

• [ ] Sintaks dasar CTE dengan WITH clause
• [ ] Keuntungan CTE dibanding subquery
• [ ] Multiple CTEs dalam satu query
• [ ] Recursive CTE untuk data hierarki
• [ ] 5 contoh penggunaan CTE yang praktis
• [ ] Performance considerations

Sintaks Dasar CTE

Ini struktur dasar CTE:

WITH nama_cte AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM nama_cte;

Simple kan? Kamu define dulu CTE-nya pake WITH, kasih nama, terus isi dengan query. Setelah itu, kamu bisa pake nama CTE itu kayak tabel biasa.

Dataset yang Akan Kita Pakai

Buat tutorial ini, kita pakai dataset perusahaan Indonesia. Ada tabel karyawan dengan struktur hierarki.

Tabel: karyawan

Tabel: orders

Contoh 1: CTE Basic untuk Query yang Lebih Clean

Misalnya kamu mau cari karyawan yang gajinya di atas rata-rata departemennya. Tanpa CTE, query-nya bakal berantakan.

Tanpa CTE (Pake Subquery):

SELECT
    k.nama,
    k.departemen,
    k.gaji,
    (SELECT AVG(gaji) FROM karyawan WHERE departemen = k.departemen) AS avg_dept_gaji
FROM karyawan k
WHERE k.gaji > (
    SELECT AVG(gaji)
    FROM karyawan
    WHERE departemen = k.departemen
);

Subquery-nya diulang dua kali. Ga efisien dan susah dibaca.

Dengan CTE:

WITH avg_gaji_per_dept AS (
    SELECT
        departemen,
        AVG(gaji) AS avg_gaji
    FROM karyawan
    GROUP BY departemen
)
SELECT
    k.nama,
    k.departemen,
    k.gaji,
    a.avg_gaji AS avg_dept_gaji
FROM karyawan k
JOIN avg_gaji_per_dept a ON k.departemen = a.departemen
WHERE k.gaji > a.avg_gaji;

Hasil:

Lebih clean kan? CTE-nya dihitung sekali, terus dipake di JOIN dan WHERE.

Contoh 2: Multiple CTEs

Kamu bisa define lebih dari satu CTE dalam satu query. Tinggal pisahin pake koma.

Misalnya, kamu mau cari departemen yang total gajinya di atas rata-rata dan punya lebih dari 3 karyawan.

WITH gaji_per_dept AS (
    SELECT
        departemen,
        SUM(gaji) AS total_gaji,
        COUNT(*) AS jumlah_karyawan
    FROM karyawan
    GROUP BY departemen
),
avg_total_gaji AS (
    SELECT AVG(total_gaji) AS rata_rata
    FROM gaji_per_dept
)
SELECT
    g.departemen,
    g.total_gaji,
    g.jumlah_karyawan,
    a.rata_rata AS avg_total_semua_dept
FROM gaji_per_dept g
CROSS JOIN avg_total_gaji a
WHERE g.total_gaji > a.rata_rata
  AND g.jumlah_karyawan > 3;

Hasil:

Dua CTE: pertama hitung per departemen, kedua hitung rata-rata. Terus digabungin di query utama.

Contoh 3: CTE untuk Step-by-Step Analysis

CTE juga berguna banget buat analisis yang bertahap. Setiap step jadi satu CTE.

Misalnya, kamu mau analisis performa sales per karyawan Finance:

WITH finance_employees AS (
    -- Step 1: Filter karyawan Finance
    SELECT karyawan_id, nama, jabatan
    FROM karyawan
    WHERE departemen = 'Finance'
),
sales_summary AS (
    -- Step 2: Hitung total sales per karyawan
    SELECT
        karyawan_id,
        COUNT(*) AS total_orders,
        SUM(amount) AS total_revenue
    FROM orders
    GROUP BY karyawan_id
),
final_report AS (
    -- Step 3: Gabungin data karyawan dengan sales
    SELECT
        fe.nama,
        fe.jabatan,
        COALESCE(ss.total_orders, 0) AS total_orders,
        COALESCE(ss.total_revenue, 0) AS total_revenue
    FROM finance_employees fe
    LEFT JOIN sales_summary ss ON fe.karyawan_id = ss.karyawan_id
)
SELECT
    *,
    CASE
        WHEN total_revenue >= 100000000 THEN 'Top Performer'
        WHEN total_revenue >= 50000000 THEN 'Good'
        WHEN total_revenue > 0 THEN 'Needs Improvement'
        ELSE 'No Sales'
    END AS performance_category
FROM final_report
ORDER BY total_revenue DESC;

Hasil:

Setiap CTE punya tugas spesifik. Gampang banget buat debug kalau ada error.

Contoh 4: CTE vs Subquery (Perbandingan)

Kapan pake CTE, kapan pake subquery? Ini aturan simpelnya:

Pake CTE kalau:
- Query kompleks dengan multiple steps
- Subquery yang sama dipake berkali-kali
- Mau query yang readable dan maintainable
- Butuh recursive query

Pake Subquery kalau:
- Query simple, satu level aja
- Subquery cuma dipake sekali
- Performance jadi concern (kadang subquery lebih cepat)

Contoh yang sama, dua versi:

-- Versi Subquery (untuk case simple, ini OK)
SELECT nama, gaji
FROM karyawan
WHERE gaji > (SELECT AVG(gaji) FROM karyawan);

-- Versi CTE (overkill untuk case ini)
WITH avg_gaji AS (
    SELECT AVG(gaji) AS rata_rata FROM karyawan
)
SELECT k.nama, k.gaji
FROM karyawan k, avg_gaji a
WHERE k.gaji > a.rata_rata;

Untuk case simple kayak gini, subquery lebih ringkas. Tapi kalau query-nya kompleks, CTE jauh lebih bagus.

Contoh 5: Recursive CTE untuk Data Hierarki

Nah ini yang keren. Recursive CTE bisa handle data hierarki kayak struktur organisasi.

Recursive CTE punya dua bagian:
1. Anchor member: Query awal (base case)
2. Recursive member: Query yang refer ke dirinya sendiri

Misalnya, kamu mau nampilin semua bawahan dari seorang manager, termasuk bawahan dari bawahannya (sampai level terbawah).

WITH RECURSIVE org_hierarchy AS (
    -- Anchor: Mulai dari CEO
    SELECT
        karyawan_id,
        nama,
        jabatan,
        manager_id,
        1 AS level,
        nama AS path
    FROM karyawan
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: Cari bawahan
    SELECT
        k.karyawan_id,
        k.nama,
        k.jabatan,
        k.manager_id,
        oh.level + 1,
        oh.path || ' > ' || k.nama AS path
    FROM karyawan k
    INNER JOIN org_hierarchy oh ON k.manager_id = oh.karyawan_id
)
SELECT
    level,
    REPEAT('  ', level - 1) || nama AS nama_indented,
    jabatan,
    path
FROM org_hierarchy
ORDER BY path;

Hasil:

Keren kan? Sekarang kamu bisa liat struktur organisasi lengkap dari satu query.

Contoh 6: Recursive CTE untuk Hitung Total Bawahan

Mau tau berapa total bawahan setiap manager, termasuk bawahan nggak langsung?

WITH RECURSIVE subordinates AS (
    -- Anchor: Setiap karyawan
    SELECT
        karyawan_id,
        nama,
        manager_id,
        karyawan_id AS root_manager
    FROM karyawan

    UNION ALL

    -- Recursive: Cari bawahan dari bawahan
    SELECT
        k.karyawan_id,
        k.nama,
        k.manager_id,
        s.root_manager
    FROM karyawan k
    INNER JOIN subordinates s ON k.manager_id = s.karyawan_id
    WHERE k.karyawan_id != s.root_manager  -- Hindari self-reference
)
SELECT
    m.nama AS manager,
    m.jabatan,
    COUNT(*) - 1 AS total_bawahan  -- Minus 1 karena include diri sendiri
FROM subordinates s
JOIN karyawan m ON s.root_manager = m.karyawan_id
GROUP BY m.karyawan_id, m.nama, m.jabatan
HAVING COUNT(*) > 1
ORDER BY total_bawahan DESC;

Hasil:

Budi (CEO) punya 11 bawahan total. Andi (CTO) punya 5 bawahan di cabang IT-nya.

Contoh 7: CTE untuk Running Total

CTE juga bagus buat kalkulasi running/cumulative.

WITH monthly_orders AS (
    SELECT
        DATE_TRUNC('month', order_date) AS bulan,
        SUM(amount) AS monthly_revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    bulan,
    monthly_revenue,
    SUM(monthly_revenue) OVER (ORDER BY bulan) AS cumulative_revenue
FROM monthly_orders
ORDER BY bulan;

Hasil:

CTE bikin query lebih clean. Agregasi bulanan di CTE, running total di query utama.

Common Mistakes yang Harus Dihindari

Mistake 1: Lupa RECURSIVE Keyword

-- SALAH (recursive tanpa keyword RECURSIVE)
WITH org_hierarchy AS (
    SELECT * FROM karyawan WHERE manager_id IS NULL
    UNION ALL
    SELECT k.* FROM karyawan k
    JOIN org_hierarchy oh ON k.manager_id = oh.karyawan_id  -- Error!
)
SELECT * FROM org_hierarchy;

-- BENAR
WITH RECURSIVE org_hierarchy AS (
    -- ...
)
SELECT * FROM org_hierarchy;

Kalau CTE refer ke dirinya sendiri, HARUS pake keyword RECURSIVE.

Mistake 2: Infinite Loop di Recursive CTE

-- POTENSI INFINITE LOOP
WITH RECURSIVE bad_cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM bad_cte  -- Ga ada termination condition!
)
SELECT * FROM bad_cte;

Selalu pastikan ada kondisi yang bikin recursion berhenti. Bisa pake WHERE atau LIMIT.

-- BENAR (pake termination)
WITH RECURSIVE good_cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM good_cte WHERE n < 10  -- Stop di 10
)
SELECT * FROM good_cte;

Mistake 3: CTE Ga Dipake

-- SIA-SIA (define CTE tapi ga dipake)
WITH unused_cte AS (
    SELECT * FROM karyawan
)
SELECT * FROM orders;  -- CTE-nya kemana?

Kalau define CTE, pastikan dipake di query utama.

Mistake 4: Nama CTE Sama dengan Nama Tabel

-- CONFUSING
WITH karyawan AS (  -- Sama kayak nama tabel asli
    SELECT * FROM karyawan WHERE departemen = 'IT'
)
SELECT * FROM karyawan;  -- Yang mana nih?

Kasih nama CTE yang deskriptif dan beda dari nama tabel.

-- LEBIH JELAS
WITH it_employees AS (
    SELECT * FROM karyawan WHERE departemen = 'IT'
)
SELECT * FROM it_employees;

Performance Considerations

CTE Materialized vs Non-Materialized

Di PostgreSQL dan beberapa database lain, CTE bisa jadi "materialized" (hasil disimpan sementara) atau "non-materialized" (di-inline kayak subquery).

-- PostgreSQL 12+: Paksa non-materialized
WITH my_cte AS NOT MATERIALIZED (
    SELECT * FROM karyawan
)
SELECT * FROM my_cte WHERE departemen = 'IT';

Non-materialized kadang lebih cepat karena optimizer bisa push down filter.

Kapan CTE Lebih Lambat

1. CTE yang cuma dipake sekali kadang lebih lambat dari subquery
2. Recursive CTE dengan depth dalam bisa slow
3. CTE yang return data besar terus di-filter lagi

Tips Performance

1. Profile query kamu dengan EXPLAIN ANALYZE
2. Kalau CTE cuma dipake sekali dan simple, pertimbangkan subquery
3. Untuk recursive CTE, limit kedalaman kalau bisa
4. Index kolom yang dipake di JOIN dalam recursive CTE

Tips dan Best Practices

1. Nama CTE yang Deskriptif

-- Kurang bagus
WITH a AS (...), b AS (...)

-- Lebih bagus
WITH active_employees AS (...), sales_summary AS (...)

Nama yang jelas bikin query self-documenting.

2. Satu CTE, Satu Tugas

Jangan bikin CTE yang ngerjain terlalu banyak hal. Break down jadi beberapa CTE.

3. Comment Complex CTEs

WITH
-- Ambil karyawan yang aktif (masuk sebelum 2023)
active_employees AS (
    SELECT * FROM karyawan WHERE tanggal_masuk < '2023-01-01'
),
-- Hitung performa dari active employees
performance_summary AS (
    ...
)

4. Test CTE Secara Terpisah

Sebelum jalanin query lengkap, test setiap CTE dulu:

-- Test CTE pertama dulu
WITH my_cte AS (
    SELECT ...
)
SELECT * FROM my_cte LIMIT 10;

Latihan

Coba kerjain query ini:

Soal: Bikin query yang nampilin setiap karyawan beserta nama manager-nya dan nama "grand manager" (manager dari manager). Pake CTE biar readable.

WITH employee_manager AS (
    SELECT
        e.karyawan_id,
        e.nama AS employee_name,
        e.jabatan,
        m.nama AS manager_name,
        m.manager_id AS grand_manager_id
    FROM karyawan e
    LEFT JOIN karyawan m ON e.manager_id = m.karyawan_id
),
full_hierarchy AS (
    SELECT
        em.karyawan_id,
        em.employee_name,
        em.jabatan,
        COALESCE(em.manager_name, 'No Manager') AS manager_name,
        COALESCE(gm.nama, 'No Grand Manager') AS grand_manager_name
    FROM employee_manager em
    LEFT JOIN karyawan gm ON em.grand_manager_id = gm.karyawan_id
)
SELECT *
FROM full_hierarchy
ORDER BY karyawan_id;

FAQ

CTE sama subquery, bagusan mana?

Tergantung case-nya. Kalau query simple dan cuma dipake sekali, subquery lebih ringkas. Kalau query kompleks, multiple steps, atau hasil yang sama dipake berkali-kali, CTE menang jauh soal readability. Contoh 4 di atas nunjukin dua versi query yang sama. Buat case simple, CTE malah overkill.

Apakah CTE bikin query lebih cepat?

Nggak selalu. CTE itu soal readability, bukan speed. Di PostgreSQL 12+ optimizer bisa inline CTE kayak subquery, jadi performanya mirip. Tapi CTE yang cuma dipake sekali kadang malah lebih lambat dari subquery. Cek pake EXPLAIN ANALYZE dulu sebelum kamu simpulin mana yang lebih cepat.

Semua database support CTE nggak?

Hampir semua database modern support: PostgreSQL, MySQL 8.0+, SQL Server, Oracle, SQLite, sampai BigQuery. Yang perlu kamu cek itu MySQL versi lama. Di bawah 8.0 belum ada CTE. Sintaks recursive juga beda dikit antar database, misalnya SQL Server nggak butuh keyword RECURSIVE.

Bisa nggak CTE dipake di UPDATE atau DELETE?

Bisa kok. Di PostgreSQL dan SQL Server, kamu bisa pake WITH di depan UPDATE, DELETE, atau INSERT. Pola umumnya: filter data yang mau diubah lewat CTE, terus reference CTE-nya di statement utama. Ini berguna banget buat cleanup data yang kondisinya kompleks.

Kesimpulan

CTE ngebantu banget buat query SQL yang kompleks. Inget poin-poin ini:

1. CTE bikin query lebih readable dan maintainable
2. Pake WITH clause untuk define CTE
3. Bisa bikin multiple CTEs dalam satu query
4. Recursive CTE buat data hierarki (jangan lupa keyword RECURSIVE)
5. Nama deskriptif bikin query self-documenting
6. Test dan profile untuk performance

CTE itu kayak refactoring di programming. Query tetep jalan sama, tapi jauh lebih gampang dibaca dan di-maintain.

Selanjutnya

Kalau kamu udah paham CTE, next step-nya:
- Window Functions - analisis yang lebih advanced
- Cohort Analysis - retention analysis pake CTE
- Subquery - kapan pake subquery vs CTE