Apakah itu Common Table Expression (CTE)?

Question

Accepted Answer

Sebuah **CTE** (Common Table Expression), yang ditakrif dengan **`WITH`**, ialah set hasil sementara bernama yang boleh anda rujuk dalam sesuatu query. CTE menjadikan query yang kompleks lebih **mudah dibaca** dengan memecahkannya kepada langkah logik yang bernama — dan ia membolehkan query **rekursif** (untuk hierarki).

## CTE asas — namakan subquery untuk kebolehbacaan

```sql
WITH active_users AS (              -- define a named temporary result set
    SELECT id, name FROM users WHERE active = true
)
SELECT * FROM active_users          -- reference it like a table
WHERE name LIKE 'A%';
```

`WITH ... AS (...)` mentakrifkan sebuah CTE — query bernama yang anda rujuk kemudian. Ia seperti subquery, tetapi **bernama dan mudah dibaca**, terutamanya untuk query yang kompleks.

## Pelbagai CTE — merantai langkah logik

```sql
WITH
  monthly_sales AS (
    SELECT month, SUM(amount) AS total FROM orders GROUP BY month
  ),
  avg_sales AS (
    SELECT AVG(total) AS avg_total FROM monthly_sales   -- a CTE can reference another
  )
SELECT m.month, m.total
FROM monthly_sales m, avg_sales a
WHERE m.total > a.avg_total;        -- months above the average
```

Pelbagai CTE memecahkan query yang kompleks kepada langkah yang mudah dibaca dan berurutan — setiap satu dibina atas yang sebelumnya. Ini jauh lebih jelas berbanding subquery yang bersarang dalam.

## CTE berbanding subquery (kebolehbacaan)

```text
Nested subqueries → hard to read when deeply nested
CTEs → name each step, read top-to-bottom like a sequence → much clearer
→ CTEs are often preferred for COMPLEX queries purely for readability/maintainability.
```

## CTE rekursif — untuk hierarki

```sql
-- traverse a hierarchy (e.g. all descendants of a category, an org chart)
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1   -- base case (the top)
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id               -- recursive step
)
SELECT * FROM subordinates;          -- the whole tree under employee 1
```

**CTE rekursif** (`WITH RECURSIVE`) mengharungi data hierarki/pokok (carta organisasi, pokok kategori, bil-bahan, laluan graf) — sesuatu yang sebaliknya amat sukar dalam SQL.

## Mengapa ia penting

CTE ialah ciri SQL yang penting untuk **kebolehbacaan dan rekursi**, dan memahaminya bernilai untuk menulis query kompleks yang boleh diselenggara dan untuk mengendalikan data hierarki.

Manfaat harian utama ialah **kebolehbacaan**: query kompleks dengan pelbagai tahap subquery menjadi sukar dibaca dan diselenggara, manakala CTE membolehkan anda memecahkan query kepada langkah logik yang bernama dan berurutan (dibaca dari atas ke bawah seperti satu siri operasi), meningkatkan kejelasan dengan mendadak — atas sebab ini, CTE selalunya lebih disukai berbanding subquery yang bersarang dalam untuk query kompleks, menjadikannya alat penting untuk menulis SQL yang boleh diselenggara.

Sama pentingnya ialah keupayaan **CTE rekursif** (`WITH RECURSIVE`), yang membolehkan query **data berstruktur hierarki dan pokok** (carta organisasi, pokok kategori, bil-bahan, perjalanan graf) — sekelas query yang sebaliknya amat sukar atau mustahil dalam SQL standard, menjadikan CTE rekursif penyelesaian standard untuk perjalanan data hierarki.

Memahami kedua-dua kegunaan — CTE untuk menguraikan query kompleks kepada langkah yang mudah dibaca, dan CTE rekursif untuk perjalanan hierarki — merangkumi nilai praktikalnya.

Memandangkan query kompleks adalah biasa dalam aplikasi sebenar (dan kebolehbacaan/kebolehselenggaraan adalah penting), dan memandangkan data hierarki (yang dikendalikan dengan unik oleh CTE rekursif) kerap muncul (struktur organisasi, kategori bersarang, data berbenang), memahami CTE adalah pengetahuan peringkat senior yang bernilai yang meningkatkan kebolehselenggaraan query dan membuka kunci query hierarki — alat yang kerap relevan untuk menulis SQL yang kemas dan canggih serta topik biasa yang menunjukkan kemampuan SQL lanjutan, terutamanya bentuk rekursif untuk data berstruktur pokok.