If you’ve written SQL for long enough, you’ve probably asked yourself:

“Should I use a CTE here? Or just nest a subquery?”

Both get the job done. Both are valid. But how you use them can dramatically affect readability, performance, and maintenance — especially as queries get more complex.

Let’s break down when to use a CTE, when a subquery is better, and how to choose confidently.

‍

🔍 What’s the Difference?

• A subquery is a query inside another query — used directly in the SELECT, FROM, or WHERE clause.
• A CTE (Common Table Expression) is a temporary result set defined with WITH, used to simplify complex queries.

Both are temporary. Neither creates a real table. But their structure, clarity, and reuse differ greatly.

‍

✅ When to Use a CTE

1. For Clarity and Modularity

Long nested subqueries are hard to read and debug. CTEs let you name your logic and break it into layers — like functions.

Instead of this:

SELECT *FROM (    SELECT customer_id, SUM(amount)    FROM orders    WHERE status = 'completed'    GROUP BY customer_id) tWHERE t.sum > 1000

Use this:

WITH CompletedOrders AS (    SELECT customer_id, SUM(amount) AS total    FROM orders    WHERE status = 'completed'    GROUP BY customer_id) SELECT *FROM CompletedOrdersWHERE total > 1000

🟢 Easier to explain. Easier to reuse. Easier to maintain.

‍

2. When Reusing the Same Logic Multiple Times

If you calculate the same subquery logic more than once, a CTE avoids duplication and reduces errors.

WITH HighValueOrders AS (    SELECT * FROM orders WHERE amount > 1000)SELECT COUNT(*) FROM HighValueOrders;SELECT AVG(amount) FROM HighValueOrders;

‍

3. For Recursive Queries

CTEs are your only option for recursive queries like hierarchies or pathing logic — something subqueries can’t do.

‍

🧨 When a Subquery Might Be Better

1. When Simplicity Wins

If the subquery is short, one-time, and doesn’t benefit from naming — keep it inline.

SELECT nameFROM customersWHERE id IN (    SELECT customer_id FROM orders WHERE amount > 500)

Quick, readable, done.

‍

2. When You Need Performance Tuning Tricks

Some SQL engines optimize subqueries differently than CTEs. In complex joins, CTEs can be materialized (run as-is, not optimized), while subqueries may be more flexible.

In some cases, converting a slow CTE to a subquery improves performance. Always test both.

‍

✅ Wrap-Up: Use CTEs to Think in Layers

CTEs make your SQL more modular, readable, and logical — especially when working on large queries, dashboards, or reports.

Subqueries still shine for fast filtering and simple lookups.

So don’t ask which one is better. Ask what will make your future self (and your teammates) say:
“Ah, I get it.”

‍

📚 Further Reading

• CTEs Explained — Mode SQL Tutorial
• How SQL CTEs Work — SQLShack
• Why Subqueries Still Matter — Data School

{{quiz}}