IN, ANY and ALL Subqueries
Set-membership subqueries and the famous NOT IN with NULL trap.
Set-Membership Subqueries
When a subquery returns a list of values, you test membership against it with IN, ANY, or ALL. These are how SQL asks is this value in that set? or does it beat every / any element of that set?
IN— matches any value in the list.ANY/SOME— true if the comparison holds for at least one element.ALL— true only if it holds for every element.
IN With a Subquery
The everyday case: find employees who work in any department located in 'NYC'. The subquery returns a set of department ids, and IN keeps rows that match any of them.
This reads naturally and is the form interviewers expect first.
SELECT name
FROM employees
WHERE dept_id IN (
SELECT id FROM departments WHERE city = 'NYC'
);