MySQL Subqueries

A subquery is a SELECT query written inside another SQL query. The inner query runs first, and its result is passed to the outer query. Subqueries allow complex, multi-step logic to be expressed in a single SQL statement.

Why Use Subqueries?

Some questions cannot be answered in a single query. For example: "Find all employees who earn more than the average salary." To answer this, the average salary must be calculated first, then employees must be compared against it. A subquery handles both steps at once.

Syntax

SELECT column_name
FROM table_name
WHERE column_name operator (SELECT column FROM table WHERE condition);

Sample Table

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES (1, 'Ravi Kumar', 'IT', 72000);
INSERT INTO employees VALUES (2, 'Sneha Joshi', 'HR', 45000);
INSERT INTO employees VALUES (3, 'Karan Das', 'IT', 88000);
INSERT INTO employees VALUES (4, 'Pooja Nair', 'Finance', 61000);
INSERT INTO employees VALUES (5, 'Amit Roy', 'HR', 53000);

Example: Employees Earning Above Average Salary

SELECT emp_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The inner query SELECT AVG(salary) FROM employees returns 63800. The outer query then returns employees with salary above 63800.

Result:

emp_name    | salary
------------+--------
Ravi Kumar  | 72000.00
Karan Das   | 88000.00

Subquery in WHERE with IN

SELECT emp_name
FROM employees
WHERE department IN (SELECT department FROM employees WHERE salary > 70000);

The inner query finds departments where at least one employee earns over 70000. The outer query returns all employees in those departments.

Subquery in SELECT (Scalar Subquery)

A scalar subquery returns a single value and is used directly inside the SELECT clause.

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

This displays each employee's salary alongside the company-wide average.

Subquery in FROM (Derived Table)

A subquery can act as a temporary table in the FROM clause. It must be given an alias.

SELECT dept_summary.department, dept_summary.avg_sal
FROM (
    SELECT department, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
) AS dept_summary
WHERE dept_summary.avg_sal > 60000;

The inner query creates a temporary grouped result. The outer query then filters it.

Correlated Subquery

A correlated subquery references a column from the outer query. It runs once for each row processed by the outer query.

SELECT emp_name, salary, department
FROM employees AS e
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department = e.department
);

This finds the highest-paid employee in each department.

Subquery vs JOIN

Key Points

• A subquery is a query nested inside another query.
• The inner query executes first; its result is used by the outer query.
• Subqueries can appear in WHERE, SELECT, and FROM clauses.
• A scalar subquery returns exactly one value.
• A correlated subquery references the outer query's columns and runs once per row.