Tutorials › PostgreSQL Essentials › CTEs (WITH Clause)

CTEs (WITH Clause)

6 min read Quiz at the end

CTEs use WITH to name a subquery and reuse it in the main query. They break complex queries into readable named steps. Recursive CTEs traverse tree-shaped data like category hierarchies and org charts.

Common Table Expressions

WITH high_spenders AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 1000
)
SELECT u.name, hs.spent
FROM users u
JOIN high_spenders hs ON u.id = hs.user_id
ORDER BY hs.spent DESC;

CTEs make complex queries readable by naming intermediate steps.

← Subqueries Next: Window Functions →

Topic Quiz · 5 questions

Test your understanding before moving on

1. What does CTE stand for?

💡 CTE stands for Common Table Expression, introduced with WITH.

2. What keyword starts a CTE?

💡 WITH starts a CTE: WITH cte_name AS (SELECT ...).

3. What is the benefit of CTEs?

💡 CTEs improve readability by naming intermediate query steps.

4. Can a CTE be recursive?

💡 PostgreSQL supports recursive CTEs using WITH RECURSIVE.

5. What does a recursive CTE require?

💡 Recursive CTEs need a base case (anchor) and a recursive step connected by UNION ALL.

Quick Access

CTEs (WITH Clause)

Common Table Expressions

Test your understanding before moving on