Non-Correlated Subqueries: IN / NOT IN

◈ 6 cards

Use a subquery that runs once to produce a value set, then filter the outer query with IN / NOT IN — the set-membership idiom for "is this row in (or not in) that set?".

A query inside a query

A subquery is a SELECT nested inside another statement's WHERE (or HAVING). A non-correlated (regular) subquery is the simple kind: the inner query is self-contained, runs once, and hands its result up to the outer query like a precomputed list of values. The outer query then uses IN or NOT IN to test membership.

x IN (subquery) — true when x equals any value the subquery returned.
x NOT IN (subquery) — true when x matches none of them.

The inner SELECT must return exactly one column (the values being compared).

When a subquery, when a join?

The rule the exam quotes (6-7): anything written as a subquery can be rewritten as a join, but not vice-versa. The reason is projection: a join can display columns from every table; a subquery's inner columns cannot appear in the outer SELECT — the subquery is a filter only. So:

Need to display a column from the other table? → join.
Only need the other table to filter the outer rows? → either works; the subquery often reads more clearly ("keep students whose id appears in registrations").

Worked example: qualified for one course, not another (exam 6-27)

Question: which instructors are qualified to teach COMP 378 but not COMP 456? List id and name.

The "qualified for X" part is a join (instructors→qualified→courses). The "and not for Y" part is naturally a NOT IN against the set of instructors qualified for COMP 456:

SELECT i.instr_id, i.name
FROM   instructors i
JOIN   qualified q ON q.instr_id = i.instr_id
JOIN   courses  c ON c.course_id = q.course_id
WHERE  c.code = 'COMP 378'
  AND  i.instr_id NOT IN (
        SELECT q2.instr_id
        FROM   qualified q2
        JOIN   courses c2 ON c2.course_id = q2.course_id
        WHERE  c2.code = 'COMP 456');

The inner query runs once, returning the ids of every instructor qualified for COMP 456. The outer query keeps COMP 378 instructors whose id is not in that set. Result: Dr. Olsen (qualified for 378, but not for 456).

The NOT IN / NULL trap

One sharp edge: if the subquery's value set contains a NULL, NOT IN returns no rows at all (every comparison becomes UNKNOWN). When the inner column is nullable, guard it (WHERE col IS NOT NULL) or prefer NOT EXISTS (next lesson), which is null-safe.