Posted inDatabase

SQL Refactoring: How to Convert Nested Subqueries to CTEs for Better Performance and Readability

Database tutorial - IT technology blog
Database tutorial - IT technology blog

The Problem with Deeply Nested Subqueries

Six months into a project, a colleague handed me a SQL query that had grown into something I can only describe as a labyrinth. Three levels of nested subqueries, each referencing the one inside it, and a WHERE clause that spanned twelve lines. It ran in 8 seconds on a table with 2 million rows.

That query wasn’t written badly on purpose — it grew organically. A developer needed a filtered dataset, wrote a subquery. Later, someone needed to aggregate that, added another layer. Eventually the query was technically correct but practically unmaintainable.

Having worked with MySQL, PostgreSQL, and SQL Server across different projects, each has its own optimizer quirks — but nested subqueries tend to get messy fast on all of them. The optimizer may or may not handle them well, and more importantly, humans definitely don’t handle them well after a few months away from the code.

What Makes Nested Subqueries Hard to Work With

  • Readability: You have to read inside-out. The innermost query executes first, but it’s buried in the middle of the code.
  • Debugging: To test a specific layer, you have to extract it manually every time.
  • Repetition: If two parts of the query need the same filtered dataset, you write the subquery twice.
  • Optimizer behavior: Some databases (MySQL especially, pre-8.0) materialize correlated subqueries in non-obvious ways, causing repeated full scans.

What CTEs Are and How They Work

A Common Table Expression (CTE) — introduced using the WITH keyword — lets you define a named temporary result set before the main query. Think of it as giving a name to a piece of logic so you can reference it cleanly, rather than nesting it somewhere in the middle of your SQL.

The basic syntax:

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

You can define multiple CTEs in a single WITH clause by separating them with commas:

WITH
  active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
  ),
  recent_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY user_id
  )
SELECT u.name, u.email, COALESCE(o.order_count, 0) AS orders
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id;

PostgreSQL, MySQL 8.0+, and SQL Server all support this syntax. For older MySQL versions, the workaround is usually temp tables or views — another reason to keep your database version current.

Materialized vs Non-Materialized CTEs

Before you start converting everything, know that CTEs are not always equivalent to subqueries in terms of execution plan.

  • PostgreSQL (pre-12): CTEs were always materialized — evaluated once, result stored temporarily. This could help or hurt performance depending on how many times the CTE was referenced and the size of the result set.
  • PostgreSQL 12+: The optimizer can now inline CTEs when they’re referenced only once. You can also force materialization with WITH cte AS MATERIALIZED (...).
  • MySQL 8.0+: CTEs are generally non-materialized (inlined) unless the optimizer decides otherwise.

Keep that in mind when refactoring for performance. A CTE referenced three times in your query may execute three times in some databases if it’s not materialized.

Refactoring Nested Subqueries: Step by Step

The approach is simple in principle: start with the innermost subquery, give it a name, and work outward. Each layer becomes a readable, testable block instead of buried code.

Example 1 — Two-Level Nested Subquery

Here’s a query that finds department names where the average employee salary is above the company-wide average:

-- Before: nested, repetitive
SELECT department_name
FROM departments
WHERE id IN (
    SELECT department_id
    FROM employees
    WHERE salary > (
        SELECT AVG(salary)
        FROM employees
    )
    GROUP BY department_id
    HAVING AVG(salary) > (
        SELECT AVG(salary)
        FROM employees
    )
);

Notice that SELECT AVG(salary) FROM employees appears twice. The database may execute it twice depending on the engine and version.

-- After: CTE version
WITH
  company_avg AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
  ),
  high_earning_depts AS (
    SELECT department_id
    FROM employees
    CROSS JOIN company_avg
    WHERE salary > company_avg.avg_salary
    GROUP BY department_id
    HAVING AVG(salary) > (SELECT avg_salary FROM company_avg)
  )
SELECT d.department_name
FROM departments d
INNER JOIN high_earning_depts hed ON d.id = hed.department_id;

The company_avg CTE is defined once. The query reads top-to-bottom: define the company average, find departments that meet the threshold, look up department names.

