Every query we've written so far has been self-contained. One SELECT, one table, one question.
But some questions can't be answered in one step.
"Which quiz scores are higher than the group average?" - you need the average first. "Which members scored above 80?" - you need the member IDs first, then the names. "Who improved from Quiz 1 to Quiz 2?" - you need two separate lookups before you can compare.
A subquery is the solution. It's a SELECT written inside another SELECT. The inner one runs first - its result gets handed to the outer query to use. Two questions, one statement.
Today we worked through 8 questions on a new dataset: a study group with 16 quiz scores across 7 members. We solved every question manually first - two separate queries - then collapsed them into one using a subquery. That pattern is the most important thing in this session. Not the syntax. The pattern.
Where Subqueries Can Live
Before any questions, I drew this on the board:
-- 1) In WHERE/HAVING - filter using a calculated value
WHERE score > (SELECT AVG(score) FROM quiz_scores)
-- 2) In FROM - use a calculated table as if it were a real table
FROM (SELECT member_id, AVG(score) FROM quiz_scores GROUP BY member_id) AS averages
-- 3) In SELECT - add a single computed value to every row
SELECT score, (SELECT AVG(score) FROM quiz_scores) AS group_avg
Three positions. Different jobs. We'll use all three today.
The Study Group Dataset
Two tables: study_group.members (member ID, name, city) and study_group.quiz_scores (score ID, member ID, quiz number, score). Sixteen rows of quiz data across seven members. Simple enough to see what's happening, realistic enough that the questions actually matter.
Q1: Scores Above the Group Average
"Which quiz scores are higher than the average score of the whole group?"
Step 1 - find the average:
SELECT AVG(score) FROM study_group.quiz_scores;
-- 73.5625
Step 2 - use that number:
SELECT score_id, member_id, score
FROM study_group.quiz_scores
WHERE score > 73.5625;
This works. But it's fragile. Next week, when new scores are added, the average changes and this query is wrong. We've hardcoded a number that should be dynamic.
Subquery - dynamic forever:
SELECT score_id, member_id, score
FROM study_group.quiz_scores
WHERE score > (SELECT AVG(score) FROM study_group.quiz_scores);
The inner query runs first, calculates today's average, and passes it to the outer WHERE. Tomorrow, when new scores land, this query automatically uses the new average. No manual updating.
I told the class: "The moment you find yourself copying a number from one query result into another query - stop. That's a subquery waiting to be written."
Q2: The Single Highest Score
"Find the member who recorded the highest score."
Step 1:
SELECT MAX(score) FROM study_group.quiz_scores;
-- 95
Step 2:
SELECT score_id, member_id, score
FROM study_group.quiz_scores
WHERE score = 95;
Subquery:
SELECT score_id, member_id, score
FROM study_group.quiz_scores
WHERE score = (SELECT MAX(score) FROM study_group.quiz_scores);
Clean, one statement, always returns the current highest score regardless of what the data looks like. If two members tie for first, both rows come back - MAX() returns the value, and = matches every row with that value.
Q3: Beat Felix's Score
"Find all scores that beat Felix's Quiz 1 result."
This one is interesting because the inner query isn't an aggregate - it's a lookup for a specific row.
Step 1:
SELECT score
FROM study_group.quiz_scores
WHERE member_id = 5 AND quiz_number = 1;
-- 91
Step 2:
SELECT score_id, member_id, score
FROM study_group.quiz_scores
WHERE score > 91;
Subquery:
SELECT score_id, member_id, score
FROM study_group.quiz_scores
WHERE score > (
SELECT score
FROM study_group.quiz_scores
WHERE member_id = 5 AND quiz_number = 1
);
The inner query returns one value - Felix's Quiz 1 score. The outer query compares every row against it. I made a point here: "A subquery in WHERE must return exactly one value when used with = or >. If Felix had two Quiz 1 entries and the inner query returned two rows, PostgreSQL would throw an error. Always be sure your inner query returns what the outer query expects."
Q4: Names of Members Who Scored Above 80
Now subqueries get more interesting. The quiz scores table has member IDs. The members table has names. The question asks for names. Two tables, two steps, one answer.
Step 1 - get the IDs:
SELECT member_id
FROM study_group.quiz_scores
WHERE score > 80;
-- 1, 2, 4, 5, 7
Step 2 - get the names using IN:
SELECT name, member_id, city
FROM study_group.members
WHERE member_id IN (1, 2, 4, 5, 7);
Subquery:
SELECT name, member_id, city
FROM study_group.members
WHERE member_id IN (
SELECT member_id
FROM study_group.quiz_scores
WHERE score > 80
);
The IN operator is perfect here because the inner query returns a list - multiple member IDs, not just one. IN checks whether each row's member_id appears in that list.
The connection I made to last week: "IN with a subquery is the same IN operator you used with a hardcoded list like IN ('Dairy', 'Beverages'). The only difference is the list is now calculated dynamically by another query instead of typed in by you."
Q5: Members Who Never Scored Above 80
One word changes. IN becomes NOT IN. The result flips completely.
SELECT name, member_id, city
FROM study_group.members
WHERE member_id NOT IN (
SELECT member_id
FROM study_group.quiz_scores
WHERE score > 80
);
This is the cleanest demonstration of NOT IN with a subquery - you don't rewrite the inner query at all, you just negate the outer condition. The members table hasn't changed. The quiz scores haven't changed. One word, completely different answer.
The honest warning I give every time: NOT IN behaves incorrectly when the subquery returns any NULL values. If member_id is NULL for any quiz score row, NOT IN returns zero results - silently, with no error. For a clean dataset like this one it's fine. In production, be aware. NOT EXISTS is the NULL-safe alternative.
Q6: Members With Average Above 70 - Subquery in FROM
This is where subqueries start feeling like a different tool entirely.
"Which members have a personal quiz average above 70?"
You can't write WHERE avg_score > 70 directly - avg_score is an alias that doesn't exist until after SELECT runs. The solution: calculate the averages first, treat that result as a table, then filter it.
Step 1 - build the averages:
SELECT member_id, ROUND(AVG(score), 1) AS avg_score
FROM study_group.quiz_scores
GROUP BY member_id;
Subquery in FROM:
SELECT member_averages.member_id, avg_score
FROM (
SELECT member_id, ROUND(AVG(score), 1) AS avg_score
FROM study_group.quiz_scores
GROUP BY member_id
) AS member_averages
WHERE avg_score > 70;
The inner query builds the averages table. The outer query filters it. Notice: the inner result gets an alias - AS member_averages - because PostgreSQL requires every derived table in FROM to have a name.
I described it this way: "The subquery in FROM is a temporary table that only exists for the duration of this query. It has no name in the database, it doesn't persist, it can't be queried separately. It's built, used, and thrown away - all in one statement."
We also wrote the version that includes the member's name - which requires a JOIN inside the subquery:
SELECT member_id, name, avg_score
FROM (
SELECT m.member_id, name, ROUND(AVG(score), 1) AS avg_score
FROM study_group.quiz_scores q
INNER JOIN study_group.members m ON q.member_id = m.member_id
GROUP BY m.member_id, name
) AS member_averages
WHERE avg_score > 70;
A JOIN inside a subquery, filtered by the outer query. This is the point where the syntax gets dense but the logic stays clean - because we built it step by step.
Q7: Show the Group Average Alongside Every Row - Subquery in SELECT
"Show each quiz score alongside the overall group average, so you can see at a glance whether each score is above or below the mean."
SELECT
score_id,
member_id,
score,
(SELECT ROUND(AVG(score), 1) FROM study_group.quiz_scores) AS group_avg
FROM study_group.quiz_scores
ORDER BY score DESC;
score_id | member_id | score | group_avg
---------|-----------|-------|----------
12 | 5 | 95 | 73.6
8 | 4 | 92 | 73.6
3 | 5 | 91 | 73.6
...
16 | 7 | 45 | 73.6
The inner query returns one number - the group average - and that number gets stamped onto every row. The group average is the same on every row because it's calculated once and repeated.
"This is useful when you want to show a benchmark alongside individual values. A student's mark and the class average in the same row. A product's price and the category average. A daily sales figure and the monthly average. Same pattern every time - a single-value subquery in the SELECT list."
The rule: a subquery in SELECT must return exactly one row and one column. More than one row and PostgreSQL throws an error. This is a scalar subquery - it returns a scalar (single) value.
Q8: Members Whose Personal Average Beats the Group Average - Subquery in HAVING
The final position: HAVING. This is the cleanest example of why subqueries in HAVING exist.
"Which members have a personal average higher than the overall group average?"
You need two averages: each member's average (requires GROUP BY) and the group's overall average (a single number). You can't put the group average in WHERE because WHERE runs before grouping. HAVING runs after. And a subquery gives you the comparison value dynamically.
SELECT
member_id,
ROUND(AVG(score), 1) AS avg_score
FROM study_group.quiz_scores
GROUP BY member_id
HAVING AVG(score) > (
SELECT ROUND(AVG(score), 1) FROM study_group.quiz_scores
);
I wrote the order of operations on the board: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. "The subquery in HAVING runs at step 4, after grouping has already happened. By then each member has their own average. HAVING compares that average against the subquery result and keeps only the members who beat it."
The Three Positions - Summary
| Position | What the subquery does | Must return |
|---|---|---|
| WHERE / HAVING | A value to filter against | One value (scalar) or a list (for IN) |
| FROM | A temporary table the outer query treats as real | Any number of rows and columns (needs an alias) |
| SELECT | A single value added to every output row | Exactly one row, one column |
The Two-Step Pattern - The Most Important Thing
Every question today followed the same structure:
- Write two separate queries. Get both answers. Verify them.
- Take the result of Query 1 and replace it with a subquery inside Query 2.
This isn't just a teaching technique - it's how experienced SQL developers actually write subqueries. You don't start with the nested version. You build each piece, confirm it works, then combine. When a subquery breaks, you pull the inner query out, run it separately, check the result, and put it back. Debugging a subquery is just debugging two queries.
What I Noticed Teaching This Session
1. The two-step pattern removes the intimidation. Nobody finds a nested query readable the first time they see it. But everyone can follow two separate queries. Building the two steps first - then showing how they collapse into one - makes the final syntax feel like a natural conclusion rather than a magic trick.
What's Next: CTEs
Subqueries are powerful. But deeply nested subqueries can become hard to read. CTEs - Common Table Expressions - are the cleaner alternative for complex multi-step logic.
-- The same Q6 query, written as a CTE instead of a subquery
WITH member_averages AS (
SELECT m.member_id, name, ROUND(AVG(score), 1) AS avg_score
FROM study_group.quiz_scores q
JOIN study_group.members m ON q.member_id = m.member_id
GROUP BY m.member_id, name
)
SELECT member_id, name, avg_score
FROM member_averages
WHERE avg_score > 70;
Same result. Different structure. The intermediate table has a name, lives at the top, and the main query reads cleanly against it. Next session we'll rewrite today's subqueries as CTEs and see which approach fits which problem.
I'm a data trainer in Nairobi running a full data programme -
Python foundations → Data Science or Data Engineering specialisations.
Top comments (0)