SQL Subqueries vs CTEs: When to Use Which

Introduction

The moment your SQL queries stop being one-liners and start solving real business problems, you will run into a decision every analyst faces: subquery or CTE? Both let you break a complex problem into steps, use the output of one query as the input to another, and write logic that would be impossible to express in a single flat SELECT.

For decades, subqueries were the only way. Modern SQL introduced Common Table Expressions (CTEs), sometimes called WITH clauses, and they have largely become the preferred style in analytics. But subqueries are not obsolete — they still shine in specific cases. Understanding when to reach for each is what separates a junior analyst from someone who writes queries their teammates enjoy reading. If you are working through a data analytics course in Bangalore, this is a topic worth spending real time on. For more broader background, check out our complete SQL for data analytics guide.

What Is a Subquery

A subquery is a SELECT statement nested inside another statement. It can appear in the SELECT list, the FROM clause, or the WHERE clause. The database runs the inner query first (conceptually) and uses its result in the outer query.

Here is a subquery in the WHERE clause — find customers whose total orders exceed the average:

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > (SELECT AVG(total) FROM (
SELECT SUM(amount) AS total FROM orders GROUP BY customer_id
) AS averages)
);

Even this modestly complex example is already getting hard to read. The eye has to jump from the outer query into two levels of nested SELECTs and back out. This is the biggest downside of subqueries — they read from the inside out, which is not how humans naturally scan code.

What Is a CTE

A Common Table Expression is a named temporary result set defined at the top of your query with the WITH keyword. You then use its name in the main query as if it were a real table.

The same "customers above average" logic as a CTE:

WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
),
avg_total AS (
SELECT AVG(total) AS avg_amt FROM customer_totals
)
SELECT c.customer_id, c.name, ct.total
FROM customers c
INNER JOIN customer_totals ct ON c.customer_id = ct.customer_id
CROSS JOIN avg_total a
WHERE ct.total > a.avg_amt;

The CTE version is longer, but it reads top to bottom in the order the human brain processes it: first compute customer totals, then compute the average, then find customers above it. Each step has a name. If a colleague opens this query six months from now, they can follow the logic without a whiteboard.

This top-down readability is why CTEs have become the default style in modern analytics teams and why any current data analytics course in Bangalore will teach CTEs alongside or ahead of subqueries.

When Subqueries Are the Better Choice

Subqueries still have their place. They are best in three situations.

Simple one-shot filters. If you need a small scalar value in a WHERE clause and it is used only once, a subquery is more compact:

SELECT * FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);

Wrapping this in a CTE would be over-engineering.

Correlated subqueries. A correlated subquery references the outer query's row. These are the SQL equivalent of "for each row, run this mini-query":

SELECT o.order_id, o.amount,
(SELECT AVG(amount) FROM orders WHERE customer_id = o.customer_id) AS customer_avg
FROM orders o;

This shows each order alongside the customer's own average. Modern SQL can often rewrite these using window functions (which are usually faster; see our SQL window functions guide), but correlated subqueries remain a valid and readable choice for simple cases.

EXISTS and NOT EXISTS patterns. For checking whether a related row exists, EXISTS with a subquery is idiomatic and often faster than a JOIN (for more on joining strategies, read SQL joins explained) or IN clause:

SELECT customer_id, name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

When CTEs Are the Better Choice

Reach for a CTE whenever any of these are true.

The query has more than two logical steps. If you can describe the query in English as "first do A, then do B, then combine them," each step deserves its own CTE.

The same subquery is used more than once. Instead of copy-pasting a subquery, define it once as a CTE and reference the name multiple times. This eliminates a whole class of bugs where the two copies drift apart during edits.

You need recursion. Recursive CTEs, using WITH RECURSIVE, are the only clean way to walk hierarchies — organizational charts, category trees, bill-of-materials structures. Subqueries cannot do this.

Readability matters more than compactness. For any query that will be maintained, reviewed in a pull request, or handed to a teammate, CTEs are worth the extra lines. Six months later, you will thank yourself.

