MySQL Subqueries

A subquery is a query inside another query.

SELECT name
FROM students
WHERE fee > (
    SELECT AVG(fee)
    FROM students
);

The inner query calculates the average fee, and the outer query finds students whose fee is greater than that average.

Subqueries are useful when the result of one query is needed by another query.

• Compare values with an average.
• Find records above or below a calculated value.
• Find records existing in another result set.
• Find records that do not have related data.
• Generate temporary result sets.
• Filter grouped results.
• Insert data based on another query.
• Update data using another query.

SELECT column_name
FROM table_name
WHERE column_name operator (
    SELECT column_name
    FROM another_table
);

The inner SELECT is executed as part of the overall query, and its result is used by the outer query.

Suppose we want students whose fee is greater than the average fee.

SELECT
    name,
    fee
FROM students
WHERE fee > (
    SELECT AVG(fee)
    FROM students
);

The subquery:

SELECT AVG(fee)
FROM students;

returns one value, which is then used by the outer query.

A scalar subquery returns exactly one value.

SELECT
    name,
    fee
FROM students
WHERE fee > (
    SELECT AVG(fee)
    FROM students
);

The AVG() function returns one value, so it can be compared directly using >.

You can use a subquery with the equality operator when the subquery returns one value.

SELECT
    name,
    fee
FROM students
WHERE fee = (
    SELECT MAX(fee)
    FROM students
);

This finds the student or students having the maximum fee.

SELECT
    name,
    fee
FROM students
WHERE fee > (
    SELECT AVG(fee)
    FROM students
);

This returns students whose fee is greater than the average fee.

SELECT
    name,
    fee
FROM students
WHERE fee < (
    SELECT AVG(fee)
    FROM students
);

This returns students whose fee is below the average fee.

MAX() can be used inside a subquery.

SELECT
    name,
    salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

This finds employee records having the highest salary.

SELECT
    name,
    salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
);

This finds employee records having the lowest salary.

COUNT() can be used to calculate a value inside a subquery.

SELECT
    name
FROM students
WHERE student_id < (
    SELECT COUNT(*)
    FROM students
);

The subquery returns the total number of rows.

The example demonstrates the syntax, although comparing an ID with a row count is not normally a meaningful business rule.

IN is useful when a subquery returns multiple values.

SELECT
    name,
    course_id
FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
    WHERE fee > 10000
);

This finds students enrolled in courses whose fee is greater than 10,000.

NOT IN can find records whose value is not present in the subquery result.

SELECT
    name,
    course_id
FROM students
WHERE course_id NOT IN (
    SELECT id
    FROM courses
    WHERE status = 'Inactive'
);

This excludes students whose course belongs to the returned inactive-course IDs.

EXISTS checks whether the subquery returns at least one row.

SELECT
    c.course_name
FROM courses c
WHERE EXISTS (
    SELECT 1
    FROM students s
    WHERE s.course_id = c.id
);

This returns courses having at least one matching student.

NOT EXISTS checks that the subquery returns no rows.

SELECT
    c.course_name
FROM courses c
WHERE NOT EXISTS (
    SELECT 1
    FROM students s
    WHERE s.course_id = c.id
);

This finds courses without matching students.

A correlated subquery refers to a column from the outer query.

SELECT
    e.employee_name,
    e.salary,
    e.department
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e.department
);

The inner query uses the department of the current outer employee.

A scalar subquery can appear in the SELECT list.

SELECT
    name,
    fee,
    (SELECT AVG(fee) FROM students) AS average_fee
FROM students;

The overall average fee is displayed alongside every student.

A subquery in the FROM clause creates a temporary result set for the outer query. It is commonly called a derived table.

SELECT
    course_id,
    average_fee
FROM (
    SELECT
        course_id,
        AVG(fee) AS average_fee
    FROM students
    GROUP BY course_id
) AS course_summary;

The derived table must have an alias.

A subquery can be used in HAVING to compare grouped results.

SELECT
    course_id,
    AVG(fee) AS average_fee
FROM students
GROUP BY course_id
HAVING AVG(fee) > (
    SELECT AVG(fee)
    FROM students
);

This returns course groups whose average fee is greater than the overall average fee.

A subquery can provide data for an INSERT statement.

INSERT INTO selected_students
(student_id, name)
SELECT
    student_id,
    name
FROM students
WHERE fee > (
    SELECT AVG(fee)
    FROM students
);

