Non-Correlated Subqueries: IN / NOT IN

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.