Subqueries

A subquery is a query written in brackets inside another query. It runs first and hands its result to the outer query.

A subquery that returns one value

“Teachers who earn more than the average” — we do not know the average in advance, but the database can work it out:

SELECT first_name, last_name, salary
FROM teachers
WHERE salary > (SELECT AVG(salary) FROM teachers)
ORDER BY salary DESC;

The subquery returns one number, and the outer query compares against it.

A subquery that returns a list: IN

“Students who got at least one score of 95 or more”:

SELECT first_name, last_name
FROM students
WHERE id IN (SELECT student_id FROM grades WHERE score >= 95);

If a subquery returns several rows, use IN, not =. With = the database complains that the subquery returns more than one row.

EXISTS — “is there any?”

EXISTS is true when the subquery returns at least one row. It usually refers to the outer row:

SELECT p.name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

Products that have never been sold. SELECT 1 — what comes back does not matter, only whether anything does.

Correlated subqueries

The subquery uses a value from the outer row and runs again for every row. “Products more expensive than the average of their own category”:

SELECT p.name, p.price
FROM products p
WHERE p.price > (SELECT AVG(x.price) FROM products x WHERE x.category_id = p.category_id);

A subquery in FROM

A result can be used like a temporary table. It must be given an alias:

SELECT AVG(n) AS avg_orders
FROM (SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id) t;

Remember

• One value — compare with =, >; a list — use IN.
• EXISTS / NOT EXISTS check whether rows exist.
• A subquery in FROM needs an alias.