In subquery

WHERE column IN (SELECT column FROM ...)

SELECT first_name, email
FROM users
WHERE id IN (
  SELECT user_id
  FROM orders
  WHERE total_amount > 500
);

-- Users who have placed at least one order over 500

Note For large datasets, EXISTS often performs better than IN with a subquery because EXISTS short-circuits on the first match. IN materializes the entire subquery result first.

SELECT columns
FROM (SELECT ... FROM ...) AS alias;

SELECT
  top_customers.first_name,
  top_customers.total_spent
FROM (
  SELECT u.first_name, SUM(o.total_amount) AS total_spent
  FROM users u
  JOIN orders o ON o.user_id = u.id
  GROUP BY u.id, u.first_name
  HAVING SUM(o.total_amount) > 1000
) AS top_customers
ORDER BY top_customers.total_spent DESC;

-- first_name | total_spent
-- Alice      | 4520.00
-- Bob        | 2310.50

Note Derived tables (subqueries in FROM) must have an alias. They are computed once and treated like a temporary table. CTEs (WITH clause) are generally more readable for the same purpose.

WHERE column > ANY (subquery)
WHERE column > ALL (subquery)

SELECT first_name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE department_id = 3
);

-- Employees earning more than everyone in department 3

Note ANY means 'at least one' — the condition must hold for at least one row from the subquery. ALL means 'every' — it must hold for all rows. An empty subquery makes ALL true and ANY false.