Lesson 13 of 20

Subqueries

A Query Inside a Query

A subquery is a complete SELECT statement written inside another statement and wrapped in parentheses. The inner query runs, produces a result, and the outer query uses that result exactly as though you had typed the value in yourself.

The motivating case is one you have already run into. "Which students scored above the class average?" cannot be written as one flat query, because WHERE marks > AVG(marks) is not allowed — the average does not exist yet while WHERE is being evaluated. A subquery splits the problem into two steps: work out the average first, then compare each row against it.

You could do the same thing as two separate statements — run the average, note the number, paste it into a second query — and that is precisely what the subquery saves you from. The value is recomputed on every execution, so the query stays correct as the data changes and no stale number ever gets hard-coded into your application.

A subquery returning a single value — one row, one column — is called a scalar subquery, and it may be used anywhere a single value is allowed. Knowing what shape a subquery returns is the key to knowing where you can put it, and it explains almost every error you will hit here. "Subquery returns more than 1 row" means you used a multi-row result in a place that expected exactly one value.

Example 
-- Two steps, done by hand
SELECT AVG(marks) FROM students;   -- suppose this returns 74.5
SELECT name, marks FROM students WHERE marks > 74.5;

-- The same thing as one query that can never go stale
SELECT name, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students)
ORDER BY marks DESC;

-- A scalar subquery fits anywhere a single value fits
SELECT
    name,
    marks,
    ROUND(marks - (SELECT AVG(marks) FROM students), 1) AS above_average_by
FROM students;

-- A multi-row result where one value was expected is an error
-- SELECT name FROM students WHERE marks > (SELECT marks FROM students);
Notes
  • Read a subquery from the inside out. Find the innermost parentheses, work out what that query returns on its own, then mentally replace it with its result and read the outer query. Two levels of nesting is normal and readable. Four levels is a sign the query wants rewriting with joins or common table expressions.

The Three Places a Subquery Can Live

In WHERE is the most common position. The subquery produces a value or a list, and the outer query filters against it. This is where "above the average", "in one of these branches" and "not in that list" get written.

In FROM, a subquery behaves as a temporary table for the life of the statement. It is called a derived table, and it exists so that you can query the result of a query — usually to filter on an aggregate you have just computed, or to group something that has already been grouped. Every derived table must be given an alias. Forget it and MySQL says "Every derived table must have its own alias", which is one of the most common first-time errors in this topic.

In SELECT, a scalar subquery adds one extra column. It reads nicely and it has a cost: the subquery is evaluated once for every row the outer query returns. Fifty rows means fifty executions. For a small result that is irrelevant; for a page of ten thousand rows it is the difference between a fast query and a timeout, and a LEFT JOIN with GROUP BY usually produces the same answer in a single pass.

Subqueries also work in HAVING, and in the SET clause of an UPDATE as an earlier lesson showed. The underlying rule is consistent: wherever SQL expects a value or a set of values, a subquery of the right shape can supply it.

Example 
-- In WHERE: filter against a computed value
SELECT name, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);

-- In FROM: a derived table, which MUST carry an alias
SELECT b.branch, b.students
FROM (
    SELECT branch, COUNT(*) AS students
    FROM students
    GROUP BY branch
) AS b
WHERE b.students >= 5
ORDER BY b.students DESC;

-- In SELECT: one extra column, evaluated once per outer row
SELECT
    s.name,
    (SELECT COUNT(*) FROM orders o WHERE o.student_id = s.id) AS orders
FROM students s;

-- Usually better: the same answer in one pass
SELECT s.name, COUNT(o.id) AS orders
FROM students s
LEFT JOIN orders o ON o.student_id = s.id
GROUP BY s.id, s.name;

-- In HAVING: compare each group against a table-wide figure
SELECT branch, ROUND(AVG(marks), 1) AS branch_average
FROM students
GROUP BY branch
HAVING AVG(marks) > (SELECT AVG(marks) FROM students);
Notes
  • The derived-table example is also the answer to "how do I use WHERE on an aggregate". You can use HAVING, or you can compute the aggregate inside a derived table and then filter it with an ordinary WHERE in the outer query. Both are correct, and the derived table is often easier to read once several conditions are involved.

IN, ANY and ALL: Comparing Against a List

When a subquery returns a column of several values, IN is the natural way to test against it. WHERE branch IN (SELECT ...) is true whenever the row's branch matches any value the subquery produced.

The NULL trap belongs here, in its home context. IN copes sensibly with NULLs in the list. NOT IN does not: one NULL anywhere in the list makes the condition impossible to satisfy, and the query returns zero rows without any error. Because subquery results very often contain NULLs — any nullable foreign key will do it — this is a practical risk rather than a theoretical one. Either exclude the NULLs explicitly inside the subquery, or use NOT EXISTS.

