Subqueries

A subquery is a query in brackets inside another query. The outer query uses the inner query's answer.

A single value

Which products cost more than the average? Without a subquery that takes two steps: work out the average, then type it into a second query. With one, it's a single query:

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

A subquery that returns one value can go anywhere a value can, including the SELECT list:

SELECT
  name,
  price,
  ROUND(price - (SELECT AVG(price) FROM products), 2) AS vs_average
FROM products
ORDER BY vs_average DESC;

A list of values: IN

Customers with an order that's still processing:

SELECT name, city
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'processing');

EXISTS and NOT EXISTS

EXISTS (subquery) is true when the subquery returns at least one row. Customers who have never ordered:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Correlated subqueries

The subquery above uses c.customer_id from the outer query, which makes it correlated: conceptually, it runs once for every outer row. Another example, employees who earn more than their own department's average:

SELECT first_name, department, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees WHERE department = e.department
)
ORDER BY department;

A table of results: subqueries in FROM

A subquery in FROM acts as a temporary table, and needs an alias. Average order value takes two steps, a total for each order and then the average of those totals:

SELECT ROUND(AVG(order_total), 2) AS avg_order_value
FROM (
  SELECT oi.order_id, SUM(oi.quantity * p.price) AS order_total
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY oi.order_id
) AS totals;

WITH: name your subqueries

A common table expression (CTE) moves the subquery to the top and gives it a name, which reads far better as queries grow:

WITH totals AS (
  SELECT oi.order_id, ROUND(SUM(oi.quantity * p.price), 2) AS order_total
  FROM order_items oi
  JOIN products p ON p.product_id = oi.product_id
  GROUP BY oi.order_id
)
SELECT order_id, order_total
FROM totals
WHERE order_total > (SELECT AVG(order_total) FROM totals)
ORDER BY order_total DESC;

totals is used twice, for the list and for the average, but written once.