Subqueries vs CTEs in SQL: A Practical Guide for Data Analysts

💡 How I Finally Understood Subqueries vs CTEs

When I first started learning SQL, I remember feeling confident—until I ran into subqueries.

At first, they seemed simple. “Just a query inside another query,” I told myself. But the moment I started working with more complex datasets, my queries became harder to read, harder to debug, and honestly… frustrating.

Then I discovered CTEs.

I still remember rewriting one of my messy nested queries using a CTE—and suddenly, everything made sense. The logic was clearer, the structure felt natural, and debugging became much easier. That was the moment I realized something important:

Writing SQL isn’t just about getting the right answer—it’s about writing queries that make sense.

Since then, I’ve learned that both subqueries and CTEs are powerful tools—but they serve different purposes. Knowing when to use each is what separates beginner SQL users from confident data analysts.

In this article, I’ll break down both concepts in a simple, practical way—based on what actually works in real-world data analysis.

When working with SQL, you’ll eventually run into situations where a single query isn’t enough. You need to break down logic, reuse results, or simplify complex operations.

---

🔍 Understanding Subqueries (The “Quick Solution” Tool)

A subquery is simply a query inside another query. Think of it as asking SQL to first answer a smaller question before solving the main one.

Example:

SELECT customer_name
FROM Customers
WHERE customer_id IN (
    SELECT customer_id
    FROM Orders
    WHERE total_amount > 1000
);

In this case, SQL first finds customers with large orders, then uses that result to filter the main query.

---

🔹 Types of Subqueries You’ll Encounter

1. Single-Value Subqueries

Used when you expect one result (e.g., averages).

SELECT *
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);

---

2. Multi-Value Subqueries

Return multiple results and are often used with IN.

SELECT *
FROM Products
WHERE product_id IN (
    SELECT product_id FROM Sales
);

---

3. Correlated Subqueries

These are more dynamic—they run once for every row in the outer query.

SELECT c.customer_name
FROM Customers c
WHERE EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.customer_id = c.customer_id
);

👉 Powerful, but can be slow if not used carefully.

---

🎯 When Subqueries Make Sense

Subqueries are best when:

• You need a quick filter
• The logic is simple
• You don’t need to reuse the result
• You’re working with aggregates like AVG or SUM

They’re great for straightforward problems, but can get messy fast.

---

🧠 CTEs: A More Structured Approach

A CTE (Common Table Expression) is like giving a name to a temporary result so you can use it in your query.

It’s defined using the WITH keyword.

Example:

WITH HighValueCustomers AS (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM Orders
    GROUP BY customer_id
)
SELECT *
FROM HighValueCustomers
WHERE total_spent > 1000;

Instead of nesting queries, you separate the logic into steps—which makes everything easier to read.

---

🔹 Types of CTEs

1. Simple CTE

Used to simplify complex queries into readable steps.

---

2. Recursive CTE

Useful for hierarchical data like:

• Organizational structures
• Category trees

---

3. Multi-CTE Queries

You can define multiple CTEs and combine them—very useful in real projects.

---

⚖️ Subqueries vs CTEs: What Really Matters

Let’s go beyond definitions and look at what actually matters in practice:

Aspect	Subqueries	CTEs
Readability	Can become confusing quickly	Much easier to follow
Reusability	One-time use	Reusable within the query
Debugging	Harder to isolate issues	Easier to test step-by-step
Performance	Can be inefficient (especially correlated ones)	Often optimized better
Best Use	Simple filtering	Complex logic

---

🚀 Choosing the Right Approach

Here’s a simple way to think about it:

Use Subqueries if:

• The query is small and simple
• You only need the result once
• You’re filtering data

---

Use CTEs if:

• The query is getting hard to read
• You want to break logic into steps
• You need to reuse results
• You’re working on real-world data analysis

---

📊 Real-World Perspective

In real projects (especially dashboards or reporting):

• Subqueries are often used for quick checks
• CTEs are used to structure complex transformations

If you're building something that others will read or maintain, CTEs are usually the better choice.

---

🧾 Final Thoughts

Subqueries and CTEs solve similar problems—but they do it in different ways.

Subqueries are quick and compact.
CTEs are structured and scalable.

The real skill isn’t just knowing them—it’s knowing when to use each.

---

📌 Key Takeaway

If your query is becoming hard to read, that’s your signal to switch to a CTE.

---

✍️ Enock Kiprotich

Aspiring Data Analyst | SQL | Power BI | Python

📍 Open to Data Analyst Roles

🔗 linkedin.com/in/enock-kiprotich-30382a189