Subqueries

18 min

Explanation

A subquery is a SELECT nested inside another query — useful whenever a filter depends on an aggregate computed over the WHOLE table, something WHERE can't do directly (recall from the last module: WHERE can't see aggregates, only HAVING can, and HAVING only works after GROUP BY).

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The subquery (SELECT AVG(salary) FROM employees) runs once, produces a single number, and the outer query compares every row's salary against it — this is called a scalar subquery since it returns exactly one value.

Try it

A subquery can also return multiple rows — combine it with IN instead of = to check membership against the whole result set.

Loading editor…

Explanation

A correlated subquery references a column from the OUTER query, so it conceptually runs once per outer row instead of once total:

SELECT name
FROM employees e1
WHERE salary = (
  SELECT MAX(salary) FROM employees e2
  WHERE e2.department = e1.department   -- references the outer row's department
);

Here, e1 (the outer query's alias) and e2 (the subquery's own alias) both refer to the same employees table — aliasing lets you distinguish "the row I'm currently checking" from "every row in this department" even though it's the same table on both sides.

Exercise

The `employees` table has columns `id, name, department, salary`. Write a query that returns the `name` and `salary` of employees earning more than the company-wide average salary, ordered by salary descending. Use a subquery to compute the average — don't hardcode it.

Exercise

Write a query that returns the `name` of every employee who is the highest earner in their OWN department, ordered by name. Use a correlated subquery.

Quiz

What makes a subquery 'correlated'?

Checkpoint

You can write a scalar subquery to filter against an aggregate, and a correlated subquery to compare each row against a value computed relative to that row.