Subqueries
Nest queries inside other queries to solve complex problems step by step.
Sometimes you need the result of one query before you can run another. For example, "find all students enrolled in the most popular course." You need to know which course is most popular first, then find the students. A subquery lets you do both in one statement.
Definition
A subquery is a query nested inside another query. The inner query is evaluated first, and its result is used by the outer query. Subqueries can appear in WHERE, FROM, and SELECT clauses.
Required step: If you do not have these tables yet, run this setup in the playground before continuing. It creates the tables used by every example in this stage.
CREATE DATABASE IF NOT EXISTS school;
USE school;
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100)
);
CREATE TABLE IF NOT EXISTS enrollments (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
course_name VARCHAR(100) NOT NULL,
score DECIMAL(5,2),
FOREIGN KEY (student_id) REFERENCES students(id)
);
INSERT INTO students (name, email) VALUES
('Alice', 'alice@school.edu'),
('Bob', 'bob@school.edu'),
('Charlie', 'charlie@school.edu');
INSERT INTO enrollments (student_id, course_name, score) VALUES
(1, 'Math', 90), (1, 'Science', 85), (2, 'Math', 78);Subqueries in WHERE
The most common use of subqueries is inside a WHERE clause. The subquery runs first, and its result is used to filter the outer query.
IN with a subquery
SELECT name FROM students
WHERE id IN (
SELECT student_id FROM enrollments
WHERE course_name = 'Math'
);Find students enrolled in Math. The inner query finds all student_ids in Math, and the outer query returns their names.
NOT IN with a subquery
SELECT name FROM students
WHERE id NOT IN (
SELECT student_id FROM enrollments
WHERE course_name = 'Math'
);Find students NOT enrolled in Math. The inner query returns student_ids in Math, and NOT IN excludes them.
Watch out: If the subquery returns any NULL values, NOT IN will return no results at all. This is a common gotcha. Use NOT EXISTS instead when NULLs are possible.
Scalar Subqueries
A scalar subquery returns a single value (one row, one column). You can use it anywhere a single value is expected: in SELECT, WHERE, or HAVING.
Scalar subquery in SELECT
SELECT name, score,
(SELECT AVG(score) FROM enrollments) AS average_score
FROM students
JOIN enrollments ON students.id = enrollments.student_id;The scalar subquery computes the overall average score. Every row in the outer query shows the same average alongside each student's score.
Scalar subquery in WHERE
SELECT name FROM students
WHERE id = (
SELECT student_id FROM enrollments
WHERE score = (SELECT MAX(score) FROM enrollments)
LIMIT 1
);Find the student with the highest score. The innermost query finds the max score, the middle query finds the student_id, and the outer query returns the name.
EXISTS and NOT EXISTS
EXISTS checks whether a subquery returns any rows. It is often faster than IN for correlated subqueries because it can stop as soon as it finds the first match.
EXISTS
SELECT name FROM students s
WHERE EXISTS (
SELECT 1 FROM enrollments e
WHERE e.student_id = s.id
AND e.course_name = 'Math'
);Find students enrolled in Math. The subquery references s.id from the outer query (this is a correlated subquery). EXISTS returns true as soon as a match is found.
NOT EXISTS
SELECT name FROM students s
WHERE NOT EXISTS (
SELECT 1 FROM enrollments e
WHERE e.student_id = s.id
);Find students with no enrollments at all. NOT EXISTS returns true when the subquery finds zero matches.
When to use each:IN vs EXISTS
IN is good when the subquery result set is small and static. It runs the subquery once and builds a list.
EXISTS is good when the subquery is correlated (references the outer query) and the outer table is large. It can short-circuit on the first match.
In practice, the MySQL optimizer often produces the same execution plan for both. Choose the one that reads more clearly.
ANY and SOME
ANY (or its synonym SOME) compares a value to each result from a subquery. The comparison is true if it holds for at least one value.
Greater than ANY
SELECT name FROM students
WHERE id > ANY (
SELECT student_id FROM enrollments
WHERE course_name = 'Math'
);Finds students whose id is greater than at least one Math student's id. If Math student ids are 2, 5, and 8, this matches ids 3, 4, 5, 6, 7, 8, and above.
Equals ANY
SELECT name FROM students
WHERE id = ANY (
SELECT student_id FROM enrollments
WHERE course_name = 'Math'
);Same as WHERE id IN (...). Returns students who are enrolled in Math. = ANY is equivalent to IN.
Note: ANY and SOME are synonyms. = ANY is equivalent to IN. <> ALL is equivalent to NOT IN.
Correlated Subqueries
A correlated subquery references a column from the outer query. It runs once for each row in the outer query. This is powerful but can be slow on large tables.
Correlated subquery in SELECT
SELECT s.name, (
SELECT MAX(e.score) FROM enrollments e
WHERE e.student_id = s.id
) AS best_score
FROM students s;For each student, the subquery finds their highest score. The subquery runs once per student row. This is correlated because it references s.id.
Correlated subquery with alias
SELECT s.name, s.id FROM students s
WHERE (
SELECT COUNT(*) FROM enrollments e
WHERE e.student_id = s.id
) > 1;Find students with more than one enrollment. The subquery counts enrollments for each student. Students with more than one are returned.
What is a correlated subquery?
What happens when NOT IN encounters a NULL in the subquery result?
Key Takeaways
- A subquery is a query nested inside another query.
- Scalar subqueries return a single value and can be used in SELECT or WHERE.
- EXISTS checks for row existence and often outperforms IN on large datasets.
- NOT IN fails with NULLs; use NOT EXISTS instead.
- Correlated subqueries reference the outer query and run once per outer row.
Ready to test your knowledge?
Take a Quiz