More subquery examples: the employee database

The employee database

Same three tables as the join and aggregate example pages: DEPARTMENT, JOBS, and EMPLOYEES. Subqueries do not need anything extra from the schema, only a question that is easier to answer in two steps than in one.

The tables behind every query on this page.
Table	Rows	Relevant columns
DEPARTMENT	6	dept_id, dept_name, location
EMPLOYEES	24	emp_id, first_name, last_name, salary, dept_id

Build it yourself. Download employee_db.py, save it next to a notebook, then run from employee_db import create_database followed by connection, cursor = create_database(). Every number below comes straight out of that data, so you can check your own queries against it.

More Sections 4.10–4.11 examples

Subqueries, again, in every position

A subquery can sit in WHERE, in FROM, or in SELECT. These six examples visit all three, plus the ordinary-versus-correlated distinction and both directions of EXISTS.

A scalar subquery: above the company average

SELECT first_name, last_name, salary
FROM   EMPLOYEES
WHERE  salary > (SELECT AVG(salary) FROM EMPLOYEES)
ORDER BY salary DESC;
-- the inner query runs once, returns $77,708.33, and 10 employees clear that one fixed number

A correlated subquery: above your own department's average

Change the question slightly, above whose average, and the subquery has to look back at the outer row to answer it.

SELECT E.first_name, E.last_name, E.salary, E.dept_id
FROM   EMPLOYEES E
WHERE  E.salary > (SELECT AVG(salary) FROM EMPLOYEES WHERE dept_id = E.dept_id)
ORDER BY E.dept_id, E.salary DESC;
-- 9 employees now, not 10, and not the same 9 either: this recomputes the average once per department, not once for the whole company

Compare the two result sets directly: the plain subquery compares everyone to one company-wide number, $77,708.33. The correlated version compares each employee only to their own department's average, so someone can clear the company bar but miss their department's, or the reverse. E.dept_id inside the inner query is what makes it correlated; delete it and it goes back to running once.

IN: employees in Turlock departments

SELECT first_name, last_name
FROM   EMPLOYEES
WHERE  dept_id IN (SELECT dept_id FROM DEPARTMENT WHERE location = 'Turlock')
ORDER BY last_name;
-- 8 employees: everyone in Accounting or Human Resources, the two departments located in Turlock

EXISTS and NOT EXISTS: departments with nobody in them

SELECT dept_name
FROM   DEPARTMENT D
WHERE  NOT EXISTS (SELECT * FROM EMPLOYEES E WHERE E.dept_id = D.dept_id);
-- Marketing and Research: the two departments with nobody assigned

SELECT dept_name
FROM   DEPARTMENT D
WHERE  EXISTS (SELECT * FROM EMPLOYEES E WHERE E.dept_id = D.dept_id);
-- Accounting, Engineering, Sales, and Human Resources: every department that has at least one employee

This is the same question the JOIN examples page answers with a LEFT JOIN and a WHERE ... IS NULL filter. Both are correct; EXISTS states directly "no matching row anywhere," while the outer join version builds the full joined table first and then filters it. Worth writing both and confirming they return the same departments.

A subquery in FROM: department averages as a table

A subquery inside FROM produces a temporary, unnamed table for the outer query to read from, so it needs its own alias.

SELECT D.dept_name, dept_avg.avg_salary
FROM   DEPARTMENT D
       JOIN (SELECT dept_id, AVG(salary) AS avg_salary
             FROM   EMPLOYEES
             GROUP BY dept_id) dept_avg
       ON D.dept_id = dept_avg.dept_id
ORDER BY dept_avg.avg_salary DESC;
-- 4 rows, the same 4 departments and averages as the GROUP BY example on the aggregate examples page, arrived at by joining onto a subquery instead of grouping directly

A subquery in SELECT: compare every row to one number

SELECT first_name, last_name, salary,
       (SELECT AVG(salary) FROM EMPLOYEES) AS company_avg,
       salary - (SELECT AVG(salary) FROM EMPLOYEES) AS diff_from_avg
FROM   EMPLOYEES
ORDER BY diff_from_avg DESC;
-- 24 rows; the same fixed company average, $77,708.33, is recomputed for every single row and then subtracted from that row's own salary

A subquery in SELECT must return exactly one value, since it has to fit into one column of one row. That is also why it is the least efficient of the three positions here: the inner query above runs once per outer row rather than once overall, even though the answer never changes from row to row.