Nearly every senior analyst you meet in a Bangalore product company writes CTE-heavy SQL. It is the current professional standard, and a data analytics course in Bangalore that still teaches nested subqueries as the primary style is behind the times.

Recursive CTEs: The Only Way to Walk a Tree

Some data is naturally hierarchical — employees reporting to managers reporting to directors, product categories with subcategories, comment threads with replies. Recursive CTEs are how you traverse these structures in SQL.

WITH RECURSIVE org_tree AS (
SELECT employee_id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, e.manager_id, o.level + 1
FROM employees e
INNER JOIN org_tree o ON e.manager_id = o.employee_id
)
SELECT * FROM org_tree ORDER BY level, name;

The anchor member (the first SELECT) picks the root — employees with no manager. The recursive member joins the CTE back to the employees table to find each next level down. The database keeps iterating until no more rows are added.

Recursive CTEs are advanced territory, and you will not use them every week. But when you need them, nothing else works. Any thorough data analytics course in Bangalore will introduce them at the end of the SQL module.

Performance: The Truth About CTEs vs Subqueries

A common myth is that CTEs are always slower than subqueries because the database materializes them. This was true in some old PostgreSQL versions but is not the general reality anymore. Modern query optimizers in PostgreSQL 12+, SQL Server, BigQuery, Snowflake, and Databricks treat CTEs and subqueries as roughly equivalent for planning purposes, and the difference in most cases is negligible.

The exceptions are:
- Very old PostgreSQL (before version 12): CTEs are an optimization fence, meaning the planner cannot push filters down into them. Rewriting as subqueries can help.
- Extremely large data warehouses where you reference the same CTE many times: some engines re-execute rather than cache, so materializing to a temp table can help.

For 95% of analyst work, choose based on readability, not micro-performance. If your query is slow, look at indexes, joins, and WHERE-clause selectivity long before you blame CTEs.

Realistic Example: Cohort Analysis with CTEs

Here is a real analyst query — a monthly cohort retention table — that showcases why CTEs win for complex logic.

WITH first_orders AS (
SELECT customer_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
FROM orders
GROUP BY customer_id
),
monthly_activity AS (
SELECT customer_id, DATE_TRUNC('month', order_date) AS active_month
FROM orders
GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
cohort_activity AS (
SELECT f.cohort_month,
m.active_month,
COUNT(DISTINCT m.customer_id) AS active_customers
FROM first_orders f
INNER JOIN monthly_activity m ON f.customer_id = m.customer_id
GROUP BY f.cohort_month, m.active_month
)
SELECT cohort_month, active_month, active_customers
FROM cohort_activity
ORDER BY cohort_month, active_month;

Three named steps, each doing one clear thing. The same logic as one nested subquery would be a 30-line unreadable pyramid. This is the kind of query real analysts write, and CTEs are what make it maintainable.

Best Practices for Writing Readable Queries

Whichever style you use, a few habits keep your SQL professional.

- Name your CTEs after what they contain, not what they do. "customer_totals" is better than "step_1".
- Format joins and WHERE clauses on separate lines. Compact code is not clever code.
- Comment the "why," not the "what." SQL syntax is self-explanatory; business rules are not.
- If a CTE grows past 20 lines, break it into smaller CTEs. Two 10-line CTEs are always easier to debug than one 20-line block.
- Test each CTE independently by running it alone during development.

These are the standards professional analysts follow at every product company in Bangalore, and building the habit early is one of the biggest under-the-radar benefits of enrolling in a structured data analytics course in Bangalore rather than piecing together tutorials.

Final Thoughts

Subqueries and CTEs are two ways to express the same idea: build results in steps. Subqueries win for compact one-off filters and for correlated per-row lookups. CTEs win almost everywhere else — for multi-step logic, for reuse, for recursion, and above all for the humans who will read your query later. Default to CTEs when in doubt; reach for a subquery when it is genuinely simpler. Master both, know the tradeoffs, and you will write SQL that scales with the complexity of the questions being asked — which is exactly what a data analytics career demands.