SQL Is Surviving, Franklin: When One Query Isn't Enough

Franklin survived SQL JOINs.

Barely.

Last time, we made tables talk to each other. We connected students to classes, classes to teachers, and somehow convinced Franklin that duplicate rows were not a database malfunction.

He was beginning to feel confident.

Then someone asked:

"Which students scored above the average?"

Franklin smiled.

Easy.

He opened his laptop and started typing.

Then he stopped.

Wait.

What is the average?

He needs to calculate the average before he can compare the students' scores to it.

And suddenly, SELECT doesn't feel so simple anymore.

Because sometimes...

one query isn't enough.

Welcome back to SQL survival.

Today, we're talking about subqueries and CTEs.

---

Previously in SQL...

In the first article, we survived the basics:

• DDL
• DML
• DQL
• SELECT
• INSERT
• UPDATE
• DELETE

Then came JOINs.

We learned how to combine information from multiple tables using:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN

We also learned about primary keys, foreign keys, aliases, and relationships.

Basically, Franklin learned how to get tables to communicate.

But now we have a different problem.

Sometimes the information we need isn't simply sitting in another table.

Sometimes we need to calculate something first, then use that result to answer another question.

That's where subqueries come in.

---

So... What Is a Subquery?

A subquery is simply a query inside another query.

That's it.

A query...

inside another query.

SQL looked at one query and thought:

"You know what would make this more interesting?"

Another query.

For example, suppose Greenwood Academy has this table:

scores

student_id	student_name	subject	score
1	Brian	Mathematics	85
2	Mercy	Mathematics	72
3	Kevin	Mathematics	91
4	Sarah	Mathematics	64
5	Jane	Mathematics	78

Now the principal asks:

"Which students scored above the average?"

We could first calculate the average:

SELECT AVG(score)
FROM scores;

Suppose SQL gives us:

78

Now we need:

SELECT student_name, score
FROM scores
WHERE score > 78;

But there's a problem.

We don't want to manually type 78.

What if the data changes?

What if another student gets added?

What if the average becomes 79.4?

We want SQL to calculate the average for us.

So we put one query inside another.

---

Our First Subquery

SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT AVG(score)
    FROM scores
);

Look closely.

We have:

SELECT AVG(score)
FROM scores

inside:

SELECT
    student_name,
    score
FROM scores
WHERE score > (...)

The inner query calculates the average.

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

So conceptually:

              INNER QUERY
                   ↓
          Calculate the average
                   ↓
                 78
                   ↓
              OUTER QUERY
                   ↓
       Find scores greater than 78

That's a subquery.

Franklin has officially taught SQL to ask itself a question.

---

How Does SQL Execute This?

At a simplified level, you can think of it like this:

Step 1

SQL runs:

SELECT AVG(score)
FROM scores;

Result:

78

Step 2

SQL effectively uses that result here:

WHERE score > 78

Step 3

It returns:

student_name	score
Brian	85
Kevin	91

And Franklin looks at the result like:

"Oh."

"That actually worked."

---

Subqueries in WHERE

One of the most common places you'll see a subquery is inside a WHERE clause.

Let's try another example.

Suppose we want students who scored the highest score.

First, we could find the highest score:

SELECT MAX(score)
FROM scores;

Suppose the result is:

91

Then we could find the student:

SELECT
    student_name,
    score
FROM scores
WHERE score = 91;

But again, we can let SQL do both jobs:

SELECT
    student_name,
    score
FROM scores
WHERE score = (
    SELECT MAX(score)
    FROM scores
);

The inner query finds the highest score.

The outer query finds the student who achieved it.

---

The Inner Query vs The Outer Query

This terminology is worth remembering.

In:

SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT AVG(score)
    FROM scores
);

The query inside the parentheses is the:

subquery / inner query

The query surrounding it is the:

outer query

Think of it like:

OUTER QUERY
┌───────────────────────────────┐
│                               │
│   WHERE score >               │
│          ↓                    │
│      ┌───────────────┐        │
│      │  SUBQUERY     │        │
│      │               │        │
│      │ AVG(score)    │        │
│      └───────────────┘        │
│                               │
└───────────────────────────────┘

A query inside a query.

SQL nesting dolls.

---

Subqueries With IN

Subqueries aren't limited to returning one value.

Sometimes the inner query returns multiple values.

That's where IN becomes useful.

Suppose we have:

students

student_id	student_name	class_id
1	Brian	101
2	Mercy	101
3	Kevin	102
4	Sarah	103

And:

classes

class_id	class_name
101	Form 4A
102	Form 3B
103	Form 2C

Now imagine we want:

"Show me students who are in Form 4A or Form 3B."

The inner query can find the relevant class IDs:

SELECT class_id
FROM classes
WHERE class_name IN ('Form 4A', 'Form 3B');

That gives us:

101
102