Example 2 — Three Levels of Nesting

Three levels deep? Named CTEs handle it without breaking a sweat. Suppose you need customers who placed orders containing a product from supplier #42, and those customers must have accounts older than one year:

-- Before: three levels deep
SELECT customer_name
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE id IN (
        SELECT order_id
        FROM order_items
        WHERE product_id IN (
            SELECT id
            FROM products
            WHERE supplier_id = 42
        )
    )
)
AND account_created < NOW() - INTERVAL '1 year';

-- After: named layers
WITH
  supplier_products AS (
    SELECT id AS product_id
    FROM products
    WHERE supplier_id = 42
  ),
  qualifying_orders AS (
    SELECT DISTINCT oi.order_id
    FROM order_items oi
    INNER JOIN supplier_products sp ON oi.product_id = sp.product_id
  ),
  qualifying_customers AS (
    SELECT DISTINCT o.customer_id
    FROM orders o
    INNER JOIN qualifying_orders qo ON o.id = qo.order_id
  )
SELECT c.customer_name
FROM customers c
INNER JOIN qualifying_customers qc ON c.id = qc.customer_id
WHERE c.account_created < NOW() - INTERVAL '1 year';

You can now run each CTE independently to verify its output. Debugging stops being archaeology — you isolate exactly which layer produces wrong results, fix it, and move on.

Verification and Performance Monitoring

Always Confirm Results Match First

Before assuming your refactored query is equivalent, verify with a quick comparison. Run both queries on the same dataset and check that row counts match:

-- Compare row counts
SELECT COUNT(*) FROM ( /* original nested version */ ) AS old_q;
SELECT COUNT(*) FROM ( /* CTE version */ ) AS new_q;

-- PostgreSQL: compare a checksum on the result set
SELECT md5(string_agg(customer_name::text, '' ORDER BY customer_name))
FROM ( /* query */ ) q;

Use EXPLAIN ANALYZE to Compare Execution Plans

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
WITH supplier_products AS (
  SELECT id FROM products WHERE supplier_id = 42
)
SELECT ...;

-- MySQL 8.0+
EXPLAIN FORMAT=JSON
WITH supplier_products AS (
  SELECT id FROM products WHERE supplier_id = 42
)
SELECT ...;

Look specifically at:

  • Seq Scan vs Index Scan: CTEs don’t always push predicates through — sometimes a CTE forces a full scan of an intermediate result set.
  • Actual rows vs estimated rows: Large discrepancies indicate stale statistics. Run ANALYZE table_name in PostgreSQL or ANALYZE TABLE table_name in MySQL to refresh them.
  • CTE Scan nodes (PostgreSQL): If you see a “CTE Scan” on a large dataset that’s only referenced once, add NOT MATERIALIZED (PostgreSQL 12+) to let the optimizer inline it.
-- Force inlining in PostgreSQL 12+
WITH supplier_products AS NOT MATERIALIZED (
  SELECT id FROM products WHERE supplier_id = 42
)
SELECT ...;

When CTEs Don’t Help Performance

CTEs improve readability almost always. Performance is a different question. Two scenarios where a subquery may still outperform a CTE:

  1. Single-value correlated subqueries in SELECT: If you’re pulling one aggregate per row and there’s a covering index, the optimizer can sometimes short-circuit early. A CTE join may do more work than necessary.
  2. Very small subquery result sets: If a subquery returns a handful of IDs from a config table, the optimizer can often push the filter deep into the execution plan without materializing anything. Wrapping it in a CTE adds a layer that may prevent that optimization.

My rule of thumb: refactor for readability first, then measure with EXPLAIN. If the CTE version is slower, add indexes before reverting — nine times out of ten, the bottleneck is a missing index, not the CTE structure itself.

A Note on Recursive CTEs

CTEs also support recursion using WITH RECURSIVE, which unlocks hierarchical queries — org charts, tree structures, graph traversal — that would otherwise require application-level loops or multiple round trips to the database. That’s a separate topic. Master non-recursive CTEs first; recursion builds on the same foundation.

Share:
Tags: