SQL Reference ยท Joins and Subqueries
SQL IN with a subquery
Reference for IN with a subquery in SQL: test whether a value appears in a query result, the NOT IN trap with NULL, and when to use EXISTS or a join.
expr IN (subquery) is true when the value of expr equals at least one value returned by the subquery. The subquery must return a single column. It is a compact way to say "rows whose key appears in this other set", for instance customers who have placed an order or books by authors from one country.
NOT IN is the opposite, and it hides a well-known trap: if the subquery returns even one NULL, NOT IN never returns true. The page below shows the effect and the safe alternatives.
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
expr [NOT] IN (SELECT column FROM ...) (expr1, expr2) [NOT] IN (SELECT col1, col2 FROM ...)
| Part | Description |
|---|---|
expr |
The value to look for. |
subquery |
A query returning one column (or as many columns as the row value on the left). |
Result: TRUE if a match is found, FALSE if the subquery has no match and no NULL, otherwise NULL (unknown). An empty subquery makes IN false and NOT IN true.
Dialect differences
| Database | Notes |
|---|---|
| SQLite | Supports IN (subquery) and row values (a, b) IN (SELECT ...) since 3.15. |
| PostgreSQL | Supports both forms. |
| MySQL | Supports both; older versions optimized IN subqueries poorly (before 5.6). |
| SQL Server | Supports single-column IN; no row-value IN with a subquery. Use EXISTS. |
| Oracle | Supports both; a list literal IN (...) is limited to 1,000 items, a subquery is not. |
Usage notes
- Three-valued logic.
x IN (1, NULL)is true ifx = 1, otherwise unknown.x NOT IN (1, NULL)is false for 1 and unknown for everything else, so it never selects a row. See NULL and three-valued logic. - Safe alternatives to
NOT IN: addWHERE col IS NOT NULLinside the subquery, or use NOT EXISTS, or an anti-join. - Duplicates don't matter. The subquery can return a value many times; the outer row still appears once. A plain
JOINwould multiply the rows, which is whyINis often cleaner for "has at least one". - Performance. Modern optimizers treat
IN (subquery)andEXISTSsimilarly (a semi-join). Index the column used in the subquery. - Only columns you select. Unlike
EXISTS,INcompares real values, so the select list matters. - Correlated forms. The subquery may reference the outer row, but then
EXISTSis usually clearer. - Short literal lists.
IN ('Fiction', 'Poetry')is covered on IN.
Related
- IN, EXISTS, ANY, SOME and ALL, anti-joins, scalar subqueries
- Lessons: IN and BETWEEN, Subqueries