Then the outer query can use those values:

SELECT
    student_name
FROM students
WHERE class_id IN (
    SELECT class_id
    FROM classes
    WHERE class_name IN ('Form 4A', 'Form 3B')
);

The subquery returns multiple values:

101
102

And the outer query asks:

"Is this student's class_id in that list?"

---

IN vs =

This is an important distinction.

If your subquery returns one value, you might use:

=

For example:

WHERE score = (
    SELECT MAX(score)
    FROM scores
);

But if your subquery can return multiple values, you can use:

IN

For example:

WHERE class_id IN (
    SELECT class_id
    FROM classes
    WHERE class_name IN ('Form 4A', 'Form 3B')
);

Think:

=    → I expect one value

IN   → I can work with a list of values

And this distinction matters.

If your subquery returns multiple rows and you use =, SQL may complain.

And Franklin does not need another reason to panic.

---

Subqueries in FROM

Here's where things get a little more interesting.

A subquery can also appear inside the FROM clause.

For example:

SELECT *
FROM (
    SELECT
        student_name,
        score
    FROM scores
) AS student_scores;

Here, the subquery creates a temporary result that the outer query can work with.

You can think of it as:

"Run this query first, then treat its result like a table."

For example, suppose we want students who scored above 80.

We could create a temporary result:

SELECT
    student_name,
    score
FROM scores
WHERE score > 80;

Then use it:

SELECT *
FROM (
    SELECT
        student_name,
        score
    FROM scores
    WHERE score > 80
) AS high_scorers;

The AS high_scorers part gives the temporary result a name.

That's called an alias.

And yes, SQL is once again asking you to name things.

SQL loves naming things.

---

Why Would We Do That?

You might be wondering:

"Why not just write the original query?"

Good question.

For a simple example like this, there isn't much benefit.

The point is to understand the concept.

Subqueries in FROM become more useful when the inner query performs some calculation or transformation that the outer query needs to work with.

For example:

SELECT
    class_id,
    AVG(score) AS average_score
FROM scores
GROUP BY class_id;

Now imagine we want to work with those class averages in another query.

A subquery can give us that intermediate result.

This is where queries can start getting complicated.

And eventually, Franklin starts staring at his screen like:

"Who wrote this?"

He did.

Three hours ago.

---

Subqueries in SELECT

Yes.

We can even put a subquery inside the SELECT list.

For example:

SELECT
    student_name,
    score,
    (
        SELECT AVG(score)
        FROM scores
    ) AS average_score
FROM scores;

This can produce something like:

student_name	score	average_score
Brian	85	78
Mercy	72	78
Kevin	91	78
Sarah	64	78
Jane	78	78

The average is calculated by the subquery and displayed alongside each student.

Now, for a simple example like this, there are often better ways to solve the problem.

But it's useful to understand that a subquery can appear in different parts of a SQL statement.

---

Then Things Get Complicated

Subqueries are powerful.

But you can absolutely take them too far.

For example:

SELECT ...
FROM (
    SELECT ...
    FROM (
        SELECT ...
        FROM (
            SELECT ...
        ) AS something
    ) AS something_else
) AS another_thing;

At some point, Franklin isn't writing SQL anymore.

He's opening a portal.

This is one reason readability matters.

A query can be technically correct and still be painful for another human being to understand.

And that brings us to our next character.

---

Enter: The CTE

CTE stands for:

Common Table Expression

Don't let the name scare you.

A CTE is essentially a named temporary result that you define before the main query.

You create it using:

WITH

For example:

WITH average_score AS (
    SELECT AVG(score) AS avg_score
    FROM scores
)
SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT avg_score
    FROM average_score
);

At first glance, that might look like more SQL than our original solution.

And technically, it is.

But the structure is much easier to understand.

We're saying:

"First, calculate the average score."

Then:

"Now use that result to find students who scored above it."

---

Breaking Down a CTE

Let's separate the pieces.

WITH average_score AS (
    SELECT AVG(score) AS avg_score
    FROM scores
)

This creates a CTE called:

average_score

Inside it, we calculate:

AVG(score)

and give that result the name:

avg_score

Then comes the main query:

SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT avg_score
    FROM average_score
);

So conceptually:

WITH
   ↓
Create a temporary named result
   ↓
average_score
   ↓
Use it in the main query

It's much easier to read when your query becomes more complicated.

---

CTEs Are Like Giving Your Query Steps

This is probably the easiest way to think about them.

Instead of writing:

"SQL, do all of this giant thing and good luck."

You can say:

"First, do this."

Then:

"Now do this."

Then:

"Finally, give me this."

For example:

WITH average_score AS (
    SELECT AVG(score) AS avg_score
    FROM scores
)
SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT avg_score
    FROM average_score
);

The query now has a visible structure.

Step 1 → Calculate average

Step 2 → Compare student scores to average

