Subqueries and CTEs

IN Subquery

Match a Set

IN compares a value to a set returned by a subquery.

Program

Play the query to find employees in active departments.

in_subquery.sql
Replay: real traced execution (multi-file project)
CREATE TABLE departments (id INTEGER, active INTEGER);
CREATE TABLE employees (name TEXT, department_id INTEGER);
INSERT INTO departments VALUES (10, 1), (20, 0);
INSERT INTO employees VALUES ('Ada', 10), ('Lin', 20), ('Mia', 10);
SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE active = 1) ORDER BY name;

  1. departments ← 0 rows

    1CREATE TABLE departments (id INTEGER, active INTEGER);2CREATE TABLE employees (name TEXT, department_id INTEGER);
    values this step0 rowsdepartments

  2. employees ← 0 rows

    1CREATE TABLE departments (id INTEGER, active INTEGER);2CREATE TABLE employees (name TEXT, department_id INTEGER);3INSERT INTO departments VALUES (10, 1), (20, 0);
    values this step0 rowsemployees

  3. departments ← 2 rows

    2CREATE TABLE employees (name TEXT, department_id INTEGER);3INSERT INTO departments VALUES (10, 1), (20, 0);4INSERT INTO employees VALUES ('Ada', 10), ('Lin', 20), ('Mia', 10);
    values this step2 rowsdepartments

  4. employees ← 3 rows

    3INSERT INTO departments VALUES (10, 1), (20, 0);4INSERT INTO employees VALUES ('Ada', 10), ('Lin', 20), ('Mia', 10);5SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE active = 1) ORDER BY name;
    values this step3 rowsemployees

  5. result ← 2 rows

    4INSERT INTO employees VALUES ('Ada', 10), ('Lin', 20), ('Mia', 10);5SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE active = 1) ORDER BY name;
    values this step2 rowsresult

Follow the Set

  1. Departments are 10 active 1 and 20 active 0.
  2. Employees are Ada in department 10, Lin in 20, and Mia in 10.
  3. The subquery returns active department id 10.
  4. IN keeps employees whose department is in that set.
  5. The final names are Ada and Mia. | employee | dept_id | active dept set contains it? | result | | --- | --- | --- | --- | | Ada | 10 | yes | kept | | Lin | 20 | no | filtered out | | Mia | 10 | yes | kept |
IN `IN (...)` checks membership in a set.
subquery set The inner query returns active department IDs.
filter Only employees whose department appears in that set remain.

Exercise: in_subquery.sql

Reproduce the final names Ada and Mia, then identify which active department id the subquery returns.