Write CTEs with WITH

You've conquered joins, subqueries, and window functions, but every time you stare at a query more than four lines long, your eyes glaze over and your brain files it under 'spaghetti with extra SQL.' The WITH clause — Common Table Expressions, or CTEs — is the missing ingredient that turns unreadable nested piles of SELECT into a clean, linear story. This lesson shows you how to write CTEs with the WITH clause in PostgreSQL, so your queries become easier to read, debug, and maintain — one named block at a time.

The problem this lesson solves

Complex queries have a nasty habit of turning into a tangled mess of nested subqueries. The database engine doesn't mind, but your future self — and your teammates — will suffer. Reading a query that's 80 lines deep with three levels of EXISTS is like untangling a ball of Christmas lights.

CTEs solve this by letting you name intermediate results and then reference them by name. Instead of cramming everything into one monstrous SELECT, you write a sequence of logical steps. It's the difference between reading a recipe with numbered steps and reading a single paragraph of run-on instructions.

Beyond readability, CTEs also give you a natural explanatory checkpoint. You can run just the part inside the CTE to verify it returns what you expect, then layer on the next step. That's a huge win when debugging or explaining query logic to someone else.

The pain this lesson solves is real: write-clean-up becomes write-once-and-understand — you'll stop fearing complex queries.

Core concept / mental model

Think of the WITH clause as a workspace of temporary views. A view is a saved query you can SELECT from later; a CTE is that same idea, but scoped to a single query. You define one or more named queries at the top, then act as though those names are tables.

The anatomy of a CTE

WITH cte_name AS (
    SELECT ...
)
SELECT ... FROM cte_name;

• WITH signals the start of CTE definitions.
• cte_name is your chosen alias — make it descriptive!
• AS (...) contains the inner query that builds the temporary result.
• The main query follows, referencing the CTE name like any table.

You can even define multiple CTEs in a single WITH, separated by commas. Each one can reference previous ones, creating a pipeline of named steps.

Mental model: CTE is a named subquery you can reuse and reason about in isolation. It's like assigning a variable before using it in a function — but for SQL.

How it works step by step

Writing a CTE is a three-step mental process:

1. Identify the intermediate results — ask: "What smaller, testable chunks does this query need?"
2. Write each chunk as a CTE — give it a meaningful name (e.g., monthly_sales, top_customers).
3. Assemble the final result — reference the CTE names in the main SELECT.

The database engine executes the CTE definition(s) first, materializing the result into a temporary structure (though PostgreSQL optimizes it — more in the compare section), then runs the outer query against it.

Why this works

• Readability: The query reads top-to-bottom, like a narrative.
• Reusability: The same CTE can be referenced multiple times in the outer query (e.g., joining a CTE to itself).
• Isolation: You can SELECT * FROM cte_name in pgAdmin or psql to inspect what it returns — perfect for debugging.

Hands-on walkthrough

Let's make this concrete. We'll use a simple orders and order_items schema — imagine an e-commerce system.

Setup

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    total NUMERIC(10,2) NOT NULL,
    ordered_at TIMESTAMP NOT NULL
);

INSERT INTO customers (name) VALUES ('Alice'), ('Bob'), ('Carol');
INSERT INTO orders (customer_id, total, ordered_at) VALUES
    (1, 100.00, '2024-01-01'),
    (1, 150.00, '2024-02-01'),
    (2, 80.00, '2024-01-15'),
    (3, 200.00, '2024-02-20');

Example 1: Basic CTE

WITH recent_orders AS (
    SELECT id, customer_id, total
    FROM orders
    WHERE ordered_at >= '2024-02-01'
)
SELECT * FROM recent_orders;

Expected output:

 id | customer_id | total
----+-------------+-------
  2 |           1 | 150.00
  4 |           3 | 200.00
(2 rows)

Example 2: Multiple CTEs and joining

