Writing Subqueries in MySQL

A subquery is a query nested inside another query. It can appear in the SELECT list, FROM clause, or WHERE clause. Subqueries let you break complex problems into logical steps, compare rows against aggregated values, and filter data based on the results of another query.

Scalar Subqueries

A scalar subquery returns a single value. You can use it in a SELECT list or in a WHERE comparison. MySQL evaluates the inner query first and then uses the result in the outer query.

-- Employees earning above the company average
SELECT first_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
);

Subquery in the FROM Clause (Derived Table)

A subquery in the FROM clause acts as a temporary table. MySQL requires you to give it an alias. This pattern is useful when you need to aggregate data first and then filter or join the result.

SELECT dept_stats.department, dept_stats.avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_stats
WHERE dept_stats.avg_salary > 70000;

Subquery with IN

When the subquery returns multiple rows, use IN to check membership. This replaces complex joins in cases where you only need to filter the outer query.

-- Products that have been ordered at least once
SELECT product_name, price
FROM products
WHERE id IN (
  SELECT DISTINCT product_id FROM order_items
);

Correlated Subqueries

A correlated subquery references a column from the outer query, so it re-executes for every row. While powerful, correlated subqueries can be slow on large datasets. Consider rewriting them as JOINs when performance matters.

-- Employees who earn more than their department average
SELECT e.first_name, e.salary, e.department
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
);

EXISTS and NOT EXISTS

EXISTS returns true if the subquery produces at least one row. It is often more efficient thanIN for large datasets because MySQL can stop as soon as it finds a match.

-- Customers who have placed orders
SELECT c.first_name, c.email
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

-- Customers who have never ordered
SELECT c.first_name, c.email
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

When to Use Subqueries vs JOINs

Subqueries improve readability when filtering by aggregated values or checking existence. JOINs are generally faster when you need columns from both tables in the result. Use EXPLAIN to compare execution plans and choose the approach that performs best for your specific data.