SQL Subqueries Explained: Queries Inside Queries

A subquery is a query written inside another query, in parentheses. The inner query produces a value or a set of rows, and the outer query uses it. Subqueries let you answer questions in steps: first find the average salary, then find who earns more than it.

The example table#

Three shapes of subquery#

1. Scalar subqueries: one value#

Who earns more than the average?

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

name  | salary
------+-------
Ravi  | 78000
Meera | 81000

The database runs the inner query first (it returns 63,600), then uses that value in the outer WHERE. This is how you get around the rule that aggregates are not allowed directly in WHERE.

A scalar subquery can also be a column:

SELECT name,
       salary,
       salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees
ORDER BY diff_from_avg DESC;

name  | salary | diff_from_avg
------+--------+--------------
Meera | 81000  | 17400
Ravi  | 78000  | 14400
Priya | 61000  | -2600
Asha  | 52000  | -11600
John  | 46000  | -17600

2. List subqueries with IN#

Customers who placed an order worth more than 200:

SELECT name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
  WHERE amount > 200
);

name
----
Asha
Ravi

The inner query returns a list of ids (1, 2), and IN checks each customer against it. See IN for the important NOT IN + NULL trap.

3. Subqueries in FROM: derived tables#

A subquery in FROM acts like a temporary table. It must have an alias:

SELECT department, avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept
WHERE avg_salary > 50000
ORDER BY avg_salary DESC;

department  | avg_salary
------------+-----------
Engineering | 79500
Sales       | 56500

This lets you filter on avg_salary with a plain WHERE, because by the time the outer query runs, the average is just a column. Derived tables are also the standard fix for the double-counting trap: aggregate first, then join.

SELECT c.name, t.total
FROM customers c
JOIN (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) t ON t.customer_id = c.customer_id
ORDER BY t.total DESC;

name | total
-----+------
Ravi | 560
Asha | 370

4. Correlated subqueries#

A correlated subquery refers to a column from the outer query, so it is evaluated once for each outer row. The highest-paid employee in each department:

SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
)
ORDER BY e.department;

name  | department  | salary
------+-------------+-------
Meera | Engineering | 81000
John  | Marketing   | 46000
Priya | Sales       | 61000

For each employee e, the inner query finds the maximum salary in that employee's department. Read it as: "keep this employee if nobody in their department earns more".

A correlated subquery can also count related rows for each row:

SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders
FROM customers c
ORDER BY orders DESC, c.name;

name  | orders
------+-------
Asha  | 2
Ravi  | 1
John  | 0
Meera | 0

Subquery, JOIN or CTE?#

Many problems can be solved all three ways. Some guidance:

• JOIN when you need columns from both tables in the result.
• Subquery with IN / EXISTS when the other table is only used as a filter.
• CTE when a query has several steps, or uses the same subquery twice; it gives each step a name.

Modern databases usually optimise all three into similar plans. Choose the one that is easiest to read.

Common mistakes#

Practice#

SELECT name, salary
FROM employees
WHERE salary > (
  SELECT MAX(salary)
  FROM employees
  WHERE department = 'Sales'
);

name  | salary
------+-------
Ravi  | 78000
Meera | 81000

Key takeaway#