ANY and ALL extend the idea to the other comparison operators. > ANY (subquery) is true if the value beats at least one item in the list, which in effect compares it against the smallest. > ALL (subquery) is true only if it beats every item, which in effect compares it against the largest. = ANY is exactly IN, and SOME is a synonym for ANY that turns up in older code.

Most developers write the MAX or MIN version instead, because marks > (SELECT MAX(marks) FROM ...) states the intention more directly. One difference is worth knowing before you treat them as interchangeable: if the inner query returns no rows at all, > ALL is true, while > (SELECT MAX(...)) compares against NULL and is therefore unknown, so the row is dropped.

Example 
-- IN with a subquery
SELECT name, branch
FROM students
WHERE branch IN (
    SELECT branch FROM students GROUP BY branch HAVING COUNT(*) >= 5
);

-- NOT IN is unsafe whenever the subquery can produce a NULL
-- SELECT name FROM students
-- WHERE id NOT IN (SELECT student_id FROM orders);

-- Two safe versions of the same question
SELECT name FROM students
WHERE id NOT IN (
    SELECT student_id FROM orders WHERE student_id IS NOT NULL
);

SELECT name FROM students s
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.student_id = s.id);

-- ANY: beats at least one CSE student
SELECT name, marks
FROM students
WHERE marks > ANY (SELECT marks FROM students WHERE branch = 'CSE');

-- ALL: beats every CSE student
SELECT name, marks
FROM students
WHERE marks > ALL (SELECT marks FROM students WHERE branch = 'CSE');

-- Clearer, though not identical when the inner query returns nothing
SELECT name, marks
FROM students
WHERE marks > (SELECT MAX(marks) FROM students WHERE branch = 'CSE');
Notes
  • = ANY means the same as IN, and <> ALL means the same as NOT IN — inheriting the NULL problem along with it. Recognise the equivalences when you read other people's code, but write IN and NOT EXISTS in your own, because every reader understands those on the first pass.

Correlated Subqueries

Every inner query so far has stood on its own: you could copy it into a new tab and run it. A correlated subquery cannot. It refers to a column belonging to the outer query, so it has no meaning until the outer query hands it a row — and it is therefore re-evaluated once for every row the outer query considers.

The classic use is per-group comparison. "Which students scored above the average of their own branch?" is not one average; it is one average per branch, and each student has to be measured against theirs. The subquery contains WHERE x.branch = s.branch, where s is the outer query's alias, and that single reference is what makes it correlated.

The performance shape is different from an ordinary subquery. A plain subquery is computed once. A correlated one runs once per outer row, so a hundred students means a hundred executions of the inner query. Modern optimisers frequently rewrite the whole thing into a join or a hash aggregate, but you cannot count on it. When a correlated subquery is slow, the standard cure is to compute the inner result once in a derived table and join to it: the same answer, one pass instead of many.

Correlation also underlies EXISTS, which the next section covers, and the UPDATE statements from the earlier lesson that copy a value out of a related table. In each case, it is the outer row that gives the inner query something to talk about.

Example 
-- Correlated: each student compared against their OWN branch average
SELECT s.name, s.branch, s.marks
FROM students s
WHERE s.marks > (
    SELECT AVG(x.marks)
    FROM students x
    WHERE x.branch = s.branch      -- this reference makes it correlated
);

-- The same answer computed once rather than once per row
SELECT s.name, s.branch, s.marks, ROUND(b.branch_avg, 1) AS branch_avg
FROM students s
JOIN (
    SELECT branch, AVG(marks) AS branch_avg
    FROM students
    GROUP BY branch
) b ON b.branch = s.branch
WHERE s.marks > b.branch_avg;

-- A correlated subquery in SELECT: each student's latest order date
SELECT
    s.name,
    (SELECT MAX(o.ordered_at) FROM orders o WHERE o.student_id = s.id)
        AS last_order
FROM students s;
Notes
  • A quick way to tell the two apart: copy the inner query on its own into a new tab and run it. If it works, the subquery is independent and will be computed once. If it fails with "unknown column 's.branch'", it is correlated — and you now also know it will run once for every row of the outer query.

EXISTS, IN or JOIN?

EXISTS asks a yes-or-no question: does this subquery produce at least one row? It does not care what is in that row, only that there is one, which is why the convention is to write SELECT 1 inside it. SELECT * behaves identically — the database never fetches the columns — but SELECT 1 makes the intention obvious to whoever reads it next.

