What Is a Subquery?
A subquery is a SELECT statement nested inside another statement. The inner query executes first, then its result feeds into the outer query:
SELECT columns
FROM table_name
WHERE column operator (
SELECT column FROM table_name WHERE condition
);Sample Table
+----+-----------+------------+--------+-----------+
| ID | Name | Department | Salary | City |
+----+-----------+------------+--------+-----------+
| 1 | Priya K. | Sales | 55000 | Mumbai |
| 2 | John D. | Marketing | 62000 | London |
| 3 | Arun M. | IT | 78000 | Delhi |
| 4 | Emma W. | Sales | 51000 | Sydney |
| 5 | Ravi S. | IT | 72000 | Mumbai |
| 6 | Sara L. | HR | 58000 | New York |
+----+-----------+------------+--------+-----------+Subquery in WHERE Clause
Find employees who earn more than the average salary:
SELECT Name, Salary
FROM Employees
WHERE Salary > (
SELECT AVG(Salary) FROM Employees
);+-----------+--------+ | Name | Salary | +-----------+--------+ | John D. | 62000 | | Arun M. | 78000 | | Ravi S. | 72000 | +-----------+--------+
The inner query calculates the average salary (62,667). The outer query returns employees above that value.
Subquery with IN
Find employees in departments that have someone earning over 70,000. This uses IN with a subquery:
SELECT Name, Department, Salary
FROM Employees
WHERE Department IN (
SELECT Department FROM Employees
WHERE Salary > 70000
);+-----------+------------+--------+ | Name | Department | Salary | +-----------+------------+--------+ | Arun M. | IT | 78000 | | Ravi S. | IT | 72000 | +-----------+------------+--------+
Subquery in SELECT Clause
Show each employee alongside the company-wide average salary:
SELECT Name, Salary,
(SELECT ROUND(AVG(Salary), 0) FROM Employees) AS AvgSalary,
Salary - (SELECT ROUND(AVG(Salary), 0) FROM Employees) AS Difference
FROM Employees;+-----------+--------+-----------+------------+ | Name | Salary | AvgSalary | Difference | +-----------+--------+-----------+------------+ | Priya K. | 55000 | 62667 | -7667 | | John D. | 62000 | 62667 | -667 | | Arun M. | 78000 | 62667 | 15333 | | Emma W. | 51000 | 62667 | -11667 | | Ravi S. | 72000 | 62667 | 9333 | | Sara L. | 58000 | 62667 | -4667 | +-----------+--------+-----------+------------+
Subquery in FROM Clause (Derived Table)
First calculate department totals, then filter those totals. The subquery acts as a temporary table using an alias:
SELECT DeptSummary.Department, DeptSummary.TotalSalary
FROM (
SELECT Department, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY Department
) AS DeptSummary
WHERE DeptSummary.TotalSalary > 100000;+------------+-------------+ | Department | TotalSalary | +------------+-------------+ | IT | 150000 | | Sales | 106000 | +------------+-------------+
Correlated vs Non-Correlated Subqueries
| Type | How It Works | Performance |
|---|---|---|
| Non-correlated | Inner query runs once, independently | Faster — executes once |
| Correlated | Inner query runs once per outer row, referencing the outer query | Slower — runs many times |
A correlated subquery references a column from the outer query. Find employees who earn more than their department average:
SELECT Name, Department, Salary
FROM Employees AS e1
WHERE Salary > (
SELECT AVG(Salary) FROM Employees AS e2
WHERE e2.Department = e1.Department
);+-----------+------------+--------+ | Name | Department | Salary | +-----------+------------+--------+ | Priya K. | Sales | 55000 | | Arun M. | IT | 78000 | +-----------+------------+--------+
⚠️ Subquery Performance
Correlated subqueries can be slow on large tables because the inner query runs once per outer row. Consider using JOINs or window functions as faster alternatives when possible.
For the complete subquery reference, see the official MySQL subquery documentation. In the next chapter, you will learn SQL Window Functions for advanced row-level calculations.
If you work with data regularly, explore our ready-made Dashboard Templates on NextGenTemplates that use SQL-powered data for business reporting.
📺 Visit our YouTube channel @NeoTechNavigators for step-by-step video tutorials and dashboard demos on this topic.
🧪 Try It Yourself
Write a query that finds the employee with the highest salary using a subquery.
SELECT Name, Department, Salary
FROM Employees
WHERE Salary = (
SELECT MAX(Salary) FROM Employees
);Expected Output:
+-----------+------------+--------+ | Name | Department | Salary | +-----------+------------+--------+ | Arun M. | IT | 78000 | +-----------+------------+--------+
📝 What You Learned in This Chapter
- A subquery is a SELECT inside another SQL statement
- Subqueries can go in WHERE, SELECT, and FROM clauses
- Non-correlated subqueries run once; correlated subqueries run once per outer row
- Subqueries in FROM create derived (temporary) tables
- Use subqueries to compare values against aggregates dynamically
- Consider JOINs or window functions for better performance on large datasets
Frequently Asked Questions
📖 Chapter 28 of 30 — SQL Tutorial on Neotech Navigators
