Subqueries — Queries Inside Queries

10 min read

Subqueries

A subquery is a SELECT statement nested inside another SQL statement. Subqueries can appear in the WHERE clause, the FROM clause (as a derived table), or the SELECT list (as a scalar subquery).

sql

-- Students enrolled in course id 1 (using a subquery in WHERE)
SELECT name FROM students
WHERE id IN (
  SELECT student_id FROM enrolments WHERE course_id = 1
);

-- Courses with above-average price (scalar subquery)
SELECT title, price FROM courses
WHERE price > (SELECT AVG(price) FROM courses);

-- Derived table: per-course average score, then filter in outer query
SELECT course_id, avg_score
FROM (
  SELECT course_id, AVG(score) AS avg_score
  FROM enrolments
  WHERE score IS NOT NULL
  GROUP BY course_id
) AS course_stats
WHERE avg_score >= 70;

The CPCODELAB curriculum teaches subqueries alongside CTEs (Common Table Expressions) because both solve similar problems. Subqueries are more compact; CTEs are more readable for complex multi-step logic.

A correlated subquery references the outer query's columns. It runs once per row of the outer query, which can be slow on large tables. Prefer joins or window functions when performance matters.

Ready to test yourself?

Practice SQL— quiz & coding exercises