Subqueries & CTE

Chapter 6 13 code blocks 3752 words

Chapter 2 made the point that a query returns a table. This chapter cashes that in: if a query returns a table, it can be used anywhere a table is expected — inside WHERE, inside FROM, even inside SELECT. That is all a subquery is.

This is also where messy queries become readable, because a CTE lets you name each step instead of nesting five levels of brackets.

What a subquery is — the three positions

A subquery (or inner query) is a SELECT written inside another statement, always in brackets. Where you put it determines what shape it must return.

Scalar subquery

A subquery that returns a single value can be used anywhere a value can be used.

SELECT
    title,
    price,
    (SELECT ROUND(AVG(price)) FROM courses)        AS avg_price,
    price - (SELECT AVG(price) FROM courses)       AS diff_from_avg
FROM courses
ORDER BY price DESC;

15 rows

1 ms

In WHERE it answers "compared to the average / the maximum":

SELECT title, price FROM courses
WHERE price > (SELECT AVG(price) FROM courses);
-- Machine Learning, Deep Learning

SELECT enrollment_id, grade FROM enrollments
WHERE grade = (SELECT MAX(grade) FROM enrollments);
-- enrollment 13, grade 100

Result 1

8 rows

Result 2

14 rows

1 ms

Derived table (subquery in FROM)

A subquery in FROM becomes a temporary table for that query. It must be given an alias. This is how you aggregate an aggregate — something a single GROUP BY cannot do.

-- "On average, how many enrollments does a course have?"
SELECT ROUND(AVG(cnt), 2) AS avg_enrollments_per_course
FROM (
    SELECT course_id, COUNT(*) AS cnt
    FROM enrollments
    GROUP BY course_id
) AS per_course;
-- 4.0   (counts are 5, 5, 3, 3, 4)

1 row

1 ms

The inner query collapses 20 enrollments into 5 rows; the outer query then averages those 5 numbers. Two levels of aggregation, two queries.

It also rescues the Chapter 2 problem of not being able to filter on an alias:

-- WHERE cannot see an alias — but it can see a column of a derived table
SELECT * FROM (
    SELECT title, ROUND(price * 1.0 / credits) AS per_credit
    FROM courses
) AS t
WHERE per_credit > 400000
ORDER BY per_credit DESC;
-- Deep Learning 600000, SQL for Analytics 500000,
-- Machine Learning 500000, Data Visualization 466667

3 rows

0 ms

Correlated subqueries An ordinary subquery runs once. A correlated subquery references a column from the outer query, so it is conceptually re-run for every outer row.

SELECT
    s.first_name,
    (SELECT COUNT(*)     FROM enrollments e WHERE e.student_id = s.student_id) AS courses,
    (SELECT ROUND(AVG(grade),1) FROM enrollments e WHERE e.student_id = s.student_id) AS avg_grade
FROM students s
ORDER BY courses DESC, s.first_name;

… first 15 of 300 rows

300 rows

35 ms

SELECT s.first_name, COUNT(e.enrollment_id) AS courses,
       ROUND(AVG(e.grade),1) AS avg_grade
FROM students s LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.first_name;

EXISTS and NOT EXISTS

EXISTS takes a subquery and returns TRUE if it produces at least one row. It does not care what the rows contain — which is why everyone writes SELECT 1 inside it.

-- Students with at least one active enrollment
SELECT s.first_name FROM students s
WHERE EXISTS (
    SELECT 1 FROM enrollments e
    WHERE e.student_id = s.student_id AND e.status = 'active'
);
-- Aziza, Dilnoza, Malika, Zarina, Ulugbek

-- Students who have never failed anything
SELECT s.first_name FROM students s
WHERE NOT EXISTS (
    SELECT 1 FROM enrollments e
    WHERE e.student_id = s.student_id AND e.status = 'failed'
);
-- everyone except Jasur and Sevara

Result 1

… first 15 of 238 rows

238 rows

Result 2

… first 15 of 160 rows

160 rows

22 ms

CTE — the WITH clause

A CTE (Common Table Expression) is a derived table that you name before the main query instead of burying it inside. Same result, dramatically better readability.

