💡 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
);
Enter fullscreen mode
Exit fullscreen mode
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);
Enter fullscreen mode
Exit fullscreen mode
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
);
Enter fullscreen mode
Exit fullscreen mode
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
);
Enter fullscreen mode
Exit fullscreen mode
👉 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;
Enter fullscreen mode
Exit fullscreen mode
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