Subqueries

Introduction

So far, every query you have written has stood on its own. But sometimes the answer to a question depends on the answer to another question first — "which students scored above the class average?" requires knowing the average before you can compare anything to it.

A subquery lets you nest one query inside another, so the inner query's result feeds directly into the outer query. This lesson covers subqueries in a WHERE clause, subqueries in a FROM clause, and the distinction between correlated and non-correlated subqueries.

What is a Subquery?

A subquery (or inner query) is a complete SELECT statement written inside another query, usually surrounded by parentheses. The database runs the subquery, and its result is used by the outer (main) query.

Subquery in a WHERE Clause

The most common use of a subquery is inside a WHERE clause, to compare each row against a value calculated by another query — such as an average, maximum, or a list of matching IDs.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    marks INT
);

INSERT INTO students VALUES
    (1, 'Alex', 82),
    (2, 'Priya', 91),
    (3, 'Sam', 58),
    (4, 'Riya', 70);

-- Find students who scored above the average
SELECT name, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);

+-------+-------+
| name  | marks |
+-------+-------+
| Alex  | 82    |
| Priya | 91    |
+-------+-------+

Here, the inner query (SELECT AVG(marks) FROM students) runs first and returns a single number — the average of all marks (75.25) — and the outer query then compares every row's marks against that number.

Subquery in a FROM Clause

A subquery can also appear in the FROM clause, where its result is treated as if it were a real, temporary table that the outer query can select from, filter, or join against. A subquery used this way must be given an alias.

-- Treat a subquery's result as a temporary table
SELECT high_scorers.name, high_scorers.marks
FROM (
    SELECT name, marks
    FROM students
    WHERE marks >= 70
) AS high_scorers
ORDER BY high_scorers.marks DESC;

+-------+-------+
| name  | marks |
+-------+-------+
| Priya | 91    |
| Alex  | 82    |
| Riya  | 70    |
+-------+-------+

Correlated vs Non-Correlated Subqueries

A non-correlated subquery runs completely on its own — it does not depend on the outer query at all, and MySQL can evaluate it just once. Both examples above are non-correlated: the average, and the filtered list, are each calculated independently of any specific outer row.

A correlated subquery, by contrast, references a column from the outer query, so it must be re-evaluated once for every row the outer query considers.

CREATE TABLE grades (
    grade_id INT PRIMARY KEY,
    grade_name VARCHAR(10)
);

ALTER TABLE students ADD COLUMN grade_id INT;
UPDATE students SET grade_id = 1 WHERE student_id IN (1, 3);
UPDATE students SET grade_id = 2 WHERE student_id IN (2, 4);
INSERT INTO grades VALUES (1, '10th'), (2, '11th');

-- Correlated subquery: references the outer query's s.grade_id
SELECT s.name, s.marks, s.grade_id
FROM students s
WHERE s.marks > (
    SELECT AVG(marks)
    FROM students AS s2
    WHERE s2.grade_id = s.grade_id
);

+-------+-------+----------+
| name  | marks | grade_id |
+-------+-------+----------+
| Alex  | 82    | 1        |
| Priya | 91    | 2        |
+-------+-------+----------+

This inner subquery cannot run just once, because s.grade_id changes for every row the outer query examines — it must recompute the average for each grade group separately, which is exactly what "correlated" means.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Subqueries let you answer a smaller question first and feed that answer into a bigger query, whether inside a WHERE clause for comparisons or inside a FROM clause to treat a result as a temporary table.

In this lesson, you learned how to write subqueries in both WHERE and FROM clauses, and the difference between correlated and non-correlated subqueries. Next, you will learn about Common Table Expressions, a cleaner way to write many of these same queries.