Learn SQL  /  Advanced Queries  /  Subqueries

Subqueries

A subquery is a query nested inside another query — used when you need the RESULT of one query as an input to another.

Subquery in WHERE

R Code
SELECT name, score
FROM students
WHERE score > (
  SELECT AVG(score) FROM students
);
Output
name  | score
Rafi  | 88
Meera | 92

The inner query computes the average score once; the outer query then keeps only students above that average.

Subquery with IN

R Code
SELECT name
FROM employees
WHERE dept_id IN (
  SELECT dept_id FROM departments WHERE dept_name = "Engineering"
);

Correlated Subquery

A correlated subquery references a column from the OUTER query, so it re-runs once per outer row rather than just once.

R Code
SELECT s1.name, s1.score, s1.class
FROM students s1
WHERE s1.score = (
  SELECT MAX(s2.score)
  FROM students s2
  WHERE s2.class = s1.class
);
Output
name  | score | class
Meera | 92    | A
Zayn  | 76    | B

This finds the top scorer WITHIN each class - a correlated subquery re-evaluates the inner MAX() separately for every class.

Subquery in FROM (Derived Table)

🚀 Advanced Example
A subquery can also stand in for an entire table in the FROM clause - useful for building on top of an already-aggregated result.
R Code
SELECT class, avg_score
FROM (
  SELECT class, AVG(score) AS avg_score
  FROM students
  GROUP BY class
) AS class_summary
WHERE avg_score > 75;
Output
class | avg_score
A     | 90.0

Key Points

  • A subquery is a complete query nested inside WHERE, FROM, or SELECT of another query.
  • A regular subquery runs once; a correlated subquery re-runs for every row of the outer query.
  • Subqueries in WHERE are often combined with IN, =, >, or EXISTS.
  • A subquery in FROM (a derived table) lets you query the result of an aggregation as if it were a real table.