Multiple CTEs in one query in PostgreSQL

Introduction

Using multiple CTEs helps break down complex queries into smaller, easy-to-understand parts.

When you want to organize a big query into steps for clarity.

When you need to reuse intermediate results multiple times in one query.

When you want to improve readability by naming parts of your query.

When you want to separate different calculations before combining results.

Syntax

PostgreSQL

WITH cte1 AS (
  -- first query
),
cte2 AS (
  -- second query
)
SELECT * FROM cte1 JOIN cte2 ON cte1.id = cte2.id;

Each CTE is separated by a comma.

You can use the CTE names later in the main query.

Examples

This example uses two CTEs to get user names and their order totals, then joins them.

PostgreSQL

WITH first_cte AS (
  SELECT id, name FROM users
),
second_cte AS (
  SELECT user_id, order_total FROM orders
)
SELECT first_cte.name, second_cte.order_total
FROM first_cte
JOIN second_cte ON first_cte.id = second_cte.user_id;

This example creates a list of numbers and their squares using two CTEs.

PostgreSQL

WITH numbers AS (
  SELECT generate_series(1,5) AS num
),
squares AS (
  SELECT num, num * num AS square FROM numbers
)
SELECT * FROM squares;

Sample Program

This query uses two CTEs: one for employees and one for departments. Then it joins them to show each employee's department.

PostgreSQL

WITH employees AS (
  SELECT id, name, department_id FROM employee
),
departments AS (
  SELECT id, department_name FROM department
)
SELECT employees.name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.id
ORDER BY employees.name;

Important Notes

CTEs run before the main query and act like temporary tables.

Using multiple CTEs can make your SQL easier to read and maintain.

Summary

Multiple CTEs let you split complex queries into smaller parts.

Each CTE is named and separated by commas.

You can join or use CTEs in the final query to get combined results.