Subqueries & Nested Queries

What Is a Subquery?

A subquery (also called an inner query or nested query) is a complete SELECT statement enclosed in parentheses and used inside another SQL statement. The outer query receives the result of the inner query and uses it as a value or a set of values.

Subqueries can appear in three positions:

• WHERE clause — filter rows based on the subquery result
• FROM clause — treat the subquery as a derived (temporary) table
• SELECT list — compute a value for each row (correlated subquery)

Subquery in WHERE

The most common use — filter rows using a value or set produced by the inner query.

-- Students older than the average age
SELECT name, age FROM students
WHERE age > (SELECT AVG(age) FROM students);

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

-- Students NOT enrolled in any course
SELECT name FROM students
WHERE id NOT IN (SELECT DISTINCT student_id FROM enrollments);

The innermost subquery returns a single value (id); the middle subquery returns a list of student_id values; the outer query uses IN to filter against that list.

Subquery in FROM — Derived Table

When you put a subquery in the FROM clause it becomes a derived table — a temporary result set you can query just like a real table. You must give it an alias.

SELECT avg_data.grade, avg_data.avg_age
FROM (
  SELECT grade, AVG(age) AS avg_age
  FROM students
  GROUP BY grade
) AS avg_data
WHERE avg_data.avg_age > 16;

The inner query computes average ages per grade; the outer query filters only grades where the average exceeds 16. This is impossible without a subquery (or CTE) because WHERE runs before aggregation.

Correlated Subquery

A correlated subquery references a column from the outer query. It executes once per row of the outer query — making it powerful but potentially slower on large datasets.

-- For each student, count their enrollments
SELECT s.name,
  (SELECT COUNT(*) FROM enrollments e WHERE e.student_id = s.id) AS course_count
FROM students s;

Notice s.id — the inner subquery uses a column from the outer students alias s. For each student row, the database re-runs the inner query with that student's ID.

EXISTS vs IN

Both EXISTS and IN check whether rows satisfy a condition, but they work differently:

-- EXISTS: true if subquery returns any rows
SELECT name FROM students s
WHERE EXISTS (
  SELECT 1 FROM enrollments e WHERE e.student_id = s.id
);

-- IN: check against a list
SELECT name FROM students WHERE grade IN ('A', 'A+');
-- EXISTS is faster when subquery result is large