Subqueries

Why it matters

Many real questions need two steps. "Which employees earn more than average?" means first working out the average, then comparing every row with it. "Which customers have placed an order?" means first finding the customer ids in orders, then looking them up in customers. A subquery lets you do both steps in one statement: a complete SELECT placed inside another query, in brackets.

In SQL Fundamentals you filtered with fixed values such as WHERE salary > 60000. A subquery replaces that hard-coded number with a value the database computes from the data, so the query stays correct when the data changes.

Three places a subquery can go

1. A single value in WHERE (a scalar subquery). The inner query must return exactly one row and one column.

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

The database runs the inner SELECT AVG(salary) once, gets one number (say 67,400), and uses it like a constant.

2. A list in WHERE ... IN. The inner query returns one column with any number of rows.

-- Customers who have placed at least one order
SELECT id, name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

The opposite, NOT IN, finds customers with no orders, but be careful: if the subquery returns even one NULL, NOT IN returns no rows at all, because x NOT IN (1, NULL) is never true. NOT EXISTS (below) does not have this problem.

3. A table in FROM (a derived table). The inner query produces a temporary result you can select from. It must have an alias.

SELECT region, AVG(total_spent) AS avg_customer_spend
FROM (
  SELECT c.region, c.id, SUM(o.amount) AS total_spent
  FROM customers AS c
  JOIN orders AS o ON o.customer_id = c.id
  GROUP BY c.region, c.id
) AS customer_totals
GROUP BY region;

This is the classic "aggregate of an aggregate": total per customer first, then the average of those totals per region. You cannot nest AVG(SUM(...)) directly, so the subquery is required.

Correlated subqueries

The subqueries above run once on their own. A correlated subquery refers to a column from the outer query, so it is evaluated for each outer row.

Example employees table:

"Who earns more than the average of their own department?"

SELECT e.name, e.department, e.salary
FROM employees AS e
WHERE e.salary > (
  SELECT AVG(d.salary)
  FROM employees AS d
  WHERE d.department = e.department   -- links to the outer row
);

For Ben, the inner query averages Sales salaries (56,500), and 61,000 is higher, so Ben is kept. For Elif, it averages Engineering (92,500), and 87,000 is lower, so Elif is dropped. The aliases e (outer) and d (inner) are essential here: without them, department = department would compare the inner table with itself and always be true.

EXISTS and NOT EXISTS

EXISTS (subquery) is true when the subquery returns at least one row. It is usually correlated and reads like plain English:

-- Customers with no orders at all
SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
);

SELECT 1 is a convention: EXISTS only checks whether a row exists, so the selected value does not matter. Use NOT EXISTS instead of NOT IN whenever the inner column could contain NULL.

Subquery or join?

Many subqueries can be rewritten as joins, and modern databases often run both the same way. Pick the one that says what you mean:

• Use IN / EXISTS when you only need to filter by another table and do not need its columns. A join can multiply rows (a customer with three orders appears three times), and you would then need DISTINCT.
• Use a join when you need columns from both tables in the output.
• Use a derived table or, better, a CTE (next lesson) when you need an aggregate of an aggregate.

Common mistakes

• A scalar subquery that returns several rows. WHERE salary > (SELECT salary FROM employees WHERE department = 'Sales') fails in most databases (SQLite quietly uses the first row, which is worse). Aggregate it or use IN.
• Forgetting the alias on a derived table. FROM (SELECT ...) needs AS something in PostgreSQL and MySQL.
• NOT IN with NULLs. Returns nothing. Switch to NOT EXISTS.
• Ambiguous columns in a correlated subquery. Always alias both sides.

Quick recap

• A subquery is a SELECT inside brackets that supplies a value, a list or a table.
• Scalar subqueries return one value; IN takes a one-column list; FROM subqueries need an alias.
• A correlated subquery refers to the outer row and is evaluated per row.
• Prefer NOT EXISTS over NOT IN when NULLs are possible.

Try it

Open the Do tab and turn a company-wide comparison into a per-department one with a correlated subquery.