CTE (WITH) là gì và khác subquery thế nào?

CTE (Common Table Expression) là truy vấn phụ đặt tên khai báo bằng WITH, dùng lại như một bảng tạm trong câu lệnh chính:

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT customer_id, count(*) FROM recent_orders GROUP BY customer_id;

So với subquery lồng trong FROM/WHERE:
- CTE giúp truy vấn phức tạp dễ đọc, chia thành các bước có tên, tránh lặp lại cùng một subquery.
- Có thể tham chiếu nhiều lần trong câu chính.
- Hỗ trợ recursive CTE (WITH RECURSIVE) để duyệt cây/đồ thị (danh mục cha–con, chuỗi tổ chức) — điều subquery thường không làm được.
- Dùng được trong DML — xóa + lưu trữ nguyên tử: WITH deleted AS (DELETE ... RETURNING ) INSERT INTO archive SELECT FROM deleted.

Lưu ý hiệu năng: bản chất kết quả giống nhau, nhưng ở PostgreSQL cũ (trước 12) CTE là optimization fence (materialize, không đẩy điều kiện vào trong). Từ Postgres 12 planner có thể inline CTE không recursive; muốn giữ hàng rào thì dùng WITH ... AS MATERIALIZED.