Subqueries

A subquery is a query inside another query. It is like a nested loop in programming — you solve a smaller problem first and use that result to solve the bigger one. Subqueries can appear in SELECT, FROM, WHERE, and HAVING clauses.

Subquery in WHERE

The most common use. Filter results based on another query:

-- Students enrolled in the 'Database Systems' course
SELECT first_name, last_name
FROM students
WHERE id IN (
  SELECT student_id
  FROM enrollments
  WHERE course_id = (
    SELECT id FROM courses WHERE name = 'Database Systems'
  )
);

This has two subqueries: the inner one finds the course ID, and the outer one finds students enrolled in that course. The IN operator checks if the student_id exists in the subquery result.

Subquery in FROM

Use a subquery as a temporary table:

SELECT
  avg_grades.student_name,
  avg_grades.avg_grade
FROM (
  SELECT
    s.first_name || ' ' || s.last_name AS student_name,
    AVG(CASE e.grade
      WHEN 'A' THEN 4
      WHEN 'B' THEN 3
      WHEN 'C' THEN 2
      WHEN 'D' THEN 1
      ELSE 0
    END) AS avg_grade
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  GROUP BY s.id, s.first_name, s.last_name
) AS avg_grades
WHERE avg_grades.avg_grade > 3.5;

The subquery in FROM creates a derived table with average grades. The outer query then filters for students with high GPAs. Note that derived tables need an alias.

Correlated Subqueries

A correlated subquery references the outer query. It runs once for each row in the outer query:

-- Students with above-average grades
SELECT first_name, last_name
FROM students s
WHERE (
  SELECT AVG(CASE grade
    WHEN 'A' THEN 4 WHEN 'B' THEN 3
    WHEN 'C' THEN 2 WHEN 'D' THEN 1 ELSE 0
  END)
  FROM enrollments e
  WHERE e.student_id = s.id
) > 3.0;

For each student, the subquery calculates their average grade and checks if it is above 3.0. The s.id in the subquery links it to the current row of the outer query.

EXISTS

Use EXISTS to check if a subquery returns any rows:

-- Students who are enrolled in at least one course
SELECT first_name, last_name
FROM students s
WHERE EXISTS (
  SELECT 1
  FROM enrollments e
  WHERE e.student_id = s.id
);

EXISTS is often faster than IN for large datasets because it can stop as soon as it finds the first match.

Subquery vs JOIN

Most subqueries can be rewritten as JOINs. Which is better depends on the situation:

• JOINs are usually faster because the database optimizer handles them better
• Subqueries are often more readable for simple existence checks
• Use EXISTS for "does this exist?" checks
• Use JOINs when you need data from both tables in the result