3.3Nested queries: IN, EXISTS, ALL, ANY, correlated
The idea
A nested query places one query inside another. Operators: IN, EXISTS, ALL, ANY. A correlated subquery uses the outer row.
Think of it like this: Asking a colleague a question for each person on your list: the subquery is re-asked per outer row.
Why it exists
Choosing the right nested query is a GATE staple, and "NOT EXISTS twice" is division.
How it works
- IN: is the value in the set returned?
- EXISTS: did the subquery return any row?
- x > ALL (S) means greater than the maximum; x > ANY (S) means greater than the minimum.
- Correlated: the inner query mentions the outer table, so it runs per outer row.
Worked example: Students who took all required courses.
SELECT … WHERE NOT EXISTS (required course r WHERE NOT EXISTS (enrolment of this student in r)).
Now play with it
- →Step through each tab and read the notes.
- →Rewrite > ALL using MAX.
SELECT name FROM Emp WHERE did IN (SELECT did FROM Dept WHERE city = 'Pune');inner query runs once, outer keeps rows whose did is in that set
NOT IN returns nothing if the subquery result contains a NULL.
Check yourself
salary > ALL (empty set) evaluates to
Exam notes (the dense version)
IN tests membership; EXISTS tests whether the subquery returns any row; x > ALL (…) means greater than every value (true on an empty set); x > ANY (…) means greater than some value (false on an empty set). A correlated subquery refers to the outer row and is re-evaluated for each outer row. "Not exists … not exists" expresses division.
Previous year questions: SQL (13)
PYQs are grouped by topic, so this is the full set for SQL; try the ones that match this subtopic. Open all 13 on the topic page.