CTE vs Subqueries: When Should You Use Each?

We already know that CTEs make code easier to read. But should you always use them? Sometimes a simple subquery does the job better and faster. Let’s break down when each tool shines and learn how to make the right call.

Subqueries: Quick and Simple

You probably remember that a subquery is just SQL inside SQL. It’s embedded right in the main query and runs “in place.” Perfect for simple, one-off operations:

-- Find products more expensive than the average price
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Here, the subquery calculates the average price once and that’s it. No extra constructs needed.

Performance: Who’s Faster?

Subqueries often win on speed for simple stuff. PostgreSQL can optimize them “on the fly,” especially when the subquery only runs once:

-- Fast: subquery runs just once
SELECT customer_id, order_total
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

CTEs are materialized by default — PostgreSQL first calculates the CTE result, saves it as a temp table, and then uses it. This can slow down simple queries:

-- Slower: CTE gets materialized into a temp table
WITH latest_date AS (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

But! Starting with PostgreSQL 12, you can control materialization:

-- Force NOT to materialize
WITH latest_date AS NOT MATERIALIZED (
    SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;

Reusing Results: This Is Where CTEs Rock

When you need the same intermediate result more than once, CTEs are a lifesaver:

-- With a subquery: repeat the same logic twice
SELECT
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') AS completed_orders,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed') * 100.0 / COUNT(*) AS completion_rate
FROM orders;

-- With a CTE: calculate once, use twice
WITH completed_orders AS (
    SELECT COUNT(*) AS count FROM orders WHERE status = 'completed'
)
SELECT
    co.count AS completed_orders,
    co.count * 100.0 / (SELECT COUNT(*) FROM orders) AS completion_rate
FROM completed_orders co;

Complex Analytics: CTEs Win on Points

For multi-step analytics, CTEs turn chaos into order. Check out this sales report:

With subqueries (total brain scramble):

SELECT 
    category,
    revenue,
    revenue * 100.0 / (
        SELECT SUM(p.price * oi.quantity)
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        JOIN orders o ON oi.order_id = o.order_id
        WHERE EXTRACT(year FROM o.order_date) = 2024
    ) AS revenue_share
FROM (
    SELECT 
        p.category,
        SUM(p.price * oi.quantity) AS revenue
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
    GROUP BY p.category
) category_revenue;

With CTEs (everything in its place):

WITH yearly_sales AS (
    SELECT 
        p.category,
        p.price * oi.quantity AS sale_amount
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE EXTRACT(year FROM o.order_date) = 2024
),
category_revenue AS (
    SELECT 
        category,
        SUM(sale_amount) AS revenue
    FROM yearly_sales
    GROUP BY category
),
total_revenue AS (
    SELECT SUM(sale_amount) AS total FROM yearly_sales
)
SELECT 
    cr.category,
    cr.revenue,
    cr.revenue * 100.0 / tr.total AS revenue_share
FROM category_revenue cr, total_revenue tr;

Recursion: CTEs Have a Monopoly Here

For hierarchical structures, subqueries just can’t do it.

Only recursive CTEs can handle stuff like “find all subordinates of a manager”:

WITH RECURSIVE employee_hierarchy AS (
    -- Start with the CEO
    SELECT employee_id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Add subordinates at each level
    SELECT e.employee_id, e.manager_id, e.name, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;

Debugging and Maintaining Code

CTEs are easy to debug step by step:

-- Check the first step
WITH active_customers AS (
    SELECT customer_id FROM customers WHERE status = 'active'
)
SELECT COUNT(*) FROM active_customers; -- Make sure the logic is right

-- Add the second step
WITH active_customers AS (...),
recent_orders AS (
    SELECT customer_id, COUNT(*) as order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
)
SELECT COUNT(*) FROM recent_orders; -- Check this step too

Subqueries are harder to debug — you have to pull them out of context.

Practical Tips

Use subqueries when:

• The logic is simple and fits in one line
• You need max performance for simple operations
• The intermediate result is only used once
• You’re working with small amounts of data

Use CTEs when:

• The query is complex and can be split into logical steps
• You need to reuse intermediate results multiple times
• Readability and maintainability matter
• You’re working with hierarchies (recursive CTEs)
• You’re debugging complex logic step by step

The Golden Rule

Start with a subquery. If it gets hard to read or you’re repeating logic — switch to a CTE. Your future teammate (or you, six months from now) will thank you!

2

Task

SQL SELF, level 28, lesson 1

Locked

Using CTE to Filter Data

Using CTE to Filter Data

Open