Common Table Expressions (CTEs) are one of the most useful, least understood features in SQL. Beginners often think they’re “just subqueries with a nicer syntax,” but CTEs are far more powerful than that. In this article, you’ll get the clearest explanation of what a CTE truly is — and how to use them like a pro!

What Exactly Is a CTE?

A CTE is a temporary, named result set you can reference within a single SQL query. Think of it like a “query variable.” You define it once at the top of your statement, then use it as if it were a table.

WITH recent_payments AS (
  SELECT customer_id, amount
  FROM payment
  WHERE payment_date BETWEEN '2022-01-29' AND '2022-01-31'
  )
SELECT customer_id, SUM(amount)
FROM recent_payments
GROUP BY customer_id;

The WITH clause creates the CTE (recent_payments), and the main query simply selects from it.

Why Use a CTE Instead of a Subquery?

1. Readability

CTEs make complex logic dramatically easier to understand. Instead of nesting multiple subqueries, you write your logic in clear, named blocks.

2. Reusability

You can reference the same CTE multiple times in your query — something subqueries can’t do cleanly.

3. Logical Decomposition

They allow you to structure SQL like building blocks. Handle one step at a time, similar to functions in code.

A Real Example Using Pagila

Let’s say you want to find the top customers by rental activity in the Pagila sample database.

WITH customer_rentals AS (
  SELECT customer_id, COUNT(*) AS rentals
  FROM rental
  GROUP BY customer_id
  ),
  top_customers AS (
    SELECT customer_id, rentals
    FROM customer_rentals
    ORDER BY rentals DESC
    LIMIT 10
    )
SELECT c.first_name, c.last_name, t.rentals
FROM top_customers t
JOIN customer c ON c.customer_id = t.customer_id
ORDER BY t.rentals DESC;

Each step builds on the previous one. This is where CTEs shine — clean, readable, modular SQL.

Recursive CTEs (The “Superpower” Feature)

Recursive CTEs let SQL perform loops — useful for hierarchies, date series, and graph traversal.

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 10
  )
SELECT * FROM counter;

That query prints numbers 1 through 10. This is only possible using recursive CTEs. However, you need to be careful when writing them — forgetting a proper terminating condition can lead to infinite recursion, runaway queries, or heavy performance issues. Always verify that each recursive step moves closer to completion.

CTE vs Subquery vs Temp Table

• CTE: Best for readability, breaking logic into steps, and referencing the same result multiple times within a single query. CTEs are query-scoped — they must be used immediately in the statement that defines them and do not persist beyond that one SQL command. They also cannot be indexed, and historically acted as optimization fences before PostgreSQL 12.
• Subquery: Best for simple inline filtering, existence checks, and lightweight one-off logic. Subqueries are flexible and usually optimized aggressively, but they can become difficult to read when deeply nested and cannot be reused elsewhere in the same query.
• Temp Table: Best when working with large datasets that need to be reused across multiple statements in a session or transaction. Temp tables persist for the duration of the session, can be indexed, and often improve performance for multi-step workflows. However, they add overhead, require DDL privileges, and are not visible outside the session that created them.

Performance Considerations

In older versions of PostgreSQL, CTEs always acted as optimization fences — meaning the query planner treated them as fixed, separate steps and never tried to optimize them together with the rest of the query. Since PostgreSQL 12, the planner can inline CTEs when it makes sense.

Inlining simply means PostgreSQL takes the CTE’s query and “injects” it directly into the main query, as if the CTE were never defined separately. This allows the optimizer to push down filters, reorder joins, and apply other improvements that can significantly boost performance.

By default, PostgreSQL decides automatically whether to inline a CTE. But you can control this behavior using MATERIALIZED or NOT MATERIALIZED.

• MATERIALIZED: Forces PostgreSQL to fully compute the CTE and store its results before the main query runs. This prevents inlining. It is useful when the CTE’s results are reused multiple times or are expensive to compute.
• NOT MATERIALIZED: Instructs PostgreSQL to inline the CTE even if it normally wouldn’t. This keeps the CTE as a logical part of the main query, letting the optimizer push down filters or joins for better performance.

WITH NOT MATERIALIZED cte AS (
  SELECT ...
  )
SELECT * FROM cte;

Conclusion

CTEs are one of the most powerful tools in SQL. They make your queries cleaner, easier to maintain, and more modular — especially when working with complex datasets like Pagila. Whether you're analyzing customer behavior, simplifying multi-step logic, or generating sequences with recursion, mastering CTEs will instantly level up your SQL skills!