MySQL Subqueries: Use One Query Inside Another

A subquery is a query placed inside another SQL statement. The inner query produces a value or set of rows that the outer query can use for filtering, comparison, calculation, or joining.

SELECT id,
       name,
       class,
       mark
FROM student
WHERE mark = (
    SELECT MAX(mark)
    FROM student
);

This returns every student whose mark equals the highest mark in the table.

Inner Query and Outer Query Top ↑

A subquery has two logical parts:

• inner query - the query inside parentheses,
• outer query - the query that uses the inner query's result.

SELECT id,
       name,
       mark
FROM student
WHERE mark > (
    SELECT AVG(mark)
    FROM student
);

The inner query calculates the average mark. The outer query then returns students whose mark is above that average.

Scalar Subquery Top ↑

A scalar subquery returns one value. It can be compared with =, >, <, and other comparison operators.

SELECT id,
       name,
       mark
FROM student
WHERE mark = (
    SELECT MAX(mark)
    FROM student
);

The subquery returns one value: the maximum mark.

Complete Row Containing MAX() Top ↑

This is one of the most useful subquery patterns:

SELECT id,
       name,
       class,
       mark
FROM student
WHERE mark = (
    SELECT MAX(mark)
    FROM student
)
ORDER BY id;

If several students share the maximum mark, all of them are returned.

See MySQL MAX() for complete-row and per-group maximum patterns.

Compare Rows with an Average Top ↑

A scalar subquery can calculate an aggregate used by the outer query:

SELECT id,
       name,
       class,
       mark
FROM student
WHERE mark > (
    SELECT AVG(mark)
    FROM student
)
ORDER BY mark DESC, id;

The inner query returns the overall average. The outer query returns rows above it.

Below the average for one class Top ↑

SELECT id,
       name,
       class,
       mark
FROM student
WHERE class = 'Six'
  AND mark < (
      SELECT AVG(mark)
      FROM student
      WHERE class = 'Six'
  )
ORDER BY mark, id;

See MySQL AVG().

Subquery with IN Top ↑

Use IN when the subquery can return several values.

For example, return students whose class appears in a selected class list generated by another query:

SELECT id,
       name,
       class,
       mark
FROM student
WHERE class IN (
    SELECT DISTINCT class
    FROM student
    WHERE mark >= 90
)
ORDER BY class, id;

The inner query first identifies classes containing at least one mark of 90 or more. The outer query then returns all students belonging to those classes.

NOT IN and NULL Top ↑

NOT IN needs special care when the subquery can return NULL.

This query looks reasonable:

SELECT id,
       name
FROM student
WHERE id NOT IN (
    SELECT f_id
    FROM student_football
);

But if student_football.f_id contains NULL, SQL's three-valued logic can cause NOT IN to return no rows.

If the column is nullable, either exclude NULL explicitly:

SELECT id,
       name
FROM student
WHERE id NOT IN (
    SELECT f_id
    FROM student_football
    WHERE f_id IS NOT NULL
);

or use NOT EXISTS, which often expresses an anti-join more safely and directly.

EXISTS and NOT EXISTS Top ↑

EXISTS checks whether the subquery produces at least one row.

Students selected for football Top ↑

SELECT s.id,
       s.name
FROM student AS s
WHERE EXISTS (
    SELECT 1
    FROM student_football AS f
    WHERE f.f_id = s.id
)
ORDER BY s.id;

Students not selected for football Top ↑

SELECT s.id,
       s.name
FROM student AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM student_football AS f
    WHERE f.f_id = s.id
)
ORDER BY s.id;

The value selected inside EXISTS is not important. SELECT 1 is commonly used because only row existence matters.

The same unmatched-row problem can also be solved with LEFT JOIN ... IS NULL.

Subquery with UNION Top ↑

Suppose students can be selected for football or baseball. To return students who are not selected for either team:

SELECT id,
       name
FROM student
WHERE id NOT IN (
    SELECT f_id
    FROM student_football
    WHERE f_id IS NOT NULL

    UNION

    SELECT b_id
    FROM student_baseball
    WHERE b_id IS NOT NULL
)
ORDER BY id;

This preserves the original Plus2net example but makes the NULL requirement explicit.

See UNION and UNION ALL for set-combination rules.

NOT EXISTS version Top ↑

SELECT s.id,
       s.name
FROM student AS s
WHERE NOT EXISTS (
    SELECT 1
    FROM student_football AS f
    WHERE f.f_id = s.id
)
AND NOT EXISTS (
    SELECT 1
    FROM student_baseball AS b
    WHERE b.b_id = s.id
)
ORDER BY s.id;

This form avoids the NOT IN NULL issue entirely.

ANY and ALL Top ↑

ANY compares a value with the set returned by a subquery and succeeds when the comparison is true for at least one returned value.

SELECT id,
       name,
       mark
FROM student
WHERE mark > ANY (
    SELECT mark
    FROM student
    WHERE class = 'Four'
)
ORDER BY mark, id;

This means the student's mark is greater than at least one mark from class Four.

ALL Top ↑

SELECT id,
       name,
       mark
