Apa itu Common Table Expressions (CTEs)?

Question

IT Interview Practice · Accepted Answer

Sebuah **CTE** (Common Table Expression), didefinisikan dengan **`WITH`**, adalah sebuah named temporary result set yang dapat Anda referensikan dalam sebuah query. CTEs membuat query kompleks lebih **readable** dengan memecahnya menjadi langkah-langkah yang bernama dan logis — dan mereka mengaktifkan **recursive** queries (untuk hierarchies).

## Sebuah CTE dasar — beri nama sebuah subquery untuk readability

```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 (...)` mendefinisikan sebuah CTE — sebuah named query yang Anda referensikan nanti. Ini seperti sebuah subquery, tetapi **bernama dan readable**, terutama untuk query kompleks.

## Multiple CTEs — chaining langkah-langkah logis

```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
```

Multiple CTEs memecah sebuah query kompleks menjadi langkah-langkah yang readable dan sequential — masing-masing membangun di atas yang sebelumnya. Ini jauh lebih jelas daripada deeply nested subqueries.

## CTEs vs subqueries (readability)

```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.
```

## Recursive CTEs — untuk hierarchies

```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
```

**Recursive CTEs** (`WITH RECURSIVE`) melintasi data hierarchical/tree (org charts, category trees, bill-of-materials, graph paths) — sesuatu yang sebaliknya sangat sulit dalam SQL.

## Mengapa ini penting

CTEs adalah sebuah fitur SQL penting untuk **readability dan recursion**, dan memahami mereka bernilai untuk menulis complex queries yang maintainable dan untuk menangani hierarchical data.

Manfaat sehari-hari utama adalah **readability**: complex queries dengan multiple levels subqueries menjadi sulit dibaca dan dipelihara, sementara CTEs memungkinkan Anda memecah query menjadi named, sequential logical steps (dibaca top-to-bottom seperti serangkaian operasi), secara dramatis meningkatkan clarity — karena alasan ini, CTEs sering lebih dipilih daripada deeply nested subqueries untuk complex queries, menjadikan mereka alat penting untuk menulis maintainable SQL.

Equally penting adalah **recursive CTE** capability (`WITH RECURSIVE`), yang mengaktifkan querying **hierarchical dan tree-structured data** (organizational charts, category trees, bill-of-materials, graph traversal) — sebuah class queries yang sebaliknya sangat sulit atau tidak mungkin dalam standard SQL, menjadikan recursive CTEs solusi standar untuk hierarchical data traversal.

Memahami kedua uses — CTEs untuk mendekomposisi complex queries menjadi readable steps, dan recursive CTEs untuk hierarchical traversal — mencakup practical value.

Karena complex queries adalah common dalam real applications (dan readability/maintainability penting), dan karena hierarchical data (yang recursive CTEs secara unik menangani dengan baik) muncul frequently (org structures, nested categories, threaded data), memahami CTEs adalah valuable senior-level knowledge yang meningkatkan query maintainability dan membuka hierarchical querying — sebuah frequently-relevant tool untuk menulis clean, sophisticated SQL dan sebuah common topic yang mendemonstrasikan advanced SQL capability, particularly recursive form untuk tree-structured data.

Sebuah CTE dasar — beri nama sebuah subquery untuk readability

Multiple CTEs — chaining langkah-langkah logis

CTEs vs subqueries (readability)

Recursive CTEs — untuk hierarchies

Mengapa ini penting

Pertanyaan terkait