CTE là gì và khác subquery như thế nào?

CTE (common table expression) là một kết quả tạm có tên, khai báo bằng WITH và dùng trong cùng câu lệnh. Subquery là một query không tên lồng bên trong query khác.

WITH recent AS (
  SELECT customer_id, SUM(total) AS spent
  FROM orders
  WHERE created_at >= CURRENT_DATE - 30
  GROUP BY customer_id
)
SELECT c.name, r.spent
FROM recent r
JOIN customers c ON c.id = r.customer_id
WHERE r.spent > 1000;

Cùng logic viết bằng derived-table subquery sẽ trả cùng các dòng. CTE thắng ở khả năng đọc: mỗi bước có tên và bạn đọc query từ trên xuống dưới. CTE cũng có thể được tham chiếu nhiều lần, và chỉ CTE mới viết được dạng recursive.

Về hiệu năng, đừng mặc định CTE là bảng tạm được cache. PostgreSQL 12+ sẽ inline CTE không đệ quy chỉ dùng một lần, trừ khi bạn viết MATERIALIZED. Engine khác có thể materialize hoặc không, nên hãy xem plan.

Dùng CTE để cấu trúc query phức tạp, còn subquery thường cho điều kiện nhỏ dùng một lần.