SQL Subqueries and CTEs

When I first came across subqueries and CTEs, I honestly thought they were just more complicated SQL syntax I had to memorize.

After practicing them, I realised the idea is actually pretty simple.

Both help you break a bigger SQL problem into smaller pieces.

What is a subquery?

A subquery is a query inside another query.

For example, imagine I have a students table and I want to find students who scored above the average.

The inner query calculates the average score first:

SELECT AVG(score) FROM students

Then the outer query finds students whose scores are higher than that average.

That's the basic idea of a subquery.

Another example

Let's say I have a products table and I want to find products that are more expensive than the average product.

Instead of manually calculating the average first and then writing another query, SQL does both steps for me.

Finding the highest value

Here's another simple example.

Suppose I want to find the employee with the highest salary:

The inner query finds the highest salary.

The outer query finds the employee who has it.

What are CTEs?

CTE stands for Common Table Expression.

A CTE lets me create a temporary result first and then use that result in another query.

It starts with WITH.

For example:

This does almost the same thing as the earlier subquery.

The difference is that I gave the intermediate result a name: average_score.

I find this easier to understand when the query starts getting longer.

A sales example

Imagine I'm working with sales data and I want to know which salespeople made more than KSh 100,000.

First, I calculate each person's total:

Then I use that result:

SELECT salesperson, total_sales FROM sales_totals WHERE total_sales > 100000;

I like this approach because I can clearly see the two steps:

1. Calculate total sales.
2. Filter the people who exceeded 100,000.

CTEs can also make JOINs easier

Let's say I have a customers table and an orders table.

I want to calculate how much each customer has spent, then find customers who spent more than KSh 50,000.

That query looks a little bigger, but the logic is much easier to follow when I break it down.

First, SUM(amount) calculates the customer's spending.

Then the result is joined to the customer table.

Finally, I filter customers who spent more than 50,000.

Subquery vs CTE

A subquery is usually written inside another query.

A CTE is created before the main query using WITH.

Both can solve similar problems.

The main difference for me is readability and structure.

When would I use a subquery?

I'd use a subquery when the problem is fairly simple.

For example:

I don't really need to break that into several steps.

When would I use a CTE?

I'd use a CTE when I'm dealing with multiple steps or when the query is becoming difficult to read.

For example:

The CTE gives the intermediate result a name, which makes the query easier to understand and maintain.

How I remember them

I think of a subquery as:

"I need the result of this query inside another query."

And a CTE as:

"Let me calculate this first, give it a name, and then use it."

That's what finally made these concepts click for me.

They aren't really about memorising complicated syntax.