Subqueries

Subqueries in MySQL

A subquery is a query nested inside another query.

In WHERE

-- Users who made orders
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);

-- Most expensive product in each category
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products p2
               WHERE p2.category_id = products.category_id);

In FROM (derived table)

SELECT dept, avg_salary FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg
WHERE avg_salary > 60000;

EXISTS

SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Pro Tip

EXISTS is often faster than IN for large subquery result sets.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned