📚 Learning Objectives

By the end of this lesson, you will be able to: - Explain what a subquery is and where one can appear (SELECT list, FROM, WHERE, HAVING) - Write scalar, row, column and table subqueries - Choose between IN, EXISTS, ANY/SOME and ALL - Write correlated subqueries and understand how they are evaluated - Use derived tables (subqueries in FROM) - Avoid the classic NOT IN + NULL trap - Decide when a JOIN, a subquery, or a CTE (Lesson 18) is the clearer choice

🎯 Prerequisites

  • Completed Lessons 1-10
  • Comfortable with SELECT, WHERE, JOIN, GROUP BY and aggregate functions
  • The course database from setup_database.sql (departments, students, courses, enrollments)

📖 Table of Contents

  1. What Is a Subquery?
  2. Scalar Subqueries
  3. Column Subqueries: IN and NOT IN
  4. EXISTS and NOT EXISTS
  5. ANY, SOME and ALL
  6. Correlated Subqueries
  7. Derived Tables: Subqueries in FROM
  8. Subqueries in UPDATE and DELETE
  9. Subquery vs JOIN vs CTE
  10. Best Practices
  11. Common Pitfalls
  12. Exercises

1. What Is a Subquery?

A subquery (inner query, nested query) is a SELECT statement placed inside another SQL statement. The database evaluates the inner query and feeds its result to the outer query.

Subqueries are classified by the shape of the result they return:

Shape Returns Typical use
Scalar exactly one row, one column compare against a single value: WHERE gpa > (SELECT AVG(gpa) ...)
Column one column, many rows membership tests: IN, ANY, ALL
Row one row, many columns row comparisons: WHERE (a, b) = (SELECT x, y ...)
Table many rows, many columns derived tables in FROM, EXISTS

and by how they relate to the outer query:

  • Non-correlated: the inner query can run on its own; it is evaluated once.
  • Correlated: the inner query references a column of the outer query; conceptually it is re-evaluated for every outer row.

The teaching data set

The examples below show the result for this small subset of the course database so you can check your own output:

-- students
+------------+------------+------+----------+
| student_id | first_name | gpa  | status   |
+------------+------------+------+----------+
|          1 | Alice      | 3.90 | active   |
|          2 | Bob        | 3.40 | active   |
|          3 | Carol      | 3.70 | active   |
|          4 | Dan        | 2.90 | inactive |
|          5 | Eve        | 3.40 | active   |
|          6 | Frank      | NULL | active   |   -- no GPA yet, no enrollments
+------------+------------+------+----------+

-- courses: CS101 (3 cr, CS), MATH201 (4 cr, Math), CS201 (3 cr, CS), PHYS101 (4 cr, Physics)

-- enrollments (student, course, grade)
Alice CS101 92 | Bob CS101 85 | Eve CS101 85 | Carol CS101 78
Alice MATH201 88 | Carol MATH201 95 | Dan MATH201 70 | Bob MATH201 88
Alice CS201 NULL (still enrolled)

2. Scalar Subqueries

A scalar subquery returns a single value, so it can be used anywhere a value can: in WHERE, in the SELECT list, in HAVING, even as a default in an INSERT.

In WHERE: "better than average"

SELECT first_name, gpa
FROM students
WHERE gpa > (SELECT AVG(gpa) FROM students)
ORDER BY gpa DESC;
+------------+------+
| first_name | gpa  |
+------------+------+
| Alice      | 3.90 |
| Carol      | 3.70 |
+------------+------+

AVG(gpa) ignores NULL, so the average is (3.9 + 3.4 + 3.7 + 2.9 + 3.4) / 5 = 3.46, not divided by 6. Frank's NULL > 3.46 is unknown, so he is never returned by any comparison.

In the SELECT list: distance from the average

SELECT first_name,
       gpa,
       ROUND(gpa - (SELECT AVG(gpa) FROM students), 2) AS diff_from_avg
FROM students;
+------------+------+---------------+
| first_name | gpa  | diff_from_avg |
+------------+------+---------------+
| Alice      | 3.90 |          0.44 |
| Bob        | 3.40 |         -0.06 |
| Carol      | 3.70 |          0.24 |
| Dan        | 2.90 |         -0.56 |
| Eve        | 3.40 |         -0.06 |
| Frank      | NULL |          NULL |
+------------+------+---------------+

Rule: a scalar subquery must return at most one row

-- ERROR 1242 (21000): Subquery returns more than 1 row
SELECT first_name FROM students
WHERE gpa > (SELECT gpa FROM students WHERE status = 'active');

If the subquery returns zero rows, its value is NULL and the comparison is simply false for every row. Use IN/ANY when more than one value is possible.

3. Column Subqueries: IN and NOT IN

