Leah Kivuti

Posted on Apr 28

SQL Subqueries vs CTEs: What I Wish Someone Told Me When I Was Learning SQL

#beginners #tutorial #database #sql

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.

DEV Community