Subqueries

Sometimes you need a computed value to compare against. Instead of computing it separately and copying it, put that query in brackets inside the main one:

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

A subquery can also return a list, used with IN:

SELECT name
FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders WHERE status = 'Pending'
);