CTE with Multiple References
CTE with Multiple References is a SQL statement in the CTE (WITH) category. A CTE can be referenced multiple times in the same query. PostgreSQL may materialize it once for efficiency. The syntax is WITH cte AS (...) SELECT * FROM cte WHERE ... UNION ALL SELECT * FROM cte WHERE ...;. It returns multi-reference CTE result. A typical example: WITH high_earners AS ( SELECT * FROM employees WHERE salary > 80000 ) SELECT 'High Salary' AS category, name, salary FROM high_earners UNION ALL SELECT 'Very High' AS category, name, salary FROM high_earners WHERE salary > 120000 ORDER BY salary DESC; -- The CTE high_earners is referenced twice -- PostgreSQL optimizes: evaluates CTE once, uses result twice A close relative is WITH (CTE), which common Table Expression — define a named temporary result set that can be referenced in the main query. A close relative is Recursive CTE, which a CTE that references itself, allowing hierarchical or recursive queries (org charts, tree structures).