CTE vs Subqueries

What is a CTE?

A Common Table Expression (CTE) is a named temporary result set that you can reference within a single SELECT, INSERT, UPDATE, or DELETE statement. It is defined with the WITH clause:

WITH sales_by_month AS (
    SELECT
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS total
    FROM sales
    GROUP BY 1
)
SELECT *
FROM sales_by_month
WHERE total > 1000;

What is a subquery?

A subquery is a query nested inside another query. It can appear in the SELECT list, FROM clause, WHERE clause, or HAVING clause.

SELECT *
FROM (
    SELECT
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS total
    FROM sales
    GROUP BY 1
) AS sales_by_month
WHERE total > 1000;

Both examples above produce the same result, but the syntax and semantics differ.

---

Key Differences

---

When to Prefer a CTE

1. Complex Queries – When you have multiple layers of logic, naming each layer with a CTE makes the query easier to read and maintain.
2. Recursive Logic – For hierarchical or graph queries, use a recursive CTE (WITH RECURSIVE).
3. Multiple References – If the same derived table is needed more than once, a CTE avoids duplication.
4. Debugging – You can run each CTE independently to verify its output.
5. DML Statements – When inserting or updating based on a complex select, a CTE keeps the statement tidy.

---

When a Subquery Might Be Simpler

1. Single Use – If the derived table is used only once, a subquery keeps the statement short.
2. Legacy Systems – Some older databases or legacy codebases may not support CTEs.
3. Performance Tuning – In some engines, a subquery can be optimized differently than a CTE; testing is required.

---

Practical Tips

• Avoid Unnecessary Materialization – In PostgreSQL, a non‑recursive CTE is inline (no materialization). In SQL Server, a CTE is materialized unless you use OPTION (RECOMPILE) or rewrite as a derived table. Check your DB’s documentation.
• Use Aliases – Give CTEs descriptive names; this improves readability.
• Keep CTEs Small – Large CTEs can clutter the query; consider breaking them into multiple CTEs or temporary tables if the logic is very complex.
• Test Performance – Run EXPLAIN or EXPLAIN ANALYZE to see if the optimizer materializes the CTE or inlines it. Adjust accordingly.

---

Quick Example: Recursive CTE vs Subquery

Recursive CTE (PostgreSQL)

WITH RECURSIVE employee_path AS (
    SELECT id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.manager_id, e.name, ep.level + 1
    FROM employees e
    JOIN employee_path ep ON e.manager_id = ep.id
)
SELECT * FROM employee_path;

Same Result with Subqueries (no recursion)

SELECT *
FROM (
    SELECT id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
) AS base
UNION ALL
SELECT e.id, e.manager_id, e.name, base.level + 1
FROM employees e
JOIN base ON e.manager_id = base.id;

The recursive CTE is cleaner and directly expresses the hierarchical traversal.

---

Bottom Line

• CTE: Named, reusable, can be recursive, often improves readability, but may be materialized depending on the DB.
• Subquery: Inline, used where a CTE isn’t supported or needed, can be duplicated if reused.

Choose the construct that best matches the complexity of your query, the need for recursion, and the performance characteristics of your database engine.