Common Table Expression (CTE)

A CTE is a named, temporary result set defined with the WITH keyword at the top of a query. It improves readability and, unlike a subquery, can be recursive and referenced multiple times.

CTEs let you break a complex query into named, top-down steps instead of deeply nested subqueries. A recursive CTE (WITH RECURSIVE) can walk hierarchies such as org charts or bill-of-materials trees.

A CTE is scoped to the single statement that follows it. For results reused across many statements, a temporary table or view may be more appropriate.

Example

WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM   employees
  GROUP BY department_id
)
SELECT * FROM dept_avg WHERE avg_sal > 8000;