SQL Subqueries: Scalar, Multi-Row & Correlated Nested Queries

In advanced database development, answering complex business questions frequently requires multi-stage evaluations (e.g., "Find all students whose GPA is higher than the average GPA of their specific department"). A Subquery (or Nested Query) is a SELECT query embedded inside another SQL statement.

Figure 4.1: Structural comparison between nested subqueries and modular Common Table Expressions (CTEs).

1. Scalar Subqueries (Returns Single 1×1 Value)

A scalar subquery evaluates to a single value. It can be placed in SELECT, WHERE, or HAVING clauses:

-- Find all students scoring above the university average GPA
SELECT student_id, first_name, gpa
FROM students
WHERE gpa > (
    SELECT AVG(gpa) 
    FROM students
);

2. Multi-Row Subqueries (IN, ANY, ALL)

When a subquery returns a column of multiple values, use operators like IN, ANY, or ALL:

-- Find all students enrolled in active Science or IT departments
SELECT student_id, first_name, course
FROM students
WHERE dept_id IN (
    SELECT dept_id 
    FROM departments 
    WHERE dept_name IN ('Engineering', 'Computer Science')
);

3. Correlated Subqueries (Row-by-Row Evaluation)

A Correlated Subquery references columns from the outer query. It cannot run independently; the database engine re-evaluates the inner subquery for every candidate row of the outer table:

-- Find students whose GPA exceeds the average of THEIR specific course
SELECT s1.first_name, s1.course, s1.gpa
FROM students s1
WHERE s1.gpa > (
    SELECT AVG(s2.gpa)
    FROM students s2
    WHERE s2.course = s1.course  -- Correlated outer link!
);

4. The EXISTS & NOT EXISTS Operators

EXISTS tests for the existence of rows returned by a subquery. It returns TRUE immediately upon encountering the first matching record (short-circuiting):

-- Find all departments that have at least one active student
SELECT d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 
    FROM students s 
    WHERE s.dept_id = d.dept_id
);

Next, let us learn how to replace deeply nested subqueries with clean, readable SQL Common Table Expressions (CTE)!