ITPEC FE Subject A October 2025 Question 21

SQL Subquery with IN — find employees whose department matches a condition in another table.

Query pattern:
SELECT employee_name FROM Employees WHERE department_id IN (SELECT department_id FROM Departments WHERE location = 'New York');

• •The subquery retrieves all department_id values from Departments where location = 'New York'
• •The outer query selects employees whose department_id is in that result set
• •IN checks membership in a list of values returned by a subquery

Why not others:
- (b) NOT IN — returns employees whose department is not in New York (opposite logic)
- (c) IN (SELECT ... FROM Employees) — wrong table; Employees has no location column
- (d) = ALL (SELECT ... FROM Employees) — wrong table + = ALL means "equal to every value in the list," which is logically meaningless here

Key rule: Use IN (subquery) when filtering rows based on a set of values from another table. Always check that the subquery references the correct table.

AI-generated — may contain errors