Common Table Expressions (CTEs)

In the lesson on Derived Tables, we learned how to nest a query inside the FROM clause to create a temporary table. While powerful, heavily nested subqueries are notoriously difficult to read. If you have a subquery inside a subquery inside a subquery, it becomes a messy “pyramid of doom.”

Enter the Common Table Expression (CTE).

A CTE allows you to define your temporary tables at the very top of your SQL file, give them clear names, and then reference them in your main query as if they were real tables. This makes your SQL modular and infinitely more readable.

The WITH Clause

You define a CTE using the WITH keyword, followed by the name you want to give your temporary table, and the query that defines it wrapped in parentheses.

Let’s rewrite the Derived Table average-order-value query from the previous lesson using a CTE:

WITH order_summaries AS (
  SELECT order_id, SUM(quantity * unit_price) AS order_total
  FROM order_items
  GROUP BY order_id
)

SELECT AVG(order_total)
FROM order_summaries;

Look at how much cleaner that is!

1. We define order_summaries up top. It’s clear, isolated, and easy to test on its own.
2. In the main SELECT statement at the bottom, we query order_summaries exactly as if it were a physical table in our database.

Multiple CTEs

The true power of CTEs unlocks when you define multiple of them in a single query. You separate them with a comma.

WITH 
  sales_data AS (SELECT ...),
  hr_data AS (SELECT ...),
  combined_data AS (
    SELECT * FROM sales_data JOIN hr_data ...
  )
SELECT * FROM combined_data WHERE ...;

Notice that a CTE can even reference a CTE that was defined above it! This allows you to build complex data transformations step-by-step, linearly, rather than nesting them.

[!TIP] Modern SQL developers heavily prefer CTEs over nested Subqueries purely for readability and maintainability. When in doubt, extract your complex logic into a CTE!

Try refactoring a nested subquery into a clean, readable CTE in the interactive lab!