IN/NOT IN with subquery
IN tests membership in the list a subquery returns.
In this page:
Syntax
SELECT columns
FROM table_name
WHERE column IN (SELECT column FROM other_table WHERE condition);IN/NOT IN with subquery
WHERE col IN (SELECT ...) keeps rows whose value appears in the subquery result. NOT IN keeps the rest, but if the subquery result contains any NULL, NOT IN returns no rows at all, because comparisons with NULL are unknown.
Filter out NULLs inside the subquery or use NOT EXISTS.
Note:
Add WHERE col IS NOT NULL inside a NOT IN subquery.
Example: IN/NOT IN with subquery
CREATE TABLE a (v INTEGER);
CREATE TABLE b (v INTEGER);
INSERT INTO a VALUES (1), (2), (3), (4);
INSERT INTO b VALUES (2), (4), (NULL);
SELECT v FROM a WHERE v IN (SELECT v FROM b) ORDER BY v;
SELECT v FROM a WHERE v NOT IN (SELECT v FROM b);
SELECT v FROM a WHERE v NOT IN (SELECT v FROM b WHERE v IS NOT NULL) ORDER BY v;
-- Output:
-- v
-- 2
-- 4
-- v
-- v
-- 1
-- 3Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- NOT IN returning nothing because of NULLs
- Using IN with huge subqueries instead of joins
- Returning several columns in the subquery
Chapter Summary
- IN uses a subquery's list
- NOT IN fails with NULL in the list
- Filter NULLs or use NOT EXISTS
- The subquery must return one column
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: