Chapter 2 used IN with a list that you type: WHERE dept_name IN ('Math', 'Physics'). A subquery can make the list:
sql
SELECT name
FROM student
WHERE id IN (SELECT id FROM takes WHERE course_id = 'CS-340');SELECT id FROM takes WHERE course_id = 'CS-340'
| id |
|---|
| S201 |
| S202 |
student
| id | name |
|---|---|
| S201 | Leo Martins |
| S202 | Ines Duarte |
| S203 | Kai Nakamura |
- The subquery returns one column. Any number of rows.
INis true when the value equals at least one value of the list.
IN or a join
A join can give the same students. The difference is repetition. A join returns one row for each matching pair. Students who take two Winter 2026 courses appear twice:
| Query | Rows for Winter 2026 |
|---|---|
student JOIN takes … WHERE semester = 'Winter' AND year = 2026 | 7 (Ines Duarte twice) |
student WHERE id IN (SELECT id FROM takes WHERE …) | 6 (each student once) |
Use IN when the question is "which rows of this table have a partner there?".