IN tests membership in the set of values the subquery returns.

Students who have taken any Computer Science course

SELECT first_name
FROM students
WHERE student_id IN (
    SELECT e.student_id
    FROM enrollments e
    JOIN courses c ON c.course_id = e.course_id
    WHERE c.course_code LIKE 'CS%'
)
ORDER BY first_name;
Alice, Bob, Carol, Eve

Note that Alice appears once even though she has two CS enrollments. The equivalent JOIN would return her twice unless you add DISTINCT — this de-duplication is one practical reason to prefer IN/EXISTS for "is there at least one" questions.

The NOT IN trap: a single NULL empties the result

-- Intention: GPAs that are not equal to any inactive student's GPA
SELECT first_name
FROM students
WHERE gpa NOT IN (SELECT gpa FROM students WHERE status = 'inactive' OR gpa IS NULL);
-- Empty set!

x NOT IN (2.9, NULL) expands to x <> 2.9 AND x <> NULL. x <> NULL is unknown, so the whole condition is never true. Any NULL in the subquery's result makes NOT IN return nothing.

Fix it by excluding NULLs, or use NOT EXISTS, which has no such problem:

SELECT first_name
FROM students
WHERE gpa NOT IN (SELECT gpa FROM students WHERE status = 'inactive' AND gpa IS NOT NULL)
ORDER BY first_name;
-- Alice, Bob, Carol, Eve

4. EXISTS and NOT EXISTS

EXISTS (subquery) is true when the subquery returns at least one row. The columns selected do not matter — SELECT 1 or SELECT * are both conventional — and MySQL stops as soon as the first matching row is found.

Students with no enrollments at all

SELECT s.first_name
FROM students s
WHERE NOT EXISTS (
    SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id
);
Frank

This is an anti-join: "rows in A with no partner in B". The three standard ways to write one:

-- 1. NOT EXISTS (NULL-safe, usually the clearest)
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id)

-- 2. NOT IN (only safe if e.student_id can never be NULL)
WHERE s.student_id NOT IN (SELECT student_id FROM enrollments)

-- 3. LEFT JOIN ... IS NULL
FROM students s LEFT JOIN enrollments e ON e.student_id = s.student_id
WHERE e.enrollment_id IS NULL

Courses that have at least one completed enrollment

SELECT c.course_code
FROM courses c
WHERE EXISTS (
    SELECT 1 FROM enrollments e
    WHERE e.course_id = c.course_id AND e.status = 'completed'
)
ORDER BY c.course_code;
-- CS101, MATH201

CS201 is excluded because its only enrollment is still enrolled; PHYS101 has no enrollments.

5. ANY, SOME and ALL

These compare a value against every value of a column subquery using an operator.

Expression True when
x > ANY (sub) x is greater than at least one value (i.e. x > MIN)
x > ALL (sub) x is greater than every value (i.e. x > MAX)
x = ANY (sub) same as x IN (sub)
x <> ALL (sub) same as x NOT IN (sub) — and has the same NULL trap

SOME is a synonym for ANY.

-- Students whose GPA is at least as high as every active student's GPA
SELECT first_name, gpa
FROM students
WHERE gpa >= ALL (SELECT gpa FROM students WHERE status = 'active' AND gpa IS NOT NULL);
-- Alice 3.90

-- Students with a GPA higher than at least one inactive student
SELECT first_name, gpa
FROM students
WHERE gpa > ANY (SELECT gpa FROM students WHERE status = 'inactive')
ORDER BY gpa DESC;
-- Alice 3.90, Carol 3.70, Bob 3.40, Eve 3.40

Most ANY/ALL queries can be rewritten with MIN/MAX, which many people find easier to read:

WHERE gpa >= (SELECT MAX(gpa) FROM students WHERE status = 'active')

Remember that x > ALL (empty set) is true and x > ANY (empty set) is false.

6. Correlated Subqueries

A correlated subquery refers to a column from the outer query, so its result depends on the current outer row.

Enrollments whose grade beats the course average

SELECT s.first_name, c.course_code, e.grade
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses  c ON c.course_id  = e.course_id
WHERE e.grade > (
    SELECT AVG(e2.grade)
    FROM enrollments e2
    WHERE e2.course_id = e.course_id        -- correlation: refers to outer e
)
ORDER BY c.course_code, e.grade DESC;
+------------+-------------+-------+
| first_name | course_code | grade |
+------------+-------------+-------+
| Alice      | CS101       | 92.00 |   -- CS101 average 85.00
| Carol      | MATH201     | 95.00 |   -- MATH201 average 85.25
| Alice      | MATH201     | 88.00 |
| Bob        | MATH201     | 88.00 |
+------------+-------------+-------+

