Common Table Expressions (CTEs): Simplify Complex Queries in PostgreSQL

Previously in this course, we explored using DISTINCT for clean data to handle duplicates in our result sets. In this lesson, we move from data cleanup to code organization by mastering the Common Table Expression (CTE).

What is a Common Table Expression?

A Common Table Expression, or CTE, is a temporary, named result set that you define at the start of a query using the WITH clause. Think of it as a "virtual table" that exists only for the duration of a single query execution.

Before CTEs, developers often relied on deeply nested subqueries (which we touched on in subqueries in where clauses) to handle multi-step data transformations. While functional, these nested queries become difficult to read as they grow. CTEs solve this by letting you define your logic linearly, reading from top to bottom.

Syntax and First Principles

A CTE starts with the WITH keyword, followed by the name of the expression and the AS keyword. Inside the parentheses, you write a standard SELECT query.

SQL
WITH regional_sales AS (
    SELECT region, SUM(amount) as total
    FROM orders
    GROUP BY region
)
SELECT * FROM regional_sales WHERE total > 1000;

In this example, regional_sales acts exactly like a table. You can join it to other tables or use it as a source for further filtering.

Simplifying Complex Joins with Multiple CTEs

One of the greatest benefits of the WITH clause is the ability to chain multiple expressions together. This is invaluable when you need to perform data preparation before joining tables.

Suppose we want to find our "Top Customers"—defined as those who spent more than the average customer total. Instead of writing one massive, unreadable join, we can break it down:

SQL
WITH customer_totals AS (
    -- Step 1: Calculate total spent per customer
    SELECT customer_id, SUM(total_price) as spent
    FROM orders
    GROUP BY customer_id
),
average_spend AS (
    -- Step 2: Calculate the average of those totals
    SELECT AVG(spent) as avg_val
    FROM customer_totals
)
-- Step 3: Join and filter
SELECT c.name, ct.spent
FROM customer_totals ct
JOIN customers c ON ct.customer_id = c.id
CROSS JOIN average_spend avg
WHERE ct.spent > avg.avg_val;

By separating the aggregation logic from the final selection, the query becomes self-documenting. If you need to debug your results, you can simply change the final SELECT to target one of the CTEs instead.

Hands-on Exercise

Using our store application, write a query that identifies products that have never been ordered.

1. Create a CTE named ordered_products that selects all product_ids from your order_items table.
2. Use a main SELECT query to pull all records from the products table where the id is NOT IN your ordered_products CTE.
3. This approach keeps your logic clean and separates the "order history" check from the "product inventory" view.

Common Pitfalls

• Scope Issues: CTEs are only available to the query immediately following them. You cannot define a CTE and then try to use it in a separate INSERT or UPDATE statement.
• Performance Assumptions: While CTEs improve readability, they are not always a performance "magic bullet." In older versions of PostgreSQL, CTEs were always "materialized" (calculated and stored in memory). Modern PostgreSQL is smarter, but if you notice a query is slow, use the techniques from analyzing query plans to ensure the database engine isn't doing unnecessary work.
• Redundant Definitions: Don't create a CTE for a simple join. If a query is just two tables and a WHERE clause, a standard JOIN is still more efficient and readable than wrapping it in a WITH clause.

FAQ

Can I use a CTE inside another CTE? Yes. You can reference previously defined CTEs within the definition of a subsequent CTE in the same WITH block.

Are CTEs temporary tables? Not exactly. Temporary tables are stored in the database for the session duration; CTEs vanish the moment the query finishes.

Do CTEs support data modification? Yes, PostgreSQL allows INSERT, UPDATE, and DELETE inside a WITH clause, which is a powerful way to perform complex data migrations in a single transaction.

Recap

Common Table Expressions (CTEs) transform complex, nested SQL into maintainable, modular code. By using the WITH clause, you define logical building blocks that make your queries easier to debug and read. As you continue to build out your store application, look for opportunities to replace deeply nested subqueries with these cleaner, more expressive structures.

Up next: We will dive into Advanced Window Functions to perform calculations across sets of rows without collapsing them into a single group.