What does NOT IN do?
NOT IN is an operator that excludes rows if a column's value is in the given list.
It's the opposite of IN.
How it works
NOT IN checks that a value doesn't match any element from the list.
If even one match is found, the row is excluded from the result.
SELECT *
FROM employees
WHERE department NOT IN ('HR', 'Finance', 'Legal');This query selects every employee except those working in the HR, Finance, or Legal departments.
The same thing without NOT IN
Without it, you'd have to write:
WHERE department <> 'HR'
AND department <> 'Finance'
AND department <> 'Legal'NOT IN does the same thing, but simpler and more readable.
It can be used with a subquery
SELECT *
FROM customers
WHERE id NOT IN (SELECT customer_id FROM blacklist);Selects every customer not present in the blacklist table.
Important
If a NULL value shows up in the NOT IN list,
the result can end up empty, because SQL doesn't know whether to count NULL as a match.
To avoid this, it's worth excluding NULL from the subquery before filtering:
WHERE id NOT IN (SELECT customer_id FROM blacklist WHERE customer_id IS NOT NULL);Summary:
NOT IN filters data, removing every row whose value is in the given list.
It's used for exclusions and "blacklists" in queries.
Short Answer
Interview readyA concise answer to help you respond confidently on this topic during an interview.