Subqueries

A subquery is just a query inside another query. You put it in parentheses, and SQL runs it first, then uses the result in the outer query. It is like asking a question within a question.

Subquery in WHERE

This is the most common place to use subqueries:

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

The subquery (SELECT AVG(score) FROM students) runs first and calculates the average. Then the outer query uses that value to filter. Two queries in one.

Subquery in SELECT

-- Show each student's score and how it compares to the average
SELECT 
  name, 
  score,
  (SELECT AVG(score) FROM students) AS overall_average,
  score - (SELECT AVG(score) FROM students) AS difference_from_avg
FROM students;

This runs the subquery for every row, but since it is the same value each time, the database is smart enough to calculate it once and reuse it.

Subquery with IN

-- Find students enrolled in Mathematics
SELECT name FROM students
WHERE id IN (
  SELECT student_id FROM enrollments
  WHERE course_id = (SELECT id FROM courses WHERE course_name = 'Mathematics')
);

This is a subquery inside a subquery. The innermost query finds the Mathematics course ID, the middle one finds all student IDs enrolled in it, and the outer query gets their names.

Subquery vs JOIN

Many things you can do with subqueries can also be done with JOINs. Which one you use is often a matter of preference and readability.

-- Same result with JOIN (usually faster for large datasets)
SELECT s.name
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id
WHERE c.course_name = 'Mathematics';

My rule of thumb: if a JOIN makes sense, use it. Subqueries are great when you need to do something that would be awkward or messy with a JOIN.