Apa iku Common Table Expressions (CTEs)?

Question

IT Interview Practice · Accepted Answer

A **CTE** (Common Table Expression), defined with **`WITH`**, is a named temporary result set you can reference within a query. CTEs make complex queries more **readable** by breaking them into named, logical steps — and they enable **recursive** queries (for hierarchies).

## Sebuah CTE dasar — naming a subquery kanggo 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%';
```

The `WITH ... AS (...)` defines a CTE — a named query you reference later. It's like a subquery, but **named and readable**, especially for complex queries.

## Multiple CTEs — chaining logical steps

```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 break a complex query into readable, sequential steps — each building on the previous. This is far clearer than 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 — for 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`) traverse hierarchical/tree data (org charts, category trees, bill-of-materials, graph paths) — something otherwise very hard in SQL.

## Apa iku wigati

CTEs adalah fitur SQL penting kanggo **readability and recursion**, lan paham bakal berharga kanggo nulis maintainable complex queries lan kanggo handle hierarchical data.

The primary everyday benefit is **readability**: complex queries with multiple levels of subqueries become hard to read and maintain, while CTEs let you break the query into named, sequential logical steps (read top-to-bottom like a series of operations), dramatically improving clarity — for this reason, CTEs are often preferred over deeply nested subqueries for complex queries, making them an important tool for writing maintainable SQL.

Equally important is the **recursive CTE** capability (`WITH RECURSIVE`), which enables querying **hierarchical and tree-structured data** (organizational charts, category trees, bill-of-materials, graph traversal) — a class of queries that's otherwise very difficult or impossible in standard SQL, making recursive CTEs the standard solution for hierarchical data traversal.

Pamahaman loro uses — CTEs kanggo decomposing complex queries into readable steps, lan recursive CTEs kanggo hierarchical traversal — covers praktis value.

Since complex queries are common in real applications (and readability/maintainability matter), and since hierarchical data (which recursive CTEs uniquely handle well) appears frequently (org structures, nested categories, threaded data), understanding CTEs is valuable senior-level knowledge that improves query maintainability and unlocks hierarchical querying — a frequently-relevant tool for writing clean, sophisticated SQL and a common topic that demonstrates advanced SQL capability, particularly the recursive form for tree-structured data.

Sebuah CTE dasar — naming a subquery kanggo readability

Multiple CTEs — chaining logical steps

CTEs vs subqueries (readability)

Recursive CTEs — for hierarchies

Apa iku wigati

Pitakon sing gegandhengan