DE CTEs and Subqueries

Complex SQL queries often need intermediate results — a filtered subset of data, a ranked list, an aggregated summary — before the final computation can happen. Subqueries and Common Table Expressions (CTEs) both provide ways to define these intermediate steps inside a single query. Knowing how and when to use each one makes SQL easier to write, read, and debug.

What Is a Subquery

A subquery is a query written inside another query. The inner query runs first and its result becomes the input for the outer query. Subqueries can appear in the SELECT list, the FROM clause, or the WHERE clause.

The Recipe Analogy

Cooking a complex dish involves preparing components separately before combining them. You make the sauce first, then add it to the main dish. A subquery is like preparing a sauce in a separate pot — it completes its own cooking process and the result feeds into the bigger dish. A CTE is like writing that sauce recipe on a labeled notecard and placing it next to the stove — clearly named and easy to reference.

Subquery in WHERE Clause

A subquery inside WHERE filters rows based on the result of the inner query. This is useful for comparing individual rows to an aggregate value.

-- Find orders with amount above the average order value
SELECT order_id, customer_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

The inner query runs first: SELECT AVG(amount) FROM orders --> returns 2100
Then the outer query runs: WHERE amount > 2100

Subquery in FROM Clause (Derived Table)

A subquery in the FROM clause acts as a temporary table — sometimes called a derived table or inline view. The outer query treats it like a regular table.

-- Find customers who rank in the top 3 by total spending
SELECT customer_id, total_spent
FROM (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS customer_spending
WHERE total_spent > 5000
ORDER BY total_spent DESC;

The inner query aggregates spending per customer.
The outer query filters and sorts those aggregated results.

Subquery in SELECT List

A scalar subquery in the SELECT list returns one value per row. It runs once for each row in the outer query — which can be slow on large tables. Use with care.

-- Show each order alongside the total number of orders for that customer
SELECT
    order_id,
    amount,
    (SELECT COUNT(*) FROM orders o2
     WHERE o2.customer_id = o.customer_id) AS customer_order_count
FROM orders o;

What Is a CTE

A Common Table Expression (CTE) is a named temporary result set defined at the top of a query using the WITH keyword. The CTE name acts like a table that you can reference one or more times in the main query. CTEs make complex queries far more readable than nested subqueries.

Basic CTE Syntax

WITH cte_name AS (
    -- This query defines the CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- Main query references the CTE by name
SELECT *
FROM cte_name
WHERE ...;

CTE vs Subquery: Same Problem, Different Readability

-- SUBQUERY version (harder to read):
SELECT customer_id, total_spent
FROM (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS customer_spending
WHERE total_spent > 5000;

-- CTE version (much cleaner):
WITH customer_spending AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_spent
FROM customer_spending
WHERE total_spent > 5000;

Multiple CTEs

A single query can define multiple CTEs, each building on the previous one. This creates a readable step-by-step transformation — much like the stages of an assembly line.

-- Step 1: Total spending per customer
-- Step 2: Rank customers by spending
-- Step 3: Return top 5 customers

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
ranked_customers AS (
    SELECT
        customer_id,
        total_spent,
        RANK() OVER (ORDER BY total_spent DESC) AS spending_rank
    FROM customer_totals
)
SELECT
    c.name,
    rc.total_spent,
    rc.spending_rank
FROM ranked_customers rc
JOIN customers c ON rc.customer_id = c.customer_id
WHERE rc.spending_rank <= 5;

Recursive CTEs

CTEs can reference themselves — a feature called recursion. Recursive CTEs navigate hierarchical data: organizational charts, category trees, and bill-of-materials structures.

-- Traverse an employee org chart from the CEO down
WITH RECURSIVE org_tree AS (
    -- Base case: start at the top (CEO has no manager)
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: find each employee's direct reports
    SELECT e.employee_id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT level, name, employee_id
FROM org_tree
ORDER BY level, name;

When to Use Each

Use Subqueries When:              Use CTEs When:
- Simple, one-off filter          - Logic is complex or multi-step
- Used in one place only          - Same intermediate result used twice
- Performance optimization        - Readability matters (team reviews)
  (some engines optimize subqs    - Need recursive traversal
   differently)                   - Debugging step-by-step logic

CTEs in dbt Transformations

dbt (data build tool), the dominant ELT transformation framework, builds SQL models as sequences of CTEs. Each CTE represents one logical transformation step. Engineers write individual CTE blocks that reference each other, and dbt compiles them into a final table inside the data warehouse. This style makes complex transformations modular, testable, and easy for the whole team to understand.

Summary

Subqueries nest a query inside another query and work well for simple, one-use intermediate results. CTEs define named intermediate results at the top of the query, improving readability and enabling multi-step logic. Multiple CTEs chain together like pipeline stages. Recursive CTEs handle hierarchical data. Data engineers use both tools regularly, favoring CTEs for complex transformations where clarity and maintainability matter most.

Previous lesson

Back to course

Next lesson