Subqueries

A subquery is a SELECT inside another statement, wrapped in brackets. It runs first, and its result feeds the outer query.

Scalar subqueries — one value

The simplest kind returns exactly one row and one column, so it can be used anywhere a single value can:

SQL▶ Run
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

"Everyone paid above average" cannot be written without this. WHERE salary > AVG(salary) is an error — WHERE cannot contain an aggregate — so the average is computed by its own query first.

A scalar subquery works in the SELECT list too:

SQL▶ Run
SELECT first_name,
       salary,
       (SELECT ROUND(AVG(salary), 2) FROM employees) AS company_average,
       ROUND(salary - (SELECT AVG(salary) FROM employees), 2) AS above_average_by
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;

If a "scalar" subquery returns more than one row you get a runtime error — and usually on production data rather than on your test data, because the query that returns one row today returns two tomorrow.

Subqueries that return a list

IN accepts a subquery instead of a literal list:

SQL▶ Run
SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'shipped')
ORDER BY name;

The inner query yields customer ids; the outer keeps customers whose id is among them. The subquery must return exactly one column — IN (SELECT * FROM …) is an error unless the table has a single column.

The NOT IN trap, on real data

Lesson 13 warned that NOT IN collapses when the list contains a NULL. Here is that failure with a subquery, which is where it actually happens to people:

SQL▶ Run
SELECT COUNT(*) AS departments_with_no_staff
FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees);

Zero. There is a department with no employees, and this query cannot find it — because one employee has a NULL dept_id, that NULL enters the list, and every comparison against it is unknown, so no row can qualify.

Two working versions. Exclude the NULLs explicitly:

SQL▶ Run
SELECT name AS department_with_no_staff
FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees WHERE dept_id IS NOT NULL);

Or use NOT EXISTS, which has no such problem and is the habit worth building — the next lesson is about it:

SQL▶ Run
SELECT d.name AS department_with_no_staff
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

Both find the empty department. NOT IN over a nullable column is a bug waiting for the data to trigger it, and it will not error when it does.

Comparison with ANY and ALL

> ANY (subquery) means greater than at least one; > ALL means greater than every one. They are rarely used because the common cases read better as > (SELECT MAX(…)) or > (SELECT MIN(…)), but they turn up in older code:

SELECT first_name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 3)
ORDER BY salary DESC;

That block has no Run button because SQLite does not implement ANY and ALL with a subquery, and the playground here is SQLite. PostgreSQL, MySQL, SQL Server and Oracle all do. The portable rewrite runs anywhere:

SQL▶ Run
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE dept_id = 3)
ORDER BY salary DESC;

Careful with both forms: ALL over a set containing NULL has the same unknown-comparison problem as NOT IN — and note that MAX ignoring NULLs is precisely why the rewrite behaves differently from > ALL when the set has one.

Subqueries in FROM — derived tables

A subquery in FROM behaves like a temporary table, and this is how you aggregate twice or join to a summary:

SQL▶ Run
SELECT d.name AS department, t.payroll
FROM departments d
JOIN (
  SELECT dept_id, ROUND(SUM(salary), 2) AS payroll
  FROM employees
  GROUP BY dept_id
) t ON t.dept_id = d.dept_id
ORDER BY t.payroll DESC;

The inner query collapses employees to one row per department, so the join is one-to-one and the fan-out from lesson 22 cannot happen. This is the standard fix for an inflated total: aggregate first, then join.

A derived table must be aliased (t here). PostgreSQL and SQL Server error without it.

Where a subquery can go

A note on performance

A subquery is not automatically slow. Query planners routinely rewrite IN (SELECT …) into a join and evaluate an uncorrelated subquery once. What is often slow is a correlated subquery — one that re-runs per row — which is the next lesson.

Where subqueries reliably cost you is readability: three levels of nesting is hard to follow and harder to debug, because you cannot run the middle of it on its own. That is what CTEs fix, two lessons from now.

Common mistakes

• NOT IN over a nullable column — returns nothing, silently.
• A scalar subquery that returns two rows — a runtime error, usually in production.
• Multiple columns where one is expected — IN (SELECT a, b …).
• An unaliased derived table — an error on most databases.

Interview question

Find every department with no employees.

NOT EXISTS is the answer to give. Mentioning that NOT IN looks equivalent and breaks when the subquery yields a NULL is what distinguishes someone who has been bitten from someone who has read the syntax.

Check yourself

1. Find employees earning more than their company's average.
2. Why does NOT IN (SELECT dept_id FROM employees) return nothing here?
3. Why must a derived table have an alias?