SQL Subqueries and CTEs: Complex Queries Made Readable with WITH • Meteora Web Agency

Have you ever opened an SQL query written by someone else — or by you six months ago — and spent twenty minutes figuring out what it does? Nested subqueries inside other subqueries, three levels of parentheses, scattered JOINs. It works, but it's unreadable. And when a query is unreadable, it becomes impossible to maintain, optimize, or explain to a teammate.

We at Meteora Web work daily with real client databases: e-commerce stores with hundreds of thousands of orders, SaaS platforms with dozens of interconnected tables. SQL code readability isn't an aesthetic preference. It's about wasted development hours and bugs that could have been avoided.

Here we'll show you how to use Common Table Expressions (CTEs) with the WITH clause to turn intricate queries into clear logical blocks. You'll also learn when a traditional subquery is the right choice and when a CTE makes the difference.

Subqueries: the basic tool, but with limits

A subquery is a query nested inside another query. It's used in WHERE, FROM, SELECT, or HAVING. It's powerful, but has two major drawbacks: readability and reusability.

Subquery in WHERE

A classic case: find customers who made at least one order over 500 euros.

SELECT name, email
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE total > 500
);

It works, but if the subquery becomes complex (multiple JOINs, aggregations, filters), the code flattens into a hard-to-read matryoshka.

Subquery in FROM (derived table)

When you need to pre-aggregate data before a JOIN, you use a subquery in FROM:

SELECT c.name, rs.total_orders
FROM customers c
JOIN (
    SELECT customer_id, COUNT(*) AS total_orders
    FROM orders
    WHERE date > '2026-01-01'
    GROUP BY customer_id
) rs ON c.id = rs.customer_id;

Still readable, but if you need the same aggregation in multiple places, you have to rewrite it. That's where CTEs shine.

CTEs with WITH: readability and reuse

A Common Table Expression is a temporary query defined at the beginning of a larger query. You write it with WITH cte_name AS (query) and then use it like a virtual table.

First example: from subquery to CTE

Let's rewrite the previous example with a CTE:

WITH order_summary AS (
    SELECT customer_id, COUNT(*) AS total_orders
    FROM orders
    WHERE date > '2026-01-01'
    GROUP BY customer_id
)
SELECT c.name, s.total_orders
FROM customers c
JOIN order_summary s ON c.id = s.customer_id;

The advantage is clear: the aggregation logic is isolated, has a descriptive name, and can be reused later.

Multiple CTEs in cascade

CTEs can be chained. Each subsequent CTE can reference previous ones. This allows you to break a complex query into linear steps.

WITH
active_customers AS (
    SELECT id, name
    FROM customers
    WHERE last_purchase > '2026-06-01'
),
customer_orders AS (
    SELECT c.id, COUNT(o.id) AS num_orders, SUM(o.total) AS revenue
    FROM active_customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id
)
SELECT c.name, co.num_orders, co.revenue
FROM active_customers c
JOIN customer_orders co ON c.id = co.id
WHERE co.revenue > 1000
ORDER BY co.revenue DESC;

Each block has a single responsibility. If there's an error, you isolate it in one step. If you need to change the definition of "active customer", you change one line.

Recursive CTEs: when you have hierarchical data

A recursive CTE calls itself. It's used for tree-like data: categories, org charts, nested comments.

Structure of a recursive CTE

It must have an anchor (SELECT that picks the base level) and a recursive step (that joins back to the CTE).

WITH RECURSIVE hierarchy AS (
    -- anchor: roots
    SELECT id, name, parent_id, 1 AS level
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- recursive step: children
    SELECT c.id, c.name, c.parent_id, h.level + 1
    FROM categories c
    JOIN hierarchy h ON c.parent_id = h.id
)
SELECT * FROM hierarchy ORDER BY level, name;

Result: every row knows its depth level. Powerful and clean.

Performance: CTEs aren't magic, but they help

CTEs are not inherently faster than subqueries. The database materializes them as temporary derived tables. In some DBMS (e.g., PostgreSQL) CTEs may be materialized or not. But the real gain is human: writing maintainable queries means finding bottlenecks faster.

We've seen teams spend days deciphering nested subqueries only to discover a missing index. With CTEs, the query structure is clear – you can focus on the execution plan.

When to prefer subqueries over CTEs

Scalar subquery (returns a single value): fine in SELECT or WHERE, simple, doesn't need a CTE.
One-off very short queries: no need for a CTE for two lines.
When the subquery is used only once and not part of a complex intermediate calculation.

Common mistakes and how to avoid them

Forgetting the semicolon before a CTE if there are previous statements. CTEs must be the first element of the query.
Using UNION instead of UNION ALL in recursive CTEs: UNION removes duplicates, slows down, and can cause infinite loops in recursion. Always use UNION ALL.
Omitting a depth limit in recursive CTEs: if data has cycles, recursion never ends. Set an explicit limit with WHERE level < 10 or use MAXRECURSION in SQL Server.
Not testing with real data: a CTE that works on 100 rows may explode with 100,000. Check the execution plan.

In summary — what to do now

Review your most complex query and try to break it into CTEs with clear names.
If you have hierarchical data (categories, comment threads), use a recursive CTE — you'll save hours of procedural code.
Check the execution plan of your new CTE: ensure indexes are used.
Share your query with a colleague: if they understand it in a minute, you've won.
If you work with PHP backend, check how we apply the same readability principle in PHP with readonly properties — read the guide.

CTEs aren't a trend. They're a tool that separates those who write throwaway SQL from those who build queries that last. We use them every day, and you should too.