Even after writing SQL for quite some time, I still find it a bit overwhelming when I face a massive block of code. Especially as the queries start to pile up, the state of the code becomes a tangled mess! 🤯

But here’s something I’ve learned the hard way:

As queries grow in size and complexity, isolating and testing specific sections of code can become a real challenge, especially during debugging and code reviews.

Don’t get me wrong, there are definitely times when I just need a quick, ad-hoc-style query to count filtered values from a single table. But when you’re working with production-grade applications, a set of clean, efficient, and maintainable code becomes essential.

So, how do we approach this? do we use Subquery or CTE? Let’s firstly explore each of the key concepts.

🤔 What’s a Subquery?

Like the name suggest, a subquery is a query inside another query. It’s a way to grab some data first and then use that result to help answer your main query.

Example: This basically will give result of sales data that has total sales greater than 10,000 in value

SELECT *
FROM (
  SELECT salesperson_id, total_sales
  FROM sales
  WHERE total_sales > 10000
) AS top_sales;

👀 This is how the concept is stacking:

Main Query
  └── Subquery (runs first)
        └── SELECT from sales

❓ What’s a CTE?

A CTE (Common Table Expression) is like a temporary, named result set that you can use within your SQL query. Think of it like creating a mini “view” that you can refer to multiple times in the same query.

Let’s say you need to run a complex subquery, but you don’t want to repeat it over and over. Instead of rewriting the same logic, you define it once with a CTE and give it a name. You can then use that name later in the query as if it were a table or a subquery.

Example: Same as the subquery example above, this will generate result of sales data that has total sales greater than 10,000 in value. The only difference is the way it is written.

WITH top_sales AS (
  SELECT salesperson_id, total_sales
  FROM sales
  WHERE total_sales > 10000
)
SELECT *
FROM top_sales;

🧠 How the concept looks:

CTE (temporary table)
  └── SELECT from sales
Main Query
  └── SELECT from CTE

🧪 Comparison

Now let’s compare the two concepts.

Example with Subquery: Now, I’ve tried expanding the code with subquery.

SELECT e.name
FROM employees e
JOIN (
  SELECT salesperson_id
  FROM sales
  WHERE total_sales > 10000
) s ON e.id = s.salesperson_id;

By using subquery, we are stacking the logic right into the middle of the main query, which is fine at first. But do it a few more times, and it starts to get messy, especially if there are multiple tables to query from.

As you can see, the query can get cluttered fast!

Now an example with CTE:

WITH top_sales AS (
  SELECT salesperson_id
  FROM sales
  WHERE total_sales > 10000
)
SELECT e.name
FROM employees e
JOIN top_sales s ON e.id = s.salesperson_id;

I’d say this is much easier on the eyes. The logic is isolated and easier to test or modify. It’s also scalable for query extensions, for example joining to other tables and multiple filter clauses or aggregations.

☑️ Checking of Criteria

Here’s how CTE and Subquery is positioned when it comes to the core criteria of Reusability, Readability, Debugging and Recursion.

Subquery:

• 🔁 Reusability: Gets messy with nesting
• ✅ Readability: Not reusable
• 🛠️ Debugging: Hard to isolate logic
• 🔄 Recursion: Not supported!

CTE:

• 🔁 Reusability: Super clean
• ✅ Readability: Reusable
• 🛠️ Debugging: Easy to test
• 🔄 Recursion: It can recurse!

✅ When to use what?

Use subqueries when:

• It’s simple and one-off
• You’re writing quick-and-dirty logic
• Readability isn’t an issue
• Only using a single table without joins
• Analysis is ad-hoc

Use CTEs when:

• Queries are getting complex
• You need to reuse the logic
• You care about keeping things clean and understandable
• You are using multiple tables with joins
• Analysis is Production-grade

🚀 Final Thoughts

Both subqueries and CTEs are great tools, however for complex logic or anything that needs to be reused or debugged later, CTEs are by far a better option.

Next time your query starts looking like tangled yarn, give a CTE a try!