SQL · Quick Reference

Lesson 9 — Subqueries & Nested Queries Cheat Sheet

SQL

In one line: 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 th...

Key Ideas

1What 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 r...

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

3Subquery 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.

4Correlated 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.

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

Code Examples

-- 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 cours...

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;

-- 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;