Step 3 → Return students above average

Franklin can breathe again.

---

Multiple CTEs

Now we're getting somewhere.

You can define more than one CTE.

Suppose Greenwood Academy wants to calculate:

1. The average score for each class
2. Then find classes whose average is above 80 as the top students

We could start with:

WITH class_averages AS (
    SELECT
        class_id,
        AVG(score) AS average_score
    FROM scores
    GROUP BY class_id
)
SELECT
    class_id,
    average_score
FROM class_averages
WHERE average_score > 80;

The CTE handles the first task:

Calculate class averages

The main query handles the second:

Find averages above 80

This is where CTEs start becoming really useful.

---

Multiple CTEs Can Build on Each Other

You can also have one CTE build on another.

For example:

WITH student_averages AS (
    SELECT
        student_id,
        AVG(score) AS average_score
    FROM scores
    GROUP BY student_id
),
top_students AS (
    SELECT
        student_id,
        average_score
    FROM student_averages
    WHERE average_score >= 80
)
SELECT *
FROM top_students;

Look at what happened.

First:

student_averages

calculates each student's average.

Then:

top_students

uses that result.

Then the final query retrieves the students we want.

This is much easier to follow than trying to cram everything into one enormous statement.

---

Subquery vs CTE

Now comes the question:

"So which one should I use?"

The answer isn't:

"CTEs are always better."

That would be too easy.

Both have their place.

Use a subquery when:

• the logic is simple
• the result is only needed once
• the query remains readable
• you don't need to reuse the intermediate result

For example:

SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT AVG(score)
    FROM scores
);

That's perfectly reasonable.

Use a CTE when:

• the query has multiple logical steps
• you want to make the query easier to read
• an intermediate result is reused
• the query is becoming difficult to understand

For example:

WITH student_averages AS (
    SELECT
        student_id,
        AVG(score) AS average_score
    FROM scores
    GROUP BY student_id
)
SELECT *
FROM student_averages
WHERE average_score >= 80;

The important thing isn't:

"Always use CTEs."

It's:

Choose the structure that makes your logic easiest to understand and maintain.

---

A Realistic Greenwood Academy Problem

Let's give Franklin an actual assignment.

The principal walks in and says:

"Find all students whose average score is higher than the overall average score."

Now we're dealing with two different calculations.

We need:

1. Each student's average
2. The overall average

Then we compare them.

Franklin starts sweating.

But we can break it down.

---

Step 1: Calculate each student's average

SELECT
    student_id,
    AVG(score) AS student_average
FROM scores
GROUP BY student_id;

This gives us something like:

student_id	student_average
1	85
2	72
3	91
4	64
5	78

---

Step 2: Calculate the overall average

SELECT AVG(score)
FROM scores;

Suppose the result is:

78

Now we need to compare the two.

This is where a CTE makes the logic easier to follow.

WITH student_averages AS (
    SELECT
        student_id,
        AVG(score) AS student_average
    FROM scores
    GROUP BY student_id
)
SELECT
    student_id,
    student_average
FROM student_averages
WHERE student_average > (
    SELECT AVG(score)
    FROM scores
);

Now we're saying:

"Give me students whose average is greater than the overall average."

That's a much more interesting question than simply:

"Who scored above 80?"

And this is the kind of problem where subqueries and CTEs start becoming genuinely useful.

---

What About Correlated Subqueries?

Now we're stepping into slightly more advanced territory.

A correlated subquery is a subquery that depends on a value from the outer query.

Unlike our earlier subqueries, the inner query isn't completely independent.

For example, imagine we have:

employees

employee_id	employee_name	department_id	salary
1	Brian	10	80000
2	Mercy	10	95000
3	Kevin	20	70000
4	Sarah	20	85000

We want:

"Find employees earning more than the average salary in their own department."

Now the average depends on the employee's department.

We could write:

SELECT
    e.employee_name,
    e.department_id,
    e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

Notice this:

WHERE e2.department_id = e.department_id

The inner query refers to the outer query's e.department_id.

That's what makes it correlated.

The inner query needs information from the current row of the outer query.

---

Why Correlated Subqueries Matter

The previous example asks a more specific question.

Not:

"Who earns more than the average salary?"

But:

"Who earns more than the average salary for their own department?"

That's an important difference.

The comparison changes depending on the employee.

Conceptually:

Brian
  ↓
Find average salary in Brian's department
  ↓
Compare Brian's salary

Mercy
  ↓
Find average salary in Mercy's department
  ↓
Compare Mercy's salary

Kevin
  ↓
Find average salary in Kevin's department
  ↓
Compare Kevin's salary

The subquery is connected to the current row.

That's the idea behind correlation.

---

Don't Use a CTE Just Because You Can

Now that you've discovered CTEs, you may feel tempted to put WITH before everything.

Please don't.

This:

