Subqueries in MySQL: A Powerful Tool for Complex Queries

Subqueries in MySQL. Queries nested inside other queries – and the main types you will run into.

Understanding subqueries

A subquery is a SQL query nested inside a larger one. You can use them in the SELECT, FROM, and WHERE clauses. They are useful for breaking a complex problem into smaller steps.

Basic subquery structure

A simple example:

SELECT * 
FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'London');

That returns all employees in London-based departments. The subquery (SELECT department_id FROM departments WHERE location = 'London') finds the relevant department IDs first.

Types of subqueries

Subqueries fall into a few common categories depending on where they sit and what they return.

Scalar subqueries

These return a single value, often used in SELECT or WHERE.

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

Each row gets the employee name alongside the company-wide average salary.

Correlated subqueries

A correlated subquery references columns from the outer query. MySQL runs it once per row from the outer query.

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

That finds employees earning more than the average in their own department.

Subqueries in the FROM clause

These treat the subquery result as a temporary table.

SELECT avg_dept.salary 
FROM (SELECT department_id, AVG(salary) AS salary FROM employees GROUP BY department_id) AS avg_dept;

That calculates the average salary per department.

Using subqueries for complex problems

Subqueries are handy when you need to:

1. Filter on complex conditions: use a subquery in WHERE when a simple comparison is not enough.
2. Aggregate data: nest an aggregate function inside a subquery to summarise before filtering.
3. Compare across tables: match rows in one table against aggregated data from another.

Best practices and performance

Subqueries are useful, but they are not always the fastest option:

1. Watch performance: a poorly written subquery can be slow. Index the columns it depends on.
2. Keep them readable: if a subquery gets hard to follow, break it into a view or a temporary table.
3. Test against JOINs: sometimes a JOIN does the same job more efficiently. Try both and compare.

Try the examples above on your own tables, then experiment with rewriting them as JOINs to see which approach MySQL handles better.