MySQL — Subqueries

Basic Subquery

A query inside another query:

SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Subquery in WHERE

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'USA');

Subquery in FROM

SELECT dept, avg_sal
FROM (SELECT department AS dept, AVG(salary) AS avg_sal
      FROM employees GROUP BY department) AS dept_avg;

Subquery in SELECT

SELECT name, salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

Correlated Subquery

References a column from the outer query:

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

EXISTS

Checks if a subquery returns any rows:

SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

NOT EXISTS

SELECT name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

ANY / ALL

-- Salary greater than ANY salary in Engineering
SELECT name FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Engineering');

-- Salary greater than ALL salaries in Engineering
SELECT name FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Engineering');

Subquery Performance Tips

Tip	Detail
Use EXISTS over IN for large datasets	EXISTS stops at first match
Avoid correlated subqueries when possible	They run once per outer row
Index columns used in subqueries	Speeds up filtering

Mini Practice

Write SQL code that:

1. Uses a subquery in WHERE to filter by aggregate
2. Uses a subquery in FROM as a derived table
3. Uses EXISTS to check for related rows
4. Uses a correlated subquery

Up Next

Continue with Views — saved queries that act like virtual tables.