WITH student_stats AS (
    SELECT
        student_id,
        COUNT(*)      AS courses,
        AVG(grade)    AS avg_grade
    FROM enrollments
    WHERE grade IS NOT NULL
    GROUP BY student_id
)
SELECT
    s.first_name,
    st.courses,
    ROUND(st.avg_grade, 1) AS avg_grade
FROM student_stats st
JOIN students s ON s.student_id = st.student_id
WHERE st.avg_grade > (SELECT AVG(grade) FROM enrollments)
ORDER BY st.avg_grade DESC;

… first 15 of 147 rows

147 rows

1 ms

Chaining several CTEs

Separate multiple CTEs with commas. Each one can reference the ones defined before it, so a complicated report becomes a readable pipeline.

WITH graded AS (
    -- step 1: only enrollments that actually have a grade
    SELECT * FROM enrollments WHERE grade IS NOT NULL
),
course_avg AS (
    -- step 2: average per course
    SELECT course_id, COUNT(*) AS n, AVG(grade) AS avg_grade
    FROM graded GROUP BY course_id
),
overall AS (
    -- step 3: one number for the whole school
    SELECT AVG(grade) AS school_avg FROM graded
)
SELECT
    c.title,
    ca.n                                   AS graded,
    ROUND(ca.avg_grade, 1)                 AS course_avg,
    ROUND((SELECT school_avg FROM overall), 1) AS school_avg,
    CASE WHEN ca.avg_grade > (SELECT school_avg FROM overall)
         THEN 'above' ELSE 'below' END     AS vs_school
FROM course_avg ca
JOIN courses c ON c.course_id = ca.course_id
ORDER BY ca.avg_grade DESC;

14 rows

1 ms

Recursive CTE

A recursive CTE refers to itself. It has two parts joined by UNION ALL: an anchor (the starting rows) and a recursive step (how to get from one row to the next). It stops when the recursive step returns nothing.

WITH RECURSIVE numbers(n) AS (
    SELECT 1                       -- anchor: where we start
    UNION ALL
    SELECT n + 1 FROM numbers      -- recursive step
    WHERE n < 10                   -- STOP condition — never omit this
)
SELECT n FROM numbers;
-- 1 2 3 4 5 6 7 8 9 10

10 rows

0 ms

The genuinely useful analytics version is generating a complete date series, so that months with no data still appear in a report as zero:

WITH RECURSIVE months(m) AS (
    SELECT '2025-09'
    UNION ALL
    SELECT strftime('%Y-%m', DATE(m || '-01', '+1 month'))
    FROM months WHERE m < '2026-09'
)
SELECT
    months.m                       AS month,
    COUNT(e.enrollment_id)         AS enrollments
FROM months
LEFT JOIN enrollments e ON strftime('%Y-%m', e.enrolled_at) = months.m
GROUP BY months.m
ORDER BY months.m;

13 rows

6 ms

Now all 13 months appear — September 2025 with 6, February 2026 with 12, September 2026 with 2, and the ten empty months with 0. Without the generated series those months would be invisible, and a line chart of the result would be badly misleading.

The other classic use is walking a hierarchy — an employee's whole management chain, or a category tree of unknown depth.

Subquery vs JOIN vs CTE — which to use

-- 8 rows — students appear once per matching enrollment
SELECT s.first_name FROM students s
JOIN enrollments e ON s.student_id = e.student_id WHERE e.status = 'completed';

-- 8 distinct students — each appears exactly once
SELECT s.first_name FROM students s
WHERE EXISTS (SELECT 1 FROM enrollments e
              WHERE e.student_id = s.student_id AND e.status = 'completed');

Practice set

-- 1. Courses more expensive than average
SELECT title, price FROM courses
WHERE price > (SELECT AVG(price) FROM courses);

-- 2. The student(s) with the highest average grade
WITH avg_per_student AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM enrollments WHERE grade IS NOT NULL GROUP BY student_id
)
SELECT s.first_name, ROUND(a.avg_grade,1) AS avg_grade
FROM avg_per_student a JOIN students s USING (student_id)
WHERE a.avg_grade = (SELECT MAX(avg_grade) FROM avg_per_student);

