SQL Subqueries — Using a Query Inside Another Query

The teacher needs students whose marks exceed the class-wide average, which first has to be calculated.

A subquery is a query used inside another statement. Its role determines whether the surrounding expression expects a single value, a set of values or an existence test.

The inner AVG query produces one scalar value, and the outer WHERE compares each mark with it. IN tests membership in a set; EXISTS tests whether a matching row exists. A correlated subquery refers to an outer row, but the optimizer is free to transform the physical execution.

Related concepts: Missing Values and NULL and Common Table Expressions.

A subquery is a SQL query inside another query. It’s usually enclosed in parentheses and can be used in SELECT, FROM, or WHERE clauses.

• Scalar Subquery – returns a single value
• Row Subquery – returns one row with multiple columns
• Table Subquery – returns multiple rows and columns (used in FROM clause)

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A'),
(2, 'Diya Iyer', '9B'),
(3, 'Sneha Patil', '10A'),
(4, 'Mehul Agarwal', '8C');

CREATE TABLE results (
  roll_no INT,
  subject VARCHAR(30),
  marks INT
);

INSERT INTO results VALUES
(1, 'Maths', 85),
(2, 'Maths', 92),
(3, 'Maths', 78),
(4, 'Maths', 90);

SQL · portable concepts; preserved examples identify their engine

Let’s find students who scored more than the average marks in Maths:

SELECT name
FROM students
WHERE roll_no IN (
  SELECT roll_no
  FROM results
  WHERE subject = 'Maths'
  AND marks > (
    SELECT AVG(marks)
    FROM results
    WHERE subject = 'Maths'
  )
);

name
-------------
Diya Iyer
Mehul Agarwal

SQL · portable concepts; preserved examples identify their engine

Show each student's marks and how they compare to the average:

SELECT s.name, r.marks,
  (SELECT AVG(marks) FROM results WHERE subject = 'Maths') AS avg_marks
FROM students s
JOIN results r ON s.roll_no = r.roll_no
WHERE r.subject = 'Maths';

name           | marks | avg_marks
----------------+-------+-----------
Aarav Sharma   | 85    | 86.25
Diya Iyer      | 92    | 86.25
Sneha Patil    | 78    | 86.25
Mehul Agarwal  | 90    | 86.25

SQL · portable concepts; preserved examples identify their engine

Let’s first calculate average marks per student, then filter only those with more than 80:

SELECT t.roll_no, t.avg_marks
FROM (
  SELECT roll_no, AVG(marks) AS avg_marks
  FROM results
  GROUP BY roll_no
) AS t
WHERE t.avg_marks > 80;

roll_no | avg_marks
--------+-----------
1       | 85.0
2       | 92.0
4       | 90.0

SQL · portable concepts; preserved examples identify their engine

These refer to columns from the outer query. Example: Get students whose marks are higher than the class average.

SELECT name
FROM students s
WHERE EXISTS (
  SELECT 1
  FROM results r
  WHERE r.roll_no = s.roll_no
  AND r.marks > (
    SELECT AVG(marks)
    FROM results
  )
);

• Use subqueries for readability and step-wise logic.
• Try CTEs (Common Table Expressions) for complex nested queries.
• Avoid over-nesting — too many levels may impact performance.

A scalar subquery must obey the engine’s single-row contract. NULL can make NOT IN misleading; use a carefully written NOT EXISTS when testing absence. Avoid claiming that every subquery is physically executed once per outer row.

Find students above the three-student average.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT name, marks FROM practice_students WHERE marks > (SELECT AVG(marks) FROM practice_students) ORDER BY roll_no;

The inner result is 80. Both 85-mark students are above that value; the 70-mark student is not.

A subquery is a query used inside another statement. Its role determines whether the surrounding expression expects a single value, a set of values or an existence test.

The inner result is 80. Both 85-mark students are above that value; the 70-mark student is not.

Continue your understanding with Missing Values and NULL and Common Table Expressions.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.