Queries inside queries: subqueries

A subquery that returns one value

A subquery that returns exactly one row and one column can appear wherever a single value is expected.

SELECT name,
       price,
       ROUND((SELECT AVG(price) FROM products), 2) AS average_price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC
LIMIT 20;

(SELECT AVG(price) FROM products) produces one number, and the rest of the query treats it as if you had typed that number. This is the answer to "why can't I write WHERE price > AVG(price)" — an aggregate summarizes a group, and while WHERE runs there are no groups yet. A subquery sidesteps that by being its own complete query, finished before the outer one is filtered.

A subquery that returns a list

SELECT first_name, last_name, email, country
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE status = 'refunded'
)
ORDER BY last_name
LIMIT 20;

The inner query returns customer IDs, and IN checks whether each outer customer ID is in that list. The result is customers who have refunded orders.

EXISTS: asking whether anything matched

SELECT c.first_name, c.last_name, c.country
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.total > 1000
)
ORDER BY c.last_name
LIMIT 20;

EXISTS is true when its subquery returns at least one row. SELECT 1 is conventional because only the existence of a row matters, not the selected value.

This subquery mentions c.id from the outer query, which makes it correlated — a different question for every customer, rather than one list computed once. For "does a related row exist", that is usually both the clearest shape and the one engines optimize best.

The NOT IN trap

Of the 120 employees, 110 have no direct reports. This query attempts to find them:

-- Employees nobody reports to. 110 of the 120 qualify,
-- and this query finds none of them.
SELECT first_name, last_name, position
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

The result is zero rows, with no error.

manager_id is nullable, and the ten people at the top of the org chart have NULL in that column. The inner query therefore returns a list containing NULL. x NOT IN (1, 2, NULL) must also ask whether x differs from an unknown value, so the condition becomes unknown rather than true. A single NULL in the list can make the result empty.

IN is not affected the same way: a row that matches a real value is kept, whatever else is in the list. Only the negated form needs every comparison to be true, which is why one unknown is enough to empty it.

You can add WHERE manager_id IS NOT NULL to the subquery. In most cases, NOT EXISTS is safer because it does not compare NULL values:

SELECT e.first_name, e.last_name, e.position
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM employees m
  WHERE m.manager_id = e.id
)
ORDER BY e.last_name
LIMIT 20;

NOT EXISTS tests for the presence of rows rather than comparing values, so NULL does not enter the comparison.

A subquery as a table

A query returning many rows and columns can stand where a table would, in FROM. Such a query is called a derived table.

SELECT category, products, average
FROM (
  SELECT cat.name AS category,
         COUNT(*) AS products,
         ROUND(AVG(p.price), 2) AS average
  FROM products p
  JOIN categories cat ON cat.id = p.category_id
  GROUP BY cat.id
)
WHERE products >= 10
ORDER BY average DESC;

To filter an aggregate result, group in the inner query and filter that result as an ordinary table. This can do the same job as HAVING and also allows filtering on an alias, which is not portable in HAVING.

Derived tables can be nested, but deeply nested queries are hard to read because they must be followed from the inside out. The next lesson replaces that structure with named steps you read top to bottom.

One subquery per row

SELECT p.name,
       p.price,
       (SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.id) AS reviews,
       (SELECT ROUND(AVG(r.rating), 2) FROM reviews r WHERE r.product_id = p.id) AS avg_rating
FROM products p
ORDER BY avg_rating DESC, reviews DESC
LIMIT 20;

What you can do now

The next lesson shows how to make these more complex queries easier to read with CTEs and window functions.