CTE và recursive query hoạt động như thế nào trong PostgreSQL?

Question

Accepted Answer

Một **CTE** (Common Table Expression), được định nghĩa bằng **`WITH`**, là một tập kết quả tạm thời có tên giúp các truy vấn phức tạp dễ đọc hơn. PostgreSQL hỗ trợ đầy đủ CTE, bao gồm cả **recursive** CTE (`WITH RECURSIVE`) để duyệt dữ liệu phân cấp/dạng cây.

## CTE cơ bản — xây dựng truy vấn dễ đọc

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- kết hợp các bước có tên một cách gọn gàng
```

CTE chia một truy vấn phức tạp thành các bước có tên, dễ đọc (nối tiếp nhau, mỗi bước tham chiếu đến bước trước) — rõ ràng hơn nhiều so với các subquery lồng nhau.

## Recursive CTE — duyệt cấu trúc phân cấp

```sql
-- tìm tất cả nhân viên cấp dưới của nhân viên 1 (một cấu trúc sơ đồ tổ chức)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: điểm bắt đầu
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: tìm các nhân viên có manager nằm trong kết quả tính được đến giờ
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- toàn bộ cây dưới quyền nhân viên 1
```

Một **recursive CTE** có một base case (các dòng khởi đầu) và một recursive step (`UNION ALL` join ngược lại chính CTE đó), lặp lại cho đến khi không còn dòng mới — hoàn hảo cho **cây và cấu trúc phân cấp** (sơ đồ tổ chức, cây danh mục, bill-of-materials, đường đi trên graph).

## Lưu ý về CTE materialization (đặc thù Postgres)

```sql
-- Postgres 12+ có thể INLINE CTE (tối ưu hóa chúng) theo mặc định; ép buộc hành vi nếu cần:
WITH x AS MATERIALIZED (...)      -- ép materialize (tính một lần, lưu lại)
WITH x AS NOT MATERIALIZED (...)  -- ép inline (tối ưu vào truy vấn chính)
```

Trước Postgres 12, CTE luôn là một "optimization fence" (được materialize); nay chúng có thể được inline để hiệu năng tốt hơn — `MATERIALIZED`/`NOT MATERIALIZED` điều khiển điều này.

## Tại sao điều này quan trọng

CTE và recursive query là các tính năng PostgreSQL có giá trị cho **khả năng đọc và dữ liệu phân cấp**, nên hiểu chúng hữu ích cho việc viết các truy vấn phức tạp dễ bảo trì và xử lý dữ liệu dạng cây.

Lợi ích thường ngày là **khả năng đọc**: các truy vấn phức tạp với nhiều cấp subquery trở nên khó bảo trì, trong khi CTE cho phép bạn phân rã chúng thành các bước logic có tên, tuần tự (đọc từ trên xuống dưới), cải thiện đáng kể sự rõ ràng — khiến CTE trở thành một công cụ quan trọng để viết SQL dễ bảo trì.

Khả năng nổi bật là **recursive CTE** (`WITH RECURSIVE`), cho phép truy vấn **dữ liệu phân cấp và dạng cây** (sơ đồ tổ chức, cây danh mục, bill-of-materials, bình luận dạng luồng, duyệt graph) — một loại truy vấn vốn rất khó trong SQL, khiến recursive CTE trở thành giải pháp chuẩn cho việc duyệt phân cấp.

Hiểu cấu trúc của recursive CTE (base case + recursive step join ngược lại chính nó) là kiến thức then chốt cho nhu cầu phổ biến này.

Một chi tiết đặc thù Postgres hữu ích là hành vi **CTE materialization** (Postgres 12+ có thể inline CTE để hiệu năng tốt hơn, điều khiển được bằng `MATERIALIZED`/`NOT MATERIALIZED`) — liên quan đến tối ưu hóa.

Vì các truy vấn phức tạp là phổ biến (và khả năng đọc quan trọng cho việc bảo trì), và vì dữ liệu phân cấp (mà recursive CTE xử lý đặc biệt tốt) xuất hiện thường xuyên (cấu trúc tổ chức, danh mục lồng nhau), nên hiểu CTE và recursive query là kiến thức PostgreSQL có giá trị, liên quan thực tiễn giúp cải thiện khả năng bảo trì truy vấn và mở khóa truy vấn phân cấp — một khả năng thường xuyên liên quan cho SQL gọn gàng, tinh tế và là một chủ đề phổ biến, đặc biệt là dạng recursive cho nhu cầu định kỳ truy vấn dữ liệu dạng cây.