Subqueries: A Query Inside a Query

Scalar subquery. A subquery that returns a single value can sit anywhere a number can. 'Orders above the average order':

SELECT id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

The biggest or smallest row. To fetch the row(s) holding the maximum, compare against a MAX subquery: WHERE amount = (SELECT MAX(amount) FROM orders). Unlike ORDER BY ... LIMIT 1, this returns all ties.

IN subquery. A subquery that returns a column of values works as a list for a membership test. 'Customers with at least one big order':

SELECT name
FROM customers
WHERE id IN (SELECT customer_id
             FROM orders
             WHERE amount > 1000);

IN only checks membership, so a customer with five big orders still appears once; a plain join would have repeated them.

Subquery in FROM (a derived table) lets you aggregate and then query the aggregate - 'the average of each customer's total':

SELECT AVG(total) AS avg_customer_spend
FROM (SELECT customer_id, SUM(amount) AS total
      FROM orders
      GROUP BY customer_id) AS per_customer;

Nested subqueries just stack. 'Accounts that sent money to the account that received the most' is a subquery for the top account inside a subquery for its senders. Work inside-out: write and test the innermost query on its own first, then wrap it.

Correlated subquery. When the inner query refers to the current row of the outer query, it is evaluated per row. 'Earns more than their own department's average':

SELECT e.name, e.salary
FROM employees AS e
WHERE e.salary > (SELECT AVG(salary)
                  FROM employees
                  WHERE department_id = e.department_id);

The NOT IN trap. If the subquery returns any NULL, x NOT IN (...) evaluates to UNKNOWN for every row and returns nothing. Filter NULLs inside the subquery (WHERE customer_id IS NOT NULL) or use NOT EXISTS.