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

CTE (WITH clause) đặt tên cho subquery phức tạp để tái sử dụng và dễ đọc — Recursive CTE giải quyết hierarchical data (org tree, categories); PostgreSQL 12+ tự chọn materialized hay inline tùy cost.

• CTE (WITH clause) là named temporary result set tồn tại trong scope của query — WITH active_users AS (SELECT FROM users WHERE deleted_at IS NULL), premium AS (SELECT user_id FROM subscriptions WHERE plan = 'premium') SELECT u. FROM active_users u JOIN premium p ON u.id = p.user_id.
• Lợi ích: dễ đọc hơn nested subqueries, có thể reference nhiều lần trong query chính, hỗ trợ chaining CTEs.
• Materialization behavior (PostgreSQL): mặc định CTEs được materialized (tính một lần, lưu temp) — optimizer không thể push predicates vào CTE, đôi khi chậm hơn subquery; thêm NOT MATERIALIZED để optimizer inline: WITH cte AS NOT MATERIALIZED (...).
• Recursive CTE — org hierarchy example: 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 — tìm toàn bộ cây tổ chức.
• CTE vs subquery performance: trong PostgreSQL < 12, CTE luôn materialized (optimization fence); PostgreSQL 12+ thông minh hơn.
• CTE trong DML: WITH deleted AS (DELETE FROM logs WHERE created_at < NOW() - INTERVAL '90 days' RETURNING ) INSERT INTO archive SELECT FROM deleted — atomic delete + archive.