Lecture 09: Advanced Subqueries & EXISTS

1. Set Comparison Operators: ANY / SOME and ALL

SQL provides set-comparison operators that evaluate an attribute against an entire column of subquery results:

ANY / SOME (Existential Quantification)

Evaluates to TRUE if the comparison holds for at least one element returned by the subquery:

• x > ANY (subquery): x is greater than the minimum value of the subquery.
• x = ANY (subquery): Exactly equivalent to x IN (subquery).

-- Find instructors who earn more than SOME instructor in the Biology department
SELECT name, salary
FROM instructor
WHERE salary > ANY (SELECT salary FROM instructor WHERE dept_name = 'Biology');

ALL (Universal Quantification)

Evaluates to TRUE only if the comparison holds for every single element in the subquery:

• x > ALL (subquery): x is greater than the maximum value of the subquery.
• x <> ALL (subquery): Equivalent to x NOT IN (subquery) (subject to the NULL trap!).

-- Find instructors whose salary is strictly greater than ALL instructors in Biology
SELECT name, salary
FROM instructor
WHERE salary > ALL (SELECT salary FROM instructor WHERE dept_name = 'Biology');

2. The EXISTS and NOT EXISTS Operators

EXISTS tests whether the subquery produces any result rows:

• Evaluates to TRUE if the subquery returns 1 or more rows.
• Evaluates to FALSE if the subquery returns 0 rows.
• Short-circuiting optimization: The database engine stops searching the moment the first matching tuple is found!

-- Find courses taught in both Fall 2025 and Spring 2026
SELECT c.course_id, c.title
FROM course c
WHERE EXISTS (
    SELECT 1 FROM section s 
    WHERE s.course_id = c.course_id AND s.semester = 'Fall' AND s.year = 2025
)
AND EXISTS (
    SELECT 1 FROM section s 
    WHERE s.course_id = c.course_id AND s.semester = 'Spring' AND s.year = 2026
);

Convention: It is standard practice to write SELECT 1 inside an EXISTS subquery because the column values are never read or returned; only row existence matters.

3. Correlated Subqueries

A subquery is correlated when it references a column from the outer query:

-- Find students who have earned more credits than the average credits of their department
SELECT s.id, s.name, s.tot_cred, s.dept_name
FROM student s
WHERE s.tot_cred > (
    SELECT AVG(s2.tot_cred)
    FROM student s2
    WHERE s2.dept_name = s.dept_name -- Correlation with outer row
);

Execution Flow

1. Fetch a candidate row from the outer table s.
2. Pass s.dept_name into the inner subquery.
3. Compute the average for that specific department.
4. Compare s.tot_cred against that computed average.
5. Repeat for every row in the outer table (unless rewritten by the query optimizer).

4. Topic Practice Questions

WHERE salary < (SELECT MIN(salary) FROM instructor WHERE dept_name = 'Physics');

SELECT i.id, i.name, i.dept_name
FROM instructor i
WHERE NOT EXISTS (
    SELECT 1 
    FROM teaches t 
    WHERE t.id = i.id
);