Subqueries

A subquery is a query inside another query. Use one when you need a value or list from one query in order to run another, like "products priced above average".

-- Products more expensive than average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Users who have placed at least one order
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders);

-- Subquery as a temporary table
SELECT category, max_price
FROM (
  SELECT category, MAX(price) AS max_price
  FROM products
  GROUP BY category
) AS top_prices;