Subqueries

Objectives

By the end of this lesson, you should be able to:

• Use a subquery to produce a single value for a WHERE comparison
• Use a subquery inside a FROM clause as a temporary table
• Explain why a query is sometimes needed just to answer part of another query

💡 Why this matters: “Employees earning more than the company average” needs the average calculated first, then compared against, two steps that don’t fit in one flat WHERE clause. A subquery, a query nested inside another, is exactly this: compute something first, then use it.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from earlier modules.

A Subquery in WHERE

SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY first_name;

 first_name |  salary
------------+----------
 Erin       | 74000.00
 Priya      | 89000.00
 Taylor     | 95000.00

(SELECT AVG(salary) FROM employees) runs first, producing a single number, the company-wide average salary. The outer query then compares every employee’s salary against that one value. This inner query is the subquery, wrapped in parentheses, nested inside the outer query’s WHERE clause.

A Subquery in the SELECT List

SELECT first_name, salary, (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees
WHERE first_name = 'Erin';

 first_name |  salary  |    company_avg
------------+----------+--------------------
 Erin       | 74000.00 | 72250.000000000000

A subquery that returns a single value (a scalar subquery) can also appear directly in the SELECT list, computed once and shown alongside each row, useful for comparing an individual value against an overall figure in the same result.

A Subquery in FROM

SELECT dept_avg.department, dept_avg.avg_salary
FROM (
  SELECT department, ROUND(AVG(salary), 2) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_avg
WHERE dept_avg.avg_salary > 65000
ORDER BY dept_avg.department;

 department  | avg_salary
--------------+------------
 Engineering |   86000.00
 Marketing   |   69000.00

Here, the subquery (SELECT department, ROUND(AVG(salary), 2) AS avg_salary FROM employees GROUP BY department) produces an entire temporary result, department averages, that the outer query then treats as if it were a real table, aliased as dept_avg. This is useful when a WHERE or HAVING condition needs to apply after an aggregate has already been computed, in a way plain HAVING (Module 8) can’t express on its own.

Try It

1. Write a query for every employee earning less than the average salary, using a subquery.
2. Write a query showing each employee’s first_name, salary, and their department’s average salary side by side, using a subquery in the SELECT list (hint: this one needs to be correlated, a preview of the next lesson, try it and notice the challenge).
3. Write a query using a subquery in FROM that finds the single highest department average salary.
4. Explain, in your own words, why (SELECT AVG(salary) FROM employees) has to run before the outer query’s WHERE clause can be evaluated.

Recap

• A subquery is a query nested inside another, wrapped in parentheses, evaluated first.
• A scalar subquery (returning one value) can appear in WHERE for a comparison, or directly in the SELECT list.
• A subquery in FROM produces a temporary result treated as a table by the outer query, useful for filtering on an already-aggregated value.

Next lesson: correlated subqueries, where the inner query references the outer query’s current row.