WITH students AS (
    SELECT *
    FROM students
)
SELECT *
FROM students;

doesn't make you look advanced.

It just makes Franklin tired.

If a simple query solves the problem clearly, use the simple query.

The goal isn't to use the most complicated SQL possible.

The goal is to write SQL that is:

• correct
• readable
• maintainable
• appropriate for the problem

Fancy SQL is not automatically good SQL.

---

Common Subquery Mistakes

Let's talk about the ways Franklin can still suffer.

1. Returning multiple rows when one value is expected

Suppose you write:

WHERE score = (
    SELECT score
    FROM scores
);

The inner query may return multiple scores.

But = expects one value.

That's a problem.

If the subquery returns multiple values, you might need something like:

IN

instead.

---

2. Forgetting the alias

When using a subquery in FROM, you generally need to give the derived table an alias.

For example:

SELECT *
FROM (
    SELECT
        student_name,
        score
    FROM scores
) AS student_scores;

Here:

student_scores

is the alias.

Don't leave your temporary result wandering around without a name.

---

3. Making the query impossible to read

This is probably the biggest danger.

You can technically nest queries inside queries inside queries.

But just because SQL lets you doesn't mean you should.

If your query looks like something Franklin wrote at 3:47 a.m. with one eye open...

stop.

Consider breaking the logic into a CTE.

---

4. Forgetting what your subquery returns

Before using a subquery, ask:

Does it return one value?

Or:

Does it return multiple rows?

Or:

Does it return an entire table-like result?

That determines how you can use it.

For example:

One value
→ =, >, <, etc.

Multiple values
→ IN, ANY, ALL, etc.

Table-like result
→ FROM / JOIN

Understanding the shape of your result is just as important as writing the syntax.

---

Subqueries vs JOINs

This is where people often get confused.

You might look at a problem and think:

"Should I use a JOIN or a subquery?"

The answer depends on what you're trying to accomplish.

A JOIN is primarily about combining related data from different tables.

For example:

SELECT
    s.student_name,
    c.class_name
FROM students AS s
JOIN classes AS c
    ON s.class_id = c.class_id;

You're bringing related information together.

A subquery is often about using the result of one query to help answer another question.

For example:

SELECT
    student_name,
    score
FROM scores
WHERE score > (
    SELECT AVG(score)
    FROM scores
);

You're using one calculation to filter another result.

So think:

JOIN
→ "Bring these related tables together."

SUBQUERY
→ "Use this query's result to answer another question."

Sometimes there are multiple valid ways to solve the same problem.

That's normal.

SQL gives you options.

Franklin gives himself headaches by trying all of them.

---

A Quick Mental Model

When you see a SQL question, ask yourself:

Do I need information from another table?

Think:

JOIN

Do I need the result of another query?

Think:

SUBQUERY

Is the logic becoming a sequence of steps?

Think:

CTE

For example:

"Show students and their class names."

JOIN

"Show students who scored above the average."

SUBQUERY

"Calculate student averages, filter them, then compare them with another result."

CTE + possibly a subquery

That mental distinction will save you a lot of confusion.

---

Franklin's Survival Guide

Let's make this painfully simple.

Subquery

A query inside another query.

SELECT ...
WHERE score > (
    SELECT AVG(score)
    FROM scores
);

Think:

One query needs another query's answer.

---

IN

Useful when the subquery returns multiple values.

WHERE class_id IN (
    SELECT class_id
    FROM classes
);

Think:

Is my value somewhere in this result?

---

CTE

A named temporary result created with WITH.

WITH average_scores AS (
    SELECT AVG(score) AS avg_score
    FROM scores
)
SELECT *
FROM average_scores;

Think:

Let's give this step a name.

---

Correlated subquery

A subquery that depends on the outer query.

Outer row
   ↓
Inner query uses information from that row
   ↓
Result

Think:

The inner query needs to know what the outer query is currently looking at.

---

Final Thoughts

So...

Franklin can now make tables talk.

He can JOIN them.

He can filter them.

And apparently, he can now make SQL ask itself questions.

We've gone from:

SELECT

to:

SELECT
    ...
WHERE something > (
    SELECT ...
);

And then to:

WITH something AS (
    SELECT ...
)
SELECT ...

Which is a pretty big leap from where we started.

But the most important thing isn't memorizing every variation of subqueries and CTEs.

It's learning how to break a problem down.

Ask yourself:

What do I need first?

Then:

What do I need to do with that result?

That little habit will take you surprisingly far.

Because SQL isn't really about memorizing hundreds of commands.

It's about learning how to turn a question into a series of smaller questions...

and then convincing the database to answer them.

Franklin is beginning to understand.

He's no longer just surviving SQL.

He's starting to think in SQL.

Which is great.

Until someone introduces him to window functions.

Because apparently, the rows need to start competing now.

But that's a problem for another day.