MYSQL Tutorial

MySQL Subqueries

A subquery (or inner query) is a query nested inside another SQL query. Subqueries can be used in SELECT, FROM, WHERE, or HAVING clauses to break complex tasks into simpler parts.

Types of Subqueries

• Single-row: Returns one row/column.
• Multiple-row: Returns multiple rows.
• Scalar: Returns a single value.
• Correlated: References outer query columns, re-executed per row.

1. Subquery in WHERE

Find employees earning more than the average salary:

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);
  

2. Subquery in FROM

Use a subquery as a derived table to get department averages, then filter:

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;
  

3. Correlated Subquery

For each employee, show those whose salary is above their department’s average:

SELECT e.first_name, e.last_name, e.department, e.salary
FROM employees AS e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department
);
  

4. Subquery in SELECT

Include a count of orders per customer in the result:

SELECT 
  c.customer_name,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers AS c;
  

Tips & Best Practices

• Use subqueries for clarity, but watch performance—large correlated subqueries can be slow.
• Consider JOINs or WITH (CTEs) for complex operations in MySQL 8.0+.
• Alias subqueries (derived tables) for readability.
• Test subqueries independently before nesting.