Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

Try it

SQL Customers who ordered since May 2024 ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL NOT IN returns nothing when the subquery has a NULL ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Fixing it: filter out the NULLs ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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 if x = 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: add WHERE col IS NOT NULL inside 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 JOIN would multiply the rows, which is why IN is often cleaner for "has at least one".
  • Performance. Modern optimizers treat IN (subquery) and EXISTS similarly (a semi-join). Index the column used in the subquery.
  • Only columns you select. Unlike EXISTS, IN compares real values, so the select list matters.
  • Correlated forms. The subquery may reference the outer row, but then EXISTS is usually clearer.
  • Short literal lists. IN ('Fiction', 'Poetry') is covered on IN.