WITH jan_orders AS (
    SELECT customer_id, SUM(total) AS total_spent
    FROM orders
    WHERE ordered_at >= '2024-01-01' AND ordered_at < '2024-02-01'
    GROUP BY customer_id
),
feb_orders AS (
    SELECT customer_id, SUM(total) AS total_spent
    FROM orders
    WHERE ordered_at >= '2024-02-01' AND ordered_at < '2024-03-01'
    GROUP BY customer_id
)
SELECT
    c.name,
    COALESCE(j.total_spent, 0) AS jan_spent,
    COALESCE(f.total_spent, 0) AS feb_spent
FROM customers c
LEFT JOIN jan_orders j ON j.customer_id = c.id
LEFT JOIN feb_orders f ON f.customer_id = c.id
ORDER BY c.name;

Expected output:

 name  | jan_spent | feb_spent
-------+-----------+----------
 Alice |    100.00 |    150.00
 Bob   |     80.00 |      0.00
 Carol |      0.00 |    200.00
(3 rows)

Notice how the logic reads like a story: get January totals, get February totals, then compare per customer. No nested subquery castles.

Example 3: Recursive CTE (a peek)

CTEs can even call themselves — a recursive CTE — great for hierarchies like employee org charts.

WITH RECURSIVE employee_tree AS (
    -- anchor: top-level employees (manager_id is null)
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- recursive: children
    SELECT e.id, e.name, e.manager_id, et.depth + 1
    FROM employees e
    JOIN employee_tree et ON e.manager_id = et.id
)
SELECT * FROM employee_tree ORDER BY depth, name;

We'll go deep into recursion in a later lesson, but this shows the power.

Compare options / when to choose what

How do CTEs stack up against plain subqueries or views? Here's a quick comparison:

Pro tip: PostgreSQL's optimizer treats CTEs as inline views by default (unless MATERIALIZED is specified). So you don't pay a big performance penalty — but be aware of it in planning.

When to choose CTE: - The same subquery appears multiple times in your query. - You want to build a step-by-step logic that's easy to debug. - You're working on a complex analytical query (e.g., funnel analysis).

When to avoid: - A simple subquery in the WHERE clause is all you need — don't overengineer. - You need the result accessible across sessions — use a view instead.

Troubleshooting & edge cases

CTEs are beginner-friendly but have a few gotchas:

• Column ambiguity: When you join CTEs to tables, always qualify columns with the CTE name or table alias, or you'll get ambiguous column errors.

-- Wrong
SELECT id FROM recent_orders;
-- If orders also has id, PostgreSQL complains.

-- Right
SELECT ro.id FROM recent_orders ro;

• CTE not defined: You accidentally misspelled the CTE name in the main query. PostgreSQL says relation does not exist. Double-check spelling.

• Recursive CTE never terminates: Forgetting the UNION ALL or an anchor condition causes infinite loops. Always test with a small dataset.

• CTE is used but optimized away: PostgreSQL may inline a non-recursive CTE into the outer query. That's fine functionally, but if you want to force materialization (to avoid recomputing expensive CTEs), use MATERIALIZED:

WITH materialized_cte AS MATERIALIZED (
    SELECT ...
)
SELECT ...

• Performance surprises: If a CTE is referenced multiple times and is expensive, the planner might inline it multiple times. Use MATERIALIZED to force single evaluation.

What you learned & what's next

You've learned the core idea behind writing CTEs with the WITH clause: naming intermediate query results to make complex SQL readable and maintainable. You walked through the anatomy, practiced with a step-by-step example, compared CTEs with subqueries and views, and explored common pitfalls.

Key takeaways from this lesson: - WITH + AS creates a named temporary result set. - Multiple CTEs build a pipeline of logical steps. - Recursive CTEs handle hierarchical data elegantly. - CTEs are scoped to a single query — use views for persistence. - Use MATERIALIZED to control performance in edge cases.

What's next: Now that you can write CTEs, you're ready to combine them with window functions for advanced analytics, or dive into recursive CTEs to model tree structures. Both are natural progressions in the PostgreSQL Tutorial track. Keep your queries clean — your future teammates will thank you.