-- 3. Students who take Machine Learning but not Python Basics
SELECT s.first_name FROM students s
WHERE EXISTS     (SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id AND e.course_id = 5)
  AND NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id AND e.course_id = 1);

-- 4. Average number of enrollments per course
SELECT ROUND(AVG(cnt),2) FROM (
    SELECT course_id, COUNT(*) AS cnt FROM enrollments GROUP BY course_id
) t;

-- 5. Each course with its share of all enrollments
WITH per_course AS (
    SELECT course_id, COUNT(*) AS n FROM enrollments GROUP BY course_id
)
SELECT c.title, p.n,
       ROUND(100.0 * p.n / (SELECT SUM(n) FROM per_course), 1) AS pct_of_all
FROM per_course p JOIN courses c USING (course_id)
ORDER BY pct_of_all DESC;

-- 6. Students who never failed
SELECT first_name FROM students s
WHERE NOT EXISTS (SELECT 1 FROM enrollments e
                  WHERE e.student_id = s.student_id AND e.status = 'failed');

-- 7. Numbers 1 to 12 (recursive)
WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM n WHERE x < 12)
SELECT x FROM n;

-- 8. Rewrite exercise: express #1 as a CTE instead of a subquery
WITH stats AS (SELECT AVG(price) AS avg_price FROM courses)
SELECT c.title, c.price FROM courses c, stats WHERE c.price > stats.avg_price;

Result 1

8 rows

Result 2

1 row

Result 3

… first 15 of 74 rows

74 rows

Result 4

1 row

Result 5

14 rows

Result 6

… first 15 of 160 rows

160 rows

Result 7

12 rows

Result 8

8 rows

34 ms

Interview questions

Q: What is a correlated subquery?

One that references a column from the outer query, so it must be evaluated per outer row and cannot run standalone. It is expressive but potentially O(n) executions; a JOIN + GROUP BY usually scales better.

Q: CTE vs subquery — is there a performance difference?

Usually none: most engines inline a CTE exactly like a derived table. The difference is readability and reuse. Two caveats: PostgreSQL before version 12 always materialised CTEs (an optimisation fence), and a CTE referenced several times may be computed several times unless the engine materialises it.

Q: When must you use a CTE rather than a subquery?

For recursion, and when the same intermediate result is needed more than once in the same query.

Q: What are the two parts of a recursive CTE?

An anchor member (the seed rows) and a recursive member that references the CTE, combined with UNION ALL, plus a condition that eventually makes the recursive member return no rows.

Q: IN vs EXISTS vs JOIN for "students who have at least one completed course"?

EXISTS is the best fit: it stops at the first match and returns each student once. A JOIN returns a student once per completed course (fan-out) and needs DISTINCT. IN is fine here but becomes dangerous the moment it is negated.

Q: Can a subquery in SELECT return multiple rows?

No — it must be scalar (one row, one column) or the query fails at runtime. Returning zero rows is allowed and yields NULL.

Q: How do you show months that have no data in a monthly report?

Generate the complete month series (a recursive CTE or a calendar table), then LEFT JOIN the data onto it and COUNT a column from the data side.

Summary

• A subquery is a SELECT inside another statement. Its position dictates its required shape: scalar in SELECT/=, a column of values for IN, a whole table in FROM.
• Scalar subqueries put an overall summary next to every row. Derived tables let you aggregate an aggregate and filter on an alias.
• Correlated subqueries reference the outer query and run per row — clear on small data, slow on large; prefer JOIN + GROUP BY at scale.
• EXISTS/NOT EXISTS answer "is there at least one?", stop early, never fan out, and are NULL-safe.
• CTEs (WITH) name each step. Chain them with commas to turn a monster query into a readable pipeline.
• Recursive CTEs (anchor + recursive step + stop condition) generate series and walk hierarchies — used in reporting to make empty periods appear as 0.
• JOIN duplicates rows on the many side; EXISTS does not. Reaching for DISTINCT after a join usually means you wanted EXISTS.

Next: Chapter 7 — window functions, which give you aggregate values without collapsing your rows. This is the single most valuable SQL skill in an analytics interview.