Nested Queries and Subqueries

What a Subquery Is

A subquery is a SELECT nested inside another SQL statement. The containing statement is the outer query. Parentheses mark the inner query and its result becomes an operand or table source for the outer query.

Verified Marks Lab

CREATE TABLE sq_students (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(60) NOT NULL,
  class_name VARCHAR(20) NOT NULL,
  marks DECIMAL(5,2) NULL
);

INSERT INTO sq_students VALUES
(1, 'Aarav', 'X-A', 86.50),
(2, 'Meera', 'X-A', 91.00),
(3, 'Kabir', 'X-B', 74.00),
(4, 'Sana', 'X-B', 88.50),
(5, 'Vihaan', 'X-B', NULL);

Four known marks total 340.00, so AVG ignores Vihaan's NULL and returns 85.00. This controlled edge case is used throughout Day 7.

Scalar Subquery: Above Overall Average

SELECT student_name, marks
FROM sq_students
WHERE marks > (
  SELECT AVG(marks)
  FROM sq_students
)
ORDER BY marks DESC;

The inner query produces one value, 85.00. The outer query compares each known mark with it. Kabir is below average; Vihaan's comparison with NULL is UNKNOWN and is not retained by WHERE.

Scalar value in SELECT

SELECT student_name, marks,
       (SELECT ROUND(AVG(marks), 2)
        FROM sq_students) AS overall_average
FROM sq_students
ORDER BY student_id;

The uncorrelated average is logically the same for every output row. MySQL may optimize its evaluation; do not assume a literal procedural loop.

Column Subquery with IN

Find students in classes that contain at least one score of 90 or more:

SELECT student_name, class_name
FROM sq_students
WHERE class_name IN (
  SELECT DISTINCT class_name
  FROM sq_students
  WHERE marks >= 90
)
ORDER BY student_id;

The inner query returns the one-column set X-A. A column subquery may return many rows; IN tests membership rather than demanding a scalar.

Table Subquery in FROM

SELECT class_name, class_average
FROM (
  SELECT class_name,
         ROUND(AVG(marks), 2) AS class_average
  FROM sq_students
  GROUP BY class_name
) AS class_summary
WHERE class_average > 82
ORDER BY class_name;

A FROM subquery is a derived table and needs an alias. X-B averages 81.25 because AVG ignores Vihaan's NULL. A CTE can express the same intermediate result and will be covered in the next advanced cluster.

Cardinality Errors and NULL Rules

• A scalar subquery returning two or more rows raises an error.
• An empty scalar subquery returns NULL.
• NOT IN can become UNKNOWN if its subquery contains NULL; prefer NOT EXISTS for absence tests unless NULL is deliberately excluded.
• Comparison types must be compatible. Avoid implicit conversions that obscure meaning or indexes.
• Aliases are scoped to their query block; qualify columns when names repeat.

-- Unsafe if class_name subquery could contain NULL:
WHERE class_name NOT IN (SELECT class_name FROM some_table)

-- Explicitly remove NULL when that matches the rule:
WHERE class_name NOT IN (
  SELECT class_name
  FROM some_table
  WHERE class_name IS NOT NULL
)

Subquery or JOIN?

Equivalent-looking rewrites can differ when NULL, duplicates or empty sets are present. Prove equivalence with edge cases before changing syntax for performance.

Optimization and Practice

1. Verify the inner result and expected cardinality independently.
2. Index correlation and filtering columns when selectivity justifies it.
3. Use EXPLAIN to inspect transformations such as semijoin or materialization.
4. Select only required inner columns.
5. Measure with representative data; readable SQL is the starting point.

Practice: list students above 80; find the class with the highest average using a scalar comparison; and deliberately create a multi-row scalar error in a disposable database, then correct it with IN.

Official References

• MySQL 8.4: Subqueries
• MySQL 8.4: Scalar Subqueries
• MySQL 8.4: Subquery Optimization

Syntax and behavior were checked against the official MySQL 8.4 manual. Test every query on a disposable copy before production use.