CTEs (Common Table Expressions) trong SQL là gì? Lợi ích?

CTE (mệnh đề WITH) là cách đặt tên cho một subquery để dùng lại và đọc dễ hơn — như khai báo "biến tạm" cho truy vấn:

WITH active_users AS (SELECT * FROM users WHERE deleted_at IS NULL)
SELECT * FROM active_users WHERE country = 'VN';

Lợi ích: dễ đọc hơn subquery lồng nhau, tham chiếu được nhiều lần, nối chuỗi nhiều CTE.

Recursive CTE giải quyết dữ liệu phân cấp (cây tổ chức, danh mục cha-con):

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
) SELECT * FROM org ORDER BY depth;

Lưu ý hiệu năng: PostgreSQL < 12 luôn materialize CTE (tính một lần, như "hàng rào tối ưu"), đôi khi chậm hơn subquery; từ 12+ optimizer tự chọn, có thể ép bằng AS NOT MATERIALIZED.

Mẹo hay: dùng CTE trong DML để xóa + lưu trữ atomic — WITH deleted AS (DELETE ... RETURNING ) INSERT INTO archive SELECT FROM deleted.

A CTE (the WITH clause) names a subquery so you can reuse it and read it more easily — like declaring a "temp variable" for a query:

WITH active_users AS (SELECT * FROM users WHERE deleted_at IS NULL)
SELECT * FROM active_users WHERE country = 'VN';

Benefits: more readable than nested subqueries, referenceable multiple times, chainable.

Recursive CTEs solve hierarchical data (org trees, parent-child categories):

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
) SELECT * FROM org ORDER BY depth;

Performance note: PostgreSQL < 12 always materializes CTEs (computed once, an "optimization fence") which can be slower than a subquery; from 12+ the optimizer decides, and you can force it with AS NOT MATERIALIZED.

Handy trick: use a CTE in DML for an atomic delete + archive — WITH deleted AS (DELETE ... RETURNING ) INSERT INTO archive SELECT FROM deleted.