Writing SQL queries can quickly get out of hand when subqueries stack up and logic gets buried. That’s where Common Table Expressions (CTEs) step in. If you're just starting with PostgreSQL, this guide will help you understand what CTEs are, why they're useful, and how to write them from scratch with confidence.
What is a CTE?
A Common Table Expression (CTE) is a temporary named result set that you define using the WITH clause. It exists only during the execution of the query that references it.
Think of a CTE as a way to "store" an intermediate result and give it a name, so your query becomes cleaner, easier to follow, and more modular.
Basic Syntax of a CTE
Here's what a CTE looks like:
WITH cte_name AS (
SELECT column1, column2
FROM some_table
WHERE condition
)
SELECT *
FROM cte_name;
Breakdown:
- WITH: Starts the CTE block.
- cte_name: A temporary name for your result.
- Inside the parentheses: A subquery that produces the result.
- After the WITH clause: The main query that uses the CTE.
Why Use a CTE?
CTEs make your queries:
- Easier to read: No more nested subqueries that are hard to follow.
- Easier to maintain: You can update the logic in one place.
- More modular: Break down complex logic into steps.
- Capable of recursion: Useful for hierarchical data.
Example 1: Simplifying a Subquery
Let’s say we want to find customers who placed orders totaling more than $1000.
Without a CTE:
SELECT customer_id
FROM (
SELECT customer_id, SUM(order_total) AS total
FROM orders
GROUP BY customer_id
) AS sub
WHERE total > 1000;
With a CTE:
WITH customer_totals AS (
SELECT customer_id, SUM(order_total) AS total
FROM orders
GROUP BY customer_id
)
SELECT customer_id
FROM customer_totals
WHERE total > 1000
Why this is better:
In the non-CTE version, we’re forced to write a nested subquery and alias it (sub), which can make the query harder to read—especially as the logic grows more complex. By using a CTE, we give the intermediate result a clear name (customer_totals) and separate it logically from the main query. This improves both readability and maintainability, allowing you to debug or extend your query more easily in the future. CTEs are especially helpful when the intermediate result is reused or if you want to break a multi-step transformation into digestible pieces.
Example 2: Joining Multiple CTEs
You can define more than one CTE and even join them together. Here's how to combine recent orders and high-value customers:
WITH recent_orders AS (
SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
),
high_value_customers AS (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING SUM(order_total) > 1000
)
SELECT ro.*
FROM recent_orders ro
JOIN high_value_customers hvc ON ro.customer_id = hvc.customer_id;
This structure allows you to clearly separate each logical part of your query, making it far easier to troubleshoot and evolve over time.
Example 3: Recursive CTE for Hierarchical Data
One of the most powerful features of CTEs in PostgreSQL is recursion. You can use recursive CTEs to work with hierarchical data such as categories, folders, or organizational charts.
Here’s how to retrieve all subcategories of a top-level category:
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
In this example:
- The first part of the CTE selects the root categories.
- The second part recursively joins child categories.
- The recursion continues until no more child records are found.
CTEs are a core part of writing clean, scalable SQL. They help you think in steps, organize your logic, and make your queries more understandable to others (and your future self). While they’re not always the fastest in performance-critical contexts, their clarity and flexibility are often worth the tradeoff—especially during the development and debugging phase.
Whether you're filtering reports, analyzing trends, or working with tree-like data, learning to use CTEs effectively is a skill every PostgreSQL user should master.
Start with a simple WITH, build up your logic, and make your SQL work for you, not the other way around.