This inserts students whose fee is above the average into another table.

A subquery can also be used to determine which rows should be updated.

UPDATE students
SET status = 'Premium'
WHERE fee > (
    SELECT average_fee
    FROM (
        SELECT AVG(fee) AS average_fee
        FROM students
    ) AS x
);

The derived table wrapper avoids the restriction involved in directly modifying and selecting from the same target table in this form.

Subqueries can help identify rows to delete.

DELETE FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
    WHERE status = 'Deleted'
);

This removes students whose course IDs belong to courses marked as Deleted.

Always test the SELECT condition first before running a DELETE query.

A subquery can return multiple rows.

SELECT
    name,
    course_id
FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
    WHERE fee > 10000
);

IN is appropriate because the inner query can return several course IDs.

Using = with a subquery that returns multiple rows can produce a MySQL error.

A subquery may return no rows.

For example:

SELECT
    name
FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
    WHERE course_name = 'Unknown Course'
);

If the inner query returns no course IDs, the outer query returns no matching students.

• Forgetting parentheses around the subquery.
• Using = when the subquery returns multiple rows.
• Returning multiple columns where one value is expected.
• Forgetting an alias for a derived table.
• Using NOT IN when NULL values can cause unexpected results.
• Writing an unnecessarily complicated nested query.
• Not testing the inner query separately.

Many tasks can be solved using either a JOIN or a subquery. The best approach depends on the problem and query structure.

A subquery can contain another subquery.

SELECT
    name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id IN (
        SELECT department_id
        FROM departments
        WHERE status = 'Active'
    )
);

This example contains multiple levels of queries.

Suppose we want to find students who have paid more than the average payment.

SELECT
    s.student_id,
    s.name,
    p.amount
FROM students s
INNER JOIN payments p
ON s.student_id = p.student_id
WHERE p.amount > (
    SELECT AVG(amount)
    FROM payments
);

The inner query calculates the average payment, and the outer query displays payments greater than that average.

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100),
    fee DECIMAL(10,2),
    status VARCHAR(20)
);

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    course_id INT,
    fee DECIMAL(10,2),
    status VARCHAR(20)
);

CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    amount DECIMAL(10,2),
    payment_date DATE
);

INSERT INTO courses
(course_name, fee, status)
VALUES
('Python', 15000, 'Active'),
('Java', 18000, 'Active'),
('PHP', 12000, 'Active'),
('SQL', 10000, 'Active');

INSERT INTO students
(name, course_id, fee, status)
VALUES
('Amit Kumar', 1, 15000, 'Active'),
('Priya Singh', 2, 18000, 'Active'),
('Rahul Sharma', 1, 15000, 'Active'),
('Neha Kumari', 3, 12000, 'Active'),
('Ravi Kumar', 4, 10000, 'Active');

INSERT INTO payments
(student_id, amount, payment_date)
VALUES
(1, 5000, '2026-01-10'),
(1, 4000, '2026-02-10'),
(2, 18000, '2026-03-15'),
(3, 7000, '2026-04-20'),
(4, 6000, '2026-05-10');

-- Students whose fee is above average
SELECT
    name,
    fee
FROM students
WHERE fee > (
    SELECT AVG(fee)
    FROM students
);

-- Students enrolled in courses costing more than 12000
SELECT
    name,
    course_id
FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
    WHERE fee > 12000
);

-- Courses having at least one student
SELECT
    c.course_name
FROM courses c
WHERE EXISTS (
    SELECT 1
    FROM students s
    WHERE s.course_id = c.id
);

-- Courses without students
SELECT
    c.course_name
FROM courses c
WHERE NOT EXISTS (
    SELECT 1
    FROM students s
    WHERE s.course_id = c.id
);

-- Students who made a payment above average
SELECT
    s.name,
    p.amount
FROM students s
INNER JOIN payments p
ON s.student_id = p.student_id
WHERE p.amount > (
    SELECT AVG(amount)
    FROM payments
);

-- Students whose fee is above their course average
SELECT
    s.name,
    s.fee,
    s.course_id
FROM students s
WHERE s.fee > (
    SELECT AVG(s2.fee)
    FROM students s2
    WHERE s2.course_id = s.course_id
);

This complete example demonstrates scalar subqueries, IN, EXISTS, NOT EXISTS, aggregate functions, and correlated subqueries.