Subqueries

A subquery is a SELECT nested inside another statement.

Scalar subquery (returns one value)

SELECT name_en, cgpa
FROM students
WHERE cgpa > (SELECT AVG(cgpa) FROM students);

SELECT name_en,
       cgpa - (SELECT AVG(cgpa) FROM students) AS above_average
FROM students;

Subquery in the column list

SELECT
  d.name_en,
  (SELECT COUNT(*) FROM students s WHERE s.department = d.code) AS total
FROM departments d;

This runs once per outer row, so a JOIN … GROUP BY is usually faster for large tables.

IN / NOT IN

SELECT * FROM students
WHERE department IN (SELECT code FROM departments WHERE active = 1);

SELECT * FROM students
WHERE id NOT IN (SELECT student_id FROM enrollments WHERE student_id IS NOT NULL);

Trap: if the subquery of a NOT IN returns even one NULL, the whole condition becomes unknown and you get zero rows. Add IS NOT NULL or use NOT EXISTS instead.

ANY, SOME and ALL

-- higher than at least one EEE student
SELECT * FROM students
WHERE cgpa > ANY (SELECT cgpa FROM students WHERE department = 'EEE');

-- higher than every EEE student
SELECT * FROM students
WHERE cgpa > ALL (SELECT cgpa FROM students WHERE department = 'EEE');

> ANY is equivalent to "greater than the minimum"; > ALL is "greater than the maximum".

Correlated subqueries

A correlated subquery references the outer query and is re-evaluated for each outer row.

-- students whose CGPA beats their own department's average
SELECT s.name_en, s.department, s.cgpa
FROM students s
WHERE s.cgpa > (
  SELECT AVG(s2.cgpa) FROM students s2 WHERE s2.department = s.department
);

EXISTS

EXISTS stops at the first matching row, which makes it efficient for "does any row exist" checks.

SELECT s.name_en
FROM students s
WHERE EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.id);

SELECT s.name_en
FROM students s
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.id);

Unlike NOT IN, NOT EXISTS handles NULL correctly.

Subquery in FROM (derived table)

SELECT department, avg_cgpa
FROM (
  SELECT department, AVG(cgpa) AS avg_cgpa
  FROM students GROUP BY department
) AS dept_stats
WHERE avg_cgpa > 3.0;

A derived table must be given an alias. In MySQL 8 a CTE is usually a clearer way to write the same thing.

Subqueries in UPDATE and DELETE

UPDATE students
SET scholarship = 1
WHERE cgpa >= (SELECT AVG(cgpa) FROM (SELECT cgpa FROM students) AS t);

MySQL cannot read from the same table it is updating directly, so the extra derived table t is required.