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
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;
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.