Common Table Expressions (CTEs)

Objectives

By the end of this lesson, you should be able to:

• Name a subquery with WITH ... AS and reference it like a table
• Chain multiple CTEs together in one query
• Explain how a CTE improves on a subquery in FROM for readability

💡 Why this matters: The FROM-clause subquery from two lessons ago works, but nesting several of them gets hard to read fast, especially indented inside each other. A Common Table Expression (CTE) gives a subquery a name up front, then the rest of the query reads top to bottom instead of inside out.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier lessons.

WITH … AS

WITH dept_averages AS (
  SELECT department, ROUND(AVG(salary), 2) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT department, avg_salary FROM dept_averages WHERE avg_salary > 65000 ORDER BY department;

 department  | avg_salary
--------------+------------
 Engineering |   86000.00
 Marketing   |   69000.00

WITH dept_averages AS (...) defines a CTE named dept_averages, the query inside the parentheses runs first, and the rest of the statement can reference dept_averages exactly like a real table. This produces the identical result to the FROM-clause subquery from Lesson 1, the difference is entirely about how it reads: the CTE’s purpose (department averages) is named before it’s used, rather than discovered by reading into a nested FROM clause.

Chaining Multiple CTEs

WITH dept_averages AS (
  SELECT department, ROUND(AVG(salary), 2) AS avg_salary
  FROM employees
  WHERE department IS NOT NULL
  GROUP BY department
),
above_company_avg AS (
  SELECT department, avg_salary FROM dept_averages
  WHERE avg_salary > (SELECT AVG(salary) FROM employees)
)
SELECT * FROM above_company_avg ORDER BY department;

 department  | avg_salary
--------------+------------
 Engineering |   86000.00

A comma separates multiple CTE definitions, and each one after the first can reference the ones defined before it, above_company_avg uses dept_averages directly, as if it were an ordinary table. Only Engineering’s department average (86000.00) exceeds the company-wide average, so it’s the only row left. This kind of multi-step logic, department averages, then which of those exceed the company average, would require deeply nested subqueries without CTEs to keep it this readable.

CTEs as Named Building Blocks

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 70000
)
SELECT department, COUNT(*) AS high_earner_count
FROM high_earners
GROUP BY department
ORDER BY department;

 department  | high_earner_count
--------------+---------------------
 Engineering |                   3
 Marketing   |                   1

A CTE doesn’t have to compute an aggregate, high_earners here is just a filtered, named subset of employees, reused by the rest of the query as needed. The same result could come from a plain WHERE clause without a CTE at all, the CTE’s value here is purely readability, naming the intermediate step (“start with high earners, then…”) rather than folding everything into one condition.

Try It

1. Write a CTE named sales_team selecting every employee in the Sales department, then select from it.
2. Write two chained CTEs: the first computing each department’s total payroll, the second filtering to departments with total payroll over 100000.
3. Rewrite Lesson 1’s FROM-subquery example (department averages over 65000) as a CTE instead, and compare the two versions side by side.
4. Explain, in your own words, when you’d reach for a CTE instead of a plain subquery, given that both can produce identical results.

Recap

• WITH name AS (query) defines a CTE, a named, reusable subquery referenced like a table in the rest of the statement.
• Multiple CTEs can be chained with commas, each one able to reference CTEs defined before it.
• A CTE produces the same result a subquery could, its main advantage is readability, naming each logical step instead of nesting queries inside each other.

Next lesson: CASE, adding conditional logic directly inside a query.