When you first start learning SQL, everything feels simple. You write basic SELECT queries, maybe add a WHERE, and life is good.
Then suddenly you meet things like subqueries and CTEs, and it feels like SQL switched languages overnight.
I remember staring at nested queries thinking, “Why would anyone write a query inside another query?”
Turns out… there’s a reason. And once it clicks, it actually makes SQL a lot more powerful and clean.
Let’s break it down in a simple way.
Subqueries (a query inside a query)
A subquery is exactly what it sounds like — you run one query inside another one.
Think of it like:
“I need one answer first, so I can use it in another question.”
Example:
```sql id="a1b2c3"
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
What’s happening here?
* First, SQL calculates the average salary
* Then it uses that result to filter employees
So instead of doing it manually, SQL handles it in one go.
---
# A simple way to think about it
Imagine your manager says:
> “Show me employees who earn more than the company average.”
You don’t guess the average. You calculate it first. That’s the subquery part.
---
# When subqueries make sense
Use them when:
* You need a quick calculation inside another query
* You’re filtering using averages, counts, or totals
* The logic is small enough to fit in one place
But honestly… once things get more complex, subqueries start to feel messy.
That’s where CTEs come in.
---
# CTEs (Common Table Expressions)
CTEs are just a cleaner way of writing complicated queries.
Instead of nesting everything, you break your query into steps and give them names.
It starts with `WITH`.
### Example:
```sql id="d4e5f6"
WITH high_earners AS (
SELECT name, salary
FROM employees
WHERE salary > 50000
)
SELECT *
FROM high_earners;
Why CTEs feel better
If subqueries feel like stacking papers inside envelopes, CTEs feel like labeling folders.
You can actually read your query like a story:
- First, get high earners
- Then use them in the final result
It’s much easier to follow.
Same problem, two styles
Let’s say we want customers who spent more than 1000.
Subquery version:
```sql id="g7h8i9"
SELECT customer_id, total_spent
FROM (
SELECT customer_id, SUM(amount) AS total_spent
FROM sales
GROUP BY customer_id
) x
WHERE total_spent > 1000;
### CTE version:
```sql id="j1k2l3"
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM sales
GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total_spent > 1000;
Same result. One just feels way easier to read.
When to use what (real talk)
Use subqueries when:
- The logic is short
- You just need one quick calculation
- You don’t want extra structure
Use CTEs when:
- Your query is getting long
- You’re doing multiple steps
- You want your SQL to look clean and readable
- You’re working in a team
The honest takeaway
Nobody really cares if you use subqueries or CTEs in isolation.
What matters is:
Can someone else read your SQL without getting confused?
That’s where CTEs usually win.
But good SQL developers don’t pick sides — they use whatever makes the query clearer.
Final thought
When I first learned this, I thought it was just “advanced SQL stuff.”
But really, it’s just about organizing your thinking.
Subqueries = quick thinking
CTEs = structured thinking
Both are useful. You just get better at knowing when to switch.
If you're learning SQL right now, don’t rush this part. Write messy queries first, then refactor them into cleaner CTEs. That’s where the real growth happens.