IN Subquery

Source data — Pattern 8: Subqueries

All queries run against the shared employees and departments tables (see schema.sql). This pattern introduces scalar, correlated, and multiple-row subqueries. Topics 47 and 48 demonstrate the EXISTS and NOT EXISTS operators, while Topics 49 and 50 introduce the ANY (SOME) and ALL comparison operators.

---

What it does

An IN subquery returns multiple values. The outer query checks whether a value matches any value returned by the subquery.

Problem

Find employees working in departments located in Delhi.

SELECT first_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location = 'Delhi'
);

Result