Read it as: for each enrollment row, compute the average of the same course, then compare. Conceptually the inner query runs once per outer row; the optimizer may cache or rewrite it, but you should write correlated subqueries as if they are expensive and make sure the correlated column (e2.course_id) is indexed.

"Latest row per group" with a correlated MAX

SELECT s.first_name, e.enrollment_date, c.course_code
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses  c ON c.course_id  = e.course_id
WHERE e.enrollment_date = (
    SELECT MAX(e2.enrollment_date)
    FROM enrollments e2
    WHERE e2.student_id = e.student_id
)
ORDER BY s.first_name;
Alice 2024-03-10 CS201 | Bob 2024-03-03 MATH201 | Carol 2024-02-07 MATH201 |
Dan 2024-03-01 MATH201 | Eve 2024-01-15 CS101

Lesson 18 shows the window-function version (ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY enrollment_date DESC)), which also handles ties cleanly.

Correlated subqueries in the SELECT list

SELECT d.dept_name,
       (SELECT COUNT(*) FROM courses c WHERE c.dept_id = d.dept_id) AS n_courses
FROM departments d
ORDER BY n_courses DESC, d.dept_name;
-- Computer Science 2, Mathematics 1, Physics 1

This is easy to read for one or two columns; for several aggregates per department a LEFT JOIN ... GROUP BY or a derived table (next section) does the work in one pass.

7. Derived Tables: Subqueries in FROM

A subquery in FROM acts as a temporary table for the duration of the query. In MySQL it must have an alias.

Join each course to its own statistics

SELECT c.course_code, ca.avg_grade, ca.graded
FROM (
    SELECT course_id,
           ROUND(AVG(grade), 2) AS avg_grade,
           COUNT(grade)         AS graded
    FROM enrollments
    GROUP BY course_id
) AS ca
JOIN courses c ON c.course_id = ca.course_id
ORDER BY ca.avg_grade DESC;
+-------------+-----------+--------+
| course_code | avg_grade | graded |
+-------------+-----------+--------+
| MATH201     |     85.25 |      4 |
| CS101       |     85.00 |      4 |
| CS201       |      NULL |      0 |   -- COUNT(grade) skips the NULL grade
+-------------+-----------+--------+

Derived tables are the standard way to aggregate first, then join or filter, and to apply a second level of aggregation ("average of the per-course averages"):

SELECT ROUND(AVG(avg_grade), 2) AS avg_of_course_avgs
FROM (SELECT AVG(grade) AS avg_grade FROM enrollments GROUP BY course_id) AS t;

When the same derived table is needed twice, or the nesting gets deep, move it into a CTE (WITH ... AS (...)) — see Lesson 18.

8. Subqueries in UPDATE and DELETE

-- Mark students with no enrollments as inactive
UPDATE students s
SET s.status = 'inactive'
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id);

-- Remove dropped enrollments in courses that no longer belong to any department
DELETE FROM enrollments
WHERE status = 'dropped'
  AND course_id IN (SELECT course_id FROM courses WHERE dept_id IS NULL);

MySQL restriction (error 1093): you cannot modify a table and select from the same table in a plain subquery:

-- ERROR 1093: You can't specify target table 'students' for update in FROM clause
UPDATE students SET gpa = 4.0
WHERE gpa = (SELECT MAX(gpa) FROM students);

-- Work-around: wrap it in a derived table so MySQL materializes it first
UPDATE students SET gpa = 4.0
WHERE gpa = (SELECT m FROM (SELECT MAX(gpa) AS m FROM students) AS t);

Wrap multi-statement changes in a transaction (Lesson 18) so a mistake can be rolled back.

9. Subquery vs JOIN vs CTE

You want to… Prefer
Return columns from both tables JOIN
Test "at least one related row exists" EXISTS / IN (no duplicates to clean up)
Test "no related row exists" NOT EXISTS (NULL-safe)
Compare against an aggregate of the whole table scalar subquery
Compare against an aggregate of the same group correlated subquery or window function
Aggregate first, then join derived table or CTE
Reuse the same intermediate result twice, or recurse CTE (Lesson 18)

MySQL 8's optimizer transforms many IN subqueries into semi-joins, so performance between IN, EXISTS and JOIN is usually similar — choose the form that states the question most directly, then check EXPLAIN (Lesson 15) if it is slow.

10. Best Practices

  1. Alias every table and qualify every column in correlated subqueries (e2.course_id = e.course_id). An unqualified column silently binds to the wrong table.
  2. Give derived tables a meaningful alias (AS course_avgs, not AS t) when the query is longer than a few lines.
  3. Index the correlated column — the inner query runs per outer row in the worst case.
  4. Use NOT EXISTS instead of NOT IN unless the subquery column is NOT NULL.
  5. Keep scalar subqueries scalar: add LIMIT 1 only if any one row is genuinely acceptable; otherwise fix the logic.
  6. Prefer CTEs for nesting deeper than two levels — the same logic, readable top to bottom.