FROM student
WHERE mark > ALL (
    SELECT mark
    FROM student
    WHERE class = 'Four'
)
ORDER BY mark, id;

This requires the mark to be greater than every non-NULL comparison value returned by the subquery.

Correlated Subquery Top ↑

A correlated subquery refers to a column from the outer query. Conceptually, the inner query depends on the current outer row.

Return students whose mark is above the average of their own class:

SELECT s.id,
       s.name,
       s.class,
       s.mark
FROM student AS s
WHERE s.mark > (
    SELECT AVG(s2.mark)
    FROM student AS s2
    WHERE s2.class = s.class
)
ORDER BY s.class, s.mark DESC, s.id;

The reference s2.class = s.class connects the inner query to the current row of the outer query.

Subquery in the SELECT List Top ↑

A scalar subquery can also appear as a returned column:

SELECT id,
       name,
       mark,
       (
           SELECT AVG(mark)
           FROM student
       ) AS overall_avg
FROM student
ORDER BY id;

The same overall average is shown beside every row.

When the requirement is a per-group value beside every detail row, a window function such as AVG(mark) OVER (PARTITION BY class) may be clearer on MySQL 8.0+.

Subquery in FROM: Derived Table Top ↑

A subquery in FROM creates a derived table. Give it an alias.

Calculate the average of class averages:

SELECT AVG(class_avg) AS avg_of_class_averages
FROM (
    SELECT class,
           AVG(mark) AS class_avg
    FROM student
    GROUP BY class
) AS class_stats;

The inner query creates one row per class. The outer query aggregates those derived rows.

See GROUP BY.

Subquery vs JOIN Top ↑

The same problem can often be written in more than one form.

Using EXISTS Top ↑

SELECT s.id,
       s.name
FROM student AS s
WHERE EXISTS (
    SELECT 1
    FROM student_football AS f
    WHERE f.f_id = s.id
);

Using INNER JOIN Top ↑

SELECT s.id,
       s.name
FROM student AS s
INNER JOIN student_football AS f
  ON f.f_id = s.id;

If student_football can contain several rows for the same student, the JOIN can repeat that student while EXISTS still returns the outer student row once. That semantic difference matters.

PHP PDO Example Top ↑

Use a prepared statement when an outer or inner condition depends on external input:

<?php
$class='Six';

$sql="SELECT id, name, class, mark
      FROM student
      WHERE class=:class_outer
        AND mark < (
            SELECT AVG(mark)
            FROM student
            WHERE class=:class_inner
        )
      ORDER BY mark, id";

$stmt=$dbo->prepare($sql);
$stmt->bindValue(
    ':class_outer',
    $class,
    PDO::PARAM_STR
);
$stmt->bindValue(
    ':class_inner',
    $class,
    PDO::PARAM_STR
);
$stmt->execute();

foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
    echo '<p>'
        .htmlspecialchars(
            $row['name'],
            ENT_QUOTES,
            'Windows-1252'
        )
        .' : '
        .(int)$row['mark']
        .'</p>';
}

The class value is bound separately in both places where it appears. Do not concatenate user input directly into either the inner or outer query.

See PDO connection and PDO record selection.

Performance Notes Top ↑

• Index columns used in important WHERE, JOIN, EXISTS, and correlation conditions.
• Use EXPLAIN to inspect important subqueries rather than assuming that a JOIN is always faster.
• Use EXISTS when you only need to test whether a related row exists; it can avoid unnecessary duplicate row production.
• Use UNION ALL instead of UNION inside a subquery when duplicate elimination is not required.
• Filter source rows as early as the logic allows.
• Correlated subqueries should be measured carefully on large data sets.
• Keep scalar subqueries truly scalar when using operators such as =.
• A subquery that repeats the same expensive aggregate for every outer row may sometimes be better expressed with a JOIN, derived table, CTE, or window function.

Common Subquery Mistakes Top ↑

Using = with a multi-row subquery Top ↑

If several values can be returned, use IN, ANY, ALL, EXISTS, or rewrite the query appropriately.

Using NOT IN when NULL may be returned Top ↑

NULL inside a NOT IN result set can make the comparison unknown for every candidate row. Exclude NULL or use NOT EXISTS.

Using a meaningless self-subquery Top ↑

The old example class NOT IN (SELECT DISTINCT class FROM student) asks whether a student's class is absent from the same table's class list. For ordinary non-NULL class values, that condition cannot be true and does not teach a useful NOT IN pattern.

Mixing unrelated datatypes Top ↑

The previous page included a query comparing class with MAX(mark). A class value and a numeric mark represent different concepts and should not be compared.

Assuming MAX() alone returns the complete row Top ↑

MAX() returns the maximum value. Use a subquery, JOIN, or window function when you need the row that contains it.

Assuming subqueries are always easier or faster than JOINs Top ↑

Choose based on semantics, readability, and the actual execution plan.

Forgetting an alias for a derived table Top ↑

A subquery used in FROM should be given an alias such as AS class_stats.

Related Tutorials and Download Top ↑

Download the existing student / football / baseball SQL dump

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: