Lesson Description
A query inside another query.
Lesson 01
Lesson Description
A query inside another query.
Easy Project
Mini project: Turn this lesson into a real-world query artifact by writing one clean business report query and validating output quality.
Some questions can't be answered in one pass over a table. Take this one from the HR database: "Which employees earn more than the company average?" To answer it you need two things. First you need the average salary, and then you need to compare every employee against that number. You could run two queries, copy the average out of the first result and paste it into the second, but that number goes stale the moment someone gets a raise.
A subquery solves this. It is a complete SELECT statement wrapped in parentheses and placed inside another query. The inner query runs, produces a value (or a list, or a whole table), and the outer query uses that result as if you had typed it in by hand. You have already met one form of this in the Filtering course when you used EXISTS. This lesson gives you the full picture.
Every subquery returns a result set, and the shape of that result decides where you can put it.
| Shape | What it returns | Where it goes | Typical operators |
|---|---|---|---|
| Scalar | Exactly one row, one column | WHERE, SELECT, HAVING, anywhere a single value fits | =, >, <, arithmetic |
| List (one column) | Many rows, one column | WHERE ... IN (...) | IN, NOT IN, = ANY, > ALL |
| Table | Many rows, many columns | FROM (a derived table) or EXISTS | joins, EXISTS, NOT EXISTS |
-- Scalar: compare against a single value
SELECT ... FROM t WHERE col > (SELECT AVG(col) FROM t);
-- List: membership test
SELECT ... FROM t WHERE col IN (SELECT other_col FROM other_table WHERE ...);
-- Existence test
SELECT ... FROM t WHERE EXISTS (SELECT 1 FROM other_table o WHERE o.fk = t.id);
-- Derived table: a subquery that acts like a table
SELECT ... FROM (SELECT ... GROUP BY ...) AS alias WHERE ...;
The parentheses are mandatory. Inside them you can use anything a normal query allows: joins, GROUP BY, WHERE, even another subquery.
Back to the HR question. The inner query computes one number, and the outer query filters against it:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;
The company average is 8,325.00, so this returns 16 of the 40 employees. The top of the list is Steven King at 24,000, followed by Lex De Haan and Neena Kochhar at 17,000 each. Notice that you never had to look up 8,325 yourself. If salaries change tomorrow, the same query still gives the right answer.
A scalar subquery can sit anywhere a single value can, including the column list. In the store database, here is how far each product's price sits from the average product price:
SELECT name,
price,
ROUND(price - (SELECT AVG(price) FROM products), 2) AS diff_from_avg
FROM products
ORDER BY diff_from_avg DESC
LIMIT 5;
The average price across all 27 products is 80.99. The 4K Monitor 27" (349.00) comes out on top at 268.01 above average, then the Espresso Machine at 218.01 and Wireless Headphones at 119.00. The subquery here doesn't depend on the current row, so PostgreSQL computes it once and reuses the value for every row.
Now a question from the hospital database: "Which patients have missed an appointment?" The status 'no-show' lives in appointments, but names live in patients. An IN subquery bridges the two:
SELECT id, first_name, last_name, city
FROM patients
WHERE id IN (
SELECT patient_id
FROM appointments
WHERE status = 'no-show'
)
ORDER BY last_name;
This returns 2 rows: Karen Allen from Portland and Susan Thompson from Bellevue. The inner query produces a one-column list of patient IDs, and IN keeps every patient whose id appears in that list. A nice property of IN compared with a join: if a patient had two no-shows, they would still appear only once, because IN is a yes or no membership test. A plain join would return that patient twice.
You can nest subqueries, too. This counts appointments handled by doctors in the Cardiology department, looking up the department ID by name instead of hard-coding it:
SELECT COUNT(*) AS cardiology_appointments
FROM appointments
WHERE doctor_id IN (
SELECT id FROM doctors
WHERE department_id = (SELECT id FROM departments WHERE name = 'Cardiology')
);
It returns 14.
"Which doctors have no appointments at all?" Questions phrased as "has none" are where NOT EXISTS shines:
SELECT d.first_name, d.last_name, d.specialty
FROM doctors d
WHERE NOT EXISTS (
SELECT 1
FROM appointments a
WHERE a.doctor_id = d.id
);
One row comes back: Marcus Wright, an Emergency Physician. EXISTS only asks whether the inner query produces at least one row, so what you put in its SELECT list doesn't matter. SELECT 1 is the usual convention because it signals that intent. Notice that the inner query mentions d.id from the outer query. That makes it a correlated subquery, which gets its own lesson later in this course.
Sometimes you need to aggregate first and then filter or join the aggregated result. In HR, "Which departments have an average salary above 8,000, and how many people work in each?"
SELECT d.name, ds.avg_sal, ds.headcount
FROM (
SELECT department_id,
ROUND(AVG(salary), 2) AS avg_sal,
COUNT(*) AS headcount
FROM employees
GROUP BY department_id
) AS ds
JOIN departments d ON d.id = ds.department_id
WHERE ds.avg_sal > 8000
ORDER BY ds.avg_sal DESC;
This returns 4 rows: Executive (19,333.33 across 3 people), Sales (10,833.33 across 9), Marketing (9,500.00 across 2) and Finance (8,600.00 across 6). The subquery in FROM builds a small table with one row per department, and the outer query treats ds like any other table: it joins it, filters it and sorts it. You could have used HAVING for the filter alone, but the derived table lets you join department names onto the aggregated rows cleanly.
Think of an uncorrelated subquery (one that doesn't reference the outer query) as running first, producing its result, and then handing it to the outer query. That is a good mental model, and for scalar subqueries like example 1 it is close to what actually happens: PostgreSQL computes the average once.
For IN and EXISTS, the planner is smarter than the mental model. It usually rewrites them into a semi-join, which is a join that stops looking after the first match. That is why IN and EXISTS are typically just as fast as an equivalent join, and why you shouldn't avoid them for performance reasons on small or well-indexed tables.
Two rules about empty and NULL results are worth memorising:
NULL. Since salary > NULL is never true, your outer query quietly returns nothing.This is the classic trap. "Which employees are not anyone's manager?" looks like it should work like this:
SELECT first_name, last_name
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);
It returns 0 rows, even though only 9 of the 40 employees manage someone. The reason: Steven King has no manager, so the subquery list contains a NULL. id NOT IN (100, 101, ..., NULL) expands to id <> 100 AND id <> 101 AND ... AND id <> NULL, and id <> NULL is unknown, so the whole condition can never be true.
Fix it with NOT EXISTS, which isn't affected by NULLs:
SELECT e.first_name, e.last_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM employees r WHERE r.manager_id = e.id
);
This correctly returns 31 rows. Adding WHERE manager_id IS NOT NULL inside the NOT IN subquery also works, but NOT EXISTS is the habit worth building.
SELECT first_name
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE department_id = 90);
PostgreSQL stops with ERROR: more than one row returned by a subquery used as an expression, because department 90 has three employees. Decide what you actually mean: higher than the department's maximum (SELECT MAX(salary) ...), or higher than any one of them (salary > ANY (SELECT ...)).
SELECT first_name
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 110);
Department 110 (Accounting) has no employees, so MAX returns NULL and the query returns 0 rows without any error. When a result is suspiciously empty, run the subquery on its own first.
The subquery forms in this lesson are standard SQL and work the same way in MySQL, SQL Server and SQLite, including the NOT IN NULL trap. One small difference: PostgreSQL 16 and later let you omit the alias on a derived table (FROM (SELECT ...) with no AS ds). MySQL and SQL Server still require the alias, and older PostgreSQL versions do too, so always naming your derived tables keeps your SQL portable.
The exercises below this lesson ask you to:
NOT forms you trust here, and why.EXISTS.Run the inner query on its own first whenever you're unsure what it returns.
SELECT in parentheses, and its shape (single value, list or table) determines where you can use it.NULL; two or more rows is an error.IN for membership in a list and EXISTS for "at least one matching row". Both are planned as efficient semi-joins.NOT EXISTS over NOT IN whenever the subquery column could contain NULL.FROM is a derived table: aggregate first, then join or filter the result.A subquery is a query inside another query. It lets you compare rows to a computed value (employees earning above the average), filter by a list from another table, or treat an aggregated result as a table. It is the first step toward breaking a hard question into smaller ones.
Compare to an overall average
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;The inner query returns a single value, so it can be used wherever a number is expected.
A derived table in FROM
SELECT ROUND(AVG(order_total), 2) AS avg_order_value
FROM (
SELECT order_id, SUM(quantity * unit_price) AS order_total
FROM order_items
GROUP BY order_id
) t;The inner query builds one row per order; the outer query averages those totals. A derived table needs an alias (t).
(SELECT salary FROM employees WHERE department_id = 90) is used with > and the department has three people?An error: the subquery returns more than one row where a single value is expected. Use MAX, ANY or ALL depending on the question.
Concept check
3 quick questions. One at a time. Instant score at the end.
Return first_name, last_name, and salary of employees earning strictly more than the company average salary.
No results yet
Run a query to see results here
Return the name of every product that does NOT appear in any order_items row.
No results yet
Run a query to see results here
Return first_name and last_name of customers who have placed at least one order. Use EXISTS. Order by last_name.
No results yet
Run a query to see results here
From the store sandbox, return name, price, and category_id for products whose price is strictly above the average price in their own category. Order by category_id, then price descending.
No results yet
Run a query to see results here