11. Common Pitfalls

Pitfall What happens Fix
NOT IN with a NULL in the list empty result NOT EXISTS, or WHERE col IS NOT NULL in the subquery
Scalar subquery returns many rows error 1242 use IN/ANY, or aggregate
Missing alias on derived table error 1248 "Every derived table must have its own alias" add AS name
Modifying and reading the same table error 1093 wrap the subquery in a derived table
Correlated subquery in SELECT list for many columns one inner query per column per row LEFT JOIN + GROUP BY, or a derived table
Using JOIN for an existence test duplicate outer rows EXISTS, or SELECT DISTINCT
Comparing to AVG() and expecting NULL rows to count NULLs are ignored by aggregates use COALESCE(gpa, 0) only if that is really the intended meaning

12. Exercises

Use the full course database (setup_database.sql) or the teaching subset above.

  1. List the courses whose credit value is above the average credit value of all courses.
  2. List the students who have never completed a course (status 'completed'). Write it once with NOT EXISTS and once with LEFT JOIN ... IS NULL.
  3. For each department, show the department name and the number of enrollments in its courses, using a correlated subquery in the SELECT list.
  4. Using a derived table, find the course with the highest average grade.
  5. Explain why the following returns no rows on the teaching subset, and rewrite it correctly: 
    SELECT first_name FROM students
WHERE student_id NOT IN (SELECT student_id FROM enrollments WHERE grade IS NULL OR grade < 80);
    (Hint: inspect the subquery's result set on its own first.)
  6. Rewrite exercise 1 using > ALL or > ANY, whichever is correct, and explain the difference.

Solutions

1. 

SELECT course_code, credits FROM courses
WHERE credits > (SELECT AVG(credits) FROM courses);
-- teaching subset: average credits = 3.5 → MATH201 (4), PHYS101 (4)
2. 
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 = 'completed');

SELECT s.first_name FROM students s
LEFT JOIN enrollments e ON e.student_id = s.student_id AND e.status = 'completed'
WHERE e.enrollment_id IS NULL;
-- teaching subset: Frank (the join condition must include the status filter;
-- putting it in WHERE would turn the LEFT JOIN back into an inner join)
3. 
SELECT d.dept_name,
       (SELECT COUNT(*) FROM enrollments e
        JOIN courses c ON c.course_id = e.course_id
        WHERE c.dept_id = d.dept_id) AS n_enrollments
FROM departments d;
-- teaching subset: Computer Science 5, Mathematics 4, Physics 0
4. 
SELECT c.course_code, t.avg_grade
FROM (SELECT course_id, AVG(grade) AS avg_grade FROM enrollments GROUP BY course_id) AS t
JOIN courses c ON c.course_id = t.course_id
ORDER BY t.avg_grade DESC
LIMIT 1;
-- MATH201 85.25
5. The subquery returns the student IDs of enrollments with grade IS NULL OR grade < 80 — that is {1 (Alice, NULL grade in CS201), 3 (Carol, 78), 4 (Dan, 70)}. It contains no NULL itself, so the result is actually not empty: it returns Bob, Eve and Frank. The trap would appear if student_id could be NULL. The lesson is to always run the subquery alone first; the NULL-safe form is: 
SELECT s.first_name FROM students s
WHERE NOT EXISTS (SELECT 1 FROM enrollments e
                  WHERE e.student_id = s.student_id
                    AND (e.grade IS NULL OR e.grade < 80));
-- Bob, Eve, Frank
6. credits > ALL (SELECT AVG(credits) FROM courses) is equivalent to exercise 1 because the subquery returns one value. > ANY would also be equivalent here for the same reason; the two differ only when the subquery returns several values (> ANY ≈ greater than the minimum, > ALL ≈ greater than the maximum).

🎯 Summary

  • A subquery is a SELECT inside another statement; it can return a scalar, a column, a row or a table.
  • IN/EXISTS answer "is there at least one", NOT EXISTS is the NULL-safe way to ask "is there none".
  • Correlated subqueries are evaluated per outer row — index the correlated column.
  • Derived tables aggregate first and join second; CTEs (Lesson 18) do the same more readably and add recursion.
  • Watch for the NOT IN + NULL trap and MySQL's error 1093 when modifying the table you read from.

🔄 Next Steps

  • Lesson 12: Views and Indexes — turn a frequently used subquery into a view
  • Lesson 15: Performance Optimization — read EXPLAIN output for subqueries and semi-joins
  • Lesson 18: Window Functions, CTEs and Transactions — the modern alternatives to deeply nested subqueries

📚 Additional Resources