The practical difference from IN is that EXISTS can stop the moment it finds a match, while IN conceptually builds the entire list first. On a modern optimiser the two plans often converge, so treat that as a tiebreaker rather than a rule. The stronger reason to prefer EXISTS has nothing to do with speed: NOT EXISTS handles NULLs correctly and NOT IN does not.

A join is the third way to express the same relationship, and it differs in one important respect: a join can multiply rows and EXISTS cannot. If a student has three orders, joining to orders produces three rows for that student, so a query that only wanted the list of students who have ordered now needs DISTINCT to clean up after itself. WHERE EXISTS returns each student exactly once regardless of how many orders they placed.

The guidance follows directly. Use a JOIN when you need columns from the other table. Use EXISTS when you only need to know whether related rows exist. Reaching for SELECT DISTINCT to tidy up a join is usually a signal that EXISTS was the tool you actually wanted.

Example 
-- EXISTS: students who have ordered at least once, each listed once
SELECT s.id, s.name
FROM students s
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.student_id = s.id);

-- NOT EXISTS: students who have never ordered
SELECT s.id, s.name
FROM students s
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.student_id = s.id);

-- The join version repeats a student once per order
SELECT s.id, s.name
FROM students s
JOIN orders o ON o.student_id = s.id;

-- ...so it needs DISTINCT, which is the hint that EXISTS fitted better
SELECT DISTINCT s.id, s.name
FROM students s
JOIN orders o ON o.student_id = s.id;

-- When you genuinely need columns from the other table, join
SELECT s.name, o.id AS order_id, o.total_amount
FROM students s
JOIN orders o ON o.student_id = s.id;
Notes
  • EXISTS ignores its own SELECT list completely. SELECT 1, SELECT * and SELECT s.name all behave the same way and cost the same. Writing SELECT 1 is purely a signal to human readers that the columns do not matter here.

Derived Tables and Common Table Expressions

A subquery in FROM is the tool for querying a result set rather than a table. Group once and then filter or group again, rank a computed value, feed an aggregate into a join — anything you can do to a table, you can do to a derived table.

The two rules are that it must have an alias, and that it exists only for the duration of the statement. Nothing outside that query can see it, which is exactly why it does not clutter the database the way a temporary table would.

Once a query has two or three derived tables it becomes hard to read, because the pieces are nested inside each other and you have to read inwards to find where the work starts. A common table expression solves this. WITH name AS (SELECT ...) defines a named result at the top of the statement, and the main query then refers to it by name, so the whole thing reads top to bottom in the order you would explain it aloud. It is supported by MySQL 8.0 and later, PostgreSQL, SQL Server 2005 and later, Oracle, and SQLite 3.8.3 and later. MySQL 5.7 does not have it, which still matters on older college lab machines and on shared hosting.

A CTE and an equivalent derived table normally produce the same execution plan, so this is a readability tool rather than a performance one. That is not a small thing. A five-step report written as one deeply nested subquery cannot realistically be reviewed; the same report written as four named CTEs can be checked one step at a time. Name each step after what it produces — order_totals, big_branches — and the query largely documents itself.

Example 
-- A derived table: group, then filter the groups with an ordinary WHERE
SELECT t.student_id, t.orders, t.spent
FROM (
    SELECT student_id, COUNT(*) AS orders, SUM(total_amount) AS spent
    FROM orders
    WHERE status <> 'cancelled'
    GROUP BY student_id
) AS t
WHERE t.spent > 2000
ORDER BY t.spent DESC;

-- The same query as a CTE
-- (MySQL 8.0+, PostgreSQL, SQL Server, Oracle, SQLite 3.8.3+)
WITH order_totals AS (
    SELECT student_id, COUNT(*) AS orders, SUM(total_amount) AS spent
    FROM orders
    WHERE status <> 'cancelled'
    GROUP BY student_id
)
SELECT s.name, t.orders, t.spent
FROM order_totals t
JOIN students s ON s.id = t.student_id
WHERE t.spent > 2000
ORDER BY t.spent DESC;

-- Several named steps, read from the top down
WITH branch_totals AS (
    SELECT branch, AVG(marks) AS branch_avg, COUNT(*) AS students
    FROM students
    GROUP BY branch
),
big_branches AS (
    SELECT * FROM branch_totals WHERE students >= 5
)
SELECT branch, ROUND(branch_avg, 1) AS branch_avg, students
FROM big_branches
ORDER BY branch_avg DESC;
Notes
  • On MySQL 5.7 and earlier, rewrite any CTE as a derived table — the logic is identical and only the layout changes. Check what you are running with SELECT VERSION(); before designing a query around WITH, especially on shared hosting, where the installed MySQL is often several years behind the current release.
Ask AI