Nested subqueries: IN, ANY/SOME, ALL, EXISTS, scalar subqueries

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. How many copies of v are in R EXCEPT ALL S if R has 4 and S has 1? from Lesson 3.3.7
2. What does x NOT IN (1, NULL) evaluate to for x = 5? from Lesson 3.3.4
3. What does R ÷ S express? from Lesson 3.1.5

1 · The Hook

"Students whose CGPA is higher than every EE student's." "Students enrolled in some CS course." A query can use the result of another query as a value, a list, or a test. These subqueries are the most frequent SQL topic in GATE, mostly because of how ALL, ANY and NOT IN behave with empty results and NULLs.

2 · Predict First

SELECT name FROM STUDENT WHERE cgpa > ALL (SELECT cgpa FROM STUDENT WHERE dept = 'CE'); There are no CE students. Who is returned?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

"Greater than every value in an empty set" is vacuously TRUE for every non-NULL cgpa (Lesson 0.2.3): Asha, Bhavin, Chirag, Diya. Esha's NULL? For an empty set, > ALL is TRUE even for her: NULL is never compared with anything. So all 5 students.

Where subqueries appear

• In WHERE/HAVING with IN, ANY, ALL, EXISTS or a comparison.
• In FROM as a derived table: FROM (SELECT …) AS T.
• In SELECT as a scalar value.

IN and NOT IN

x IN (subquery): TRUE if x equals some value of the subquery.

SELECT name FROM STUDENT
WHERE roll IN (SELECT roll FROM ENROLL WHERE cid = 'C3');   -- Asha, Bhavin

NOT IN: TRUE only if x differs from every value and no value is NULL. If the subquery returns a NULL, NOT IN is never TRUE (Lesson 3.3.4).

ANY (SOME) and ALL

x op ANY (subquery): TRUE if the comparison is TRUE for at least one value. x op ALL (subquery): TRUE if the comparison is TRUE for every value.

NULLs: if S contains NULL, x > ALL (S) can never be TRUE (the NULL comparison is UNKNOWN); x > ANY (S) can still be TRUE through another value.

Examples on the 5-row STUDENT:

• cgpa > ALL (SELECT cgpa FROM STUDENT WHERE dept = 'EE') → cgpa > 7.8 → Asha, Chirag.
• cgpa >= ALL (SELECT cgpa FROM STUDENT) → no rows: Esha's NULL makes every comparison set contain UNKNOWN. Fix: add WHERE cgpa IS NOT NULL inside, giving Asha.
• cgpa > ANY (SELECT cgpa FROM STUDENT WHERE dept = 'CS') → cgpa > 8.4 (min of non-NULL CS values) → Asha.

EXISTS and NOT EXISTS

EXISTS (subquery) is TRUE if the subquery returns at least one row, FALSE otherwise. It is never UNKNOWN, and NULLs inside do not matter. An EXISTS subquery that does not refer to the outer query is the same for every outer row: all or nothing. Its real use is with correlated subqueries (next lesson).

Scalar subqueries

A subquery used as a value must return one row, one column:

SELECT name FROM STUDENT WHERE cgpa = (SELECT MAX(cgpa) FROM STUDENT);   -- Asha

MAX ignores NULL, so this works. If a scalar subquery returns several rows, it is a run-time error; if it returns no rows, the value is NULL.

4 · Visual

               values from subquery S = {7.8}          (EE CGPAs)
cgpa > ALL S :  9.1 ✓   7.8 ✗   8.4 ✓   6.9 ✗   NULL ?    -> Asha, Chirag
cgpa > ANY S :  same here (one value)

S with a NULL = {9.1, 8.4, NULL}  (CS CGPAs)
x > ALL S : needs x > 9.1 AND x > 8.4 AND x > NULL(UNKNOWN)  -> never TRUE
x > ANY S : x > 8.4 is enough                                  -> can be TRUE

5 · Worked Examples

Example 1 Easy

SELECT title FROM COURSE WHERE cid IN (SELECT cid FROM ENROLL WHERE roll = 3);

Solution

Roll 3 took C1, C2: DBMS, Networks.

Example 2 Medium

SELECT name FROM STUDENT WHERE cgpa < ANY (SELECT cgpa FROM STUDENT WHERE dept = 'ME');

Solution

ME CGPAs = {6.9}. cgpa < 6.9: no one (Diya's 6.9 is not < 6.9). Result: empty.

Example 3 GATE

SELECT name FROM STUDENT WHERE roll NOT IN (SELECT roll FROM ENROLL); and SELECT name FROM STUDENT WHERE roll <> ANY (SELECT roll FROM ENROLL); Results?

Solution

NOT IN: ENROLL rolls {1, 2, 3, 4} (no NULL) → Esha. <> ANY: TRUE if roll differs from some enrolled roll; every roll differs from at least one of 1–4 → all 5 students. <> ANY is not NOT IN.

8 · Connect the Dots

Lesson 3.3.7 combined finished results; this lesson placed queries inside conditions. Every subquery here was independent of the outer row. Next: correlated subqueries, which re-run for each outer row and give SQL its "for all" power.

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Empty ALL

WHERE x > ALL (subquery) where the subquery returns no rows. For a non-NULL x, the condition is:

1. (A) UNKNOWN
2. (B) FALSE
3. (C) an error
4. (D) TRUE

Q2 · NAT · 2 mark(s) — ALL with NULL

How many rows: SELECT name FROM STUDENT WHERE cgpa >= ALL (SELECT cgpa FROM STUDENT); (Esha's cgpa is NULL)

Numerical answer: ________

Q3 · NAT · 1 mark(s) — IN

How many rows: SELECT name FROM STUDENT WHERE roll IN (SELECT roll FROM ENROLL WHERE grade = 'A');

Numerical answer: ________

Q4 · MCQ · 2 mark(s) — Equivalence

Which is equivalent to x NOT IN (S) when S contains no NULL?

1. (A) x <> ALL (S)
2. (B) x <> ANY (S)
3. (C) x = ANY (S)
4. (D) NOT EXISTS (S)

Q5 · NAT · 2 mark(s) — ANY

How many rows: SELECT name FROM STUDENT WHERE cgpa > ANY (SELECT cgpa FROM STUDENT WHERE dept = 'CS');

Numerical answer: ________

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

subquery (nested query)derived tableIN / NOT IN (subquery)ANY / SOMEALLEXISTS / NOT EXISTSscalar subquery