A CTE, introduced with WITH, names a subquery so it can be referenced later in the statement — mostly a readability tool, letting you break a complex query into named, sequential steps instead of nesting nested subqueries several levels deep:
WITH DeptAverages AS (
SELECT dept, AVG(salary) AS avg_salary
FROM Employees
GROUP BY dept
)
SELECT e.name, e.dept, e.salary, d.avg_salary
FROM Employees e
JOIN DeptAverages d ON e.dept = d.dept
WHERE e.salary > d.avg_salary;
-- employees earning more than their own department's average
Multiple CTEs can be chained in one WITH clause, each able to reference the ones defined before it, separated by commas. Whether a CTE is actually materialized (computed once and reused) or inlined into the query like a view (potentially re-evaluated) is an engine-specific optimizer decision — don't assume a CTE referenced multiple times is automatically computed only once, particularly on older Postgres versions, which historically always materialized CTEs (a behavior later versions relaxed).
A recursive CTE (WITH RECURSIVE) is the standard way to query hierarchical or graph-like data (an org chart, a category tree) that a fixed number of joins can't express, though it's a large enough topic to deserve its own treatment beyond this reference.