SQL In

The SQL IN operator lets you test whether a value matches any value in a given list, or any value returned by a subquery. Instead of writing a long chain of OR conditions, IN lets you express the same filter in one compact, readable line. It is one of the most frequently used tools in a WHERE clause, and understanding exactly how it behaves — especially with subqueries and NULL values — will save you from some very confusing bugs later on.

Overview / How It Works

IN is shorthand for a series of equality checks joined by OR. When you write WHERE department IN ('Engineering', 'Sales'), the database engine evaluates it logically the same as WHERE department = 'Engineering' OR department = 'Sales'. The query planner treats these as equivalent, and for indexed columns, most modern engines (including SQLite, MySQL, and PostgreSQL) can use an index to look up each value in the list efficiently, similar to how it would for a single equality check.

IN has two forms. The first takes a literal, comma-separated list of values in parentheses. The second takes a subquery — a SELECT statement in parentheses — and checks whether the tested value appears anywhere in that subquery’s result set. This second form is extremely powerful: it lets you filter one table based on values that exist in a completely different table, without writing a JOIN. Internally, the engine typically executes the subquery first (or rewrites it into a semi-join during query planning), builds a lookup set of its results, and then evaluates each outer row’s value against that set.

IN can be negated with NOT IN, which returns rows whose value does not appear in the list or subquery result. This is convenient, but as you’ll see in the Common Mistakes section, NOT IN interacts with NULL in a way that surprises almost everyone at least once.

Syntax

-- Form 1: list of literal values
SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, value3);

-- Form 2: subquery
SELECT column1, column2
FROM table_name
WHERE column_name IN (SELECT column_name FROM other_table WHERE condition);

Part	Meaning
`column_name`	The column being tested against the list or subquery
`(value1, value2, ...)`	A literal list of values, comma-separated, wrapped in parentheses
`(SELECT ...)`	A subquery that must return a single column; its result rows form the comparison set
`NOT IN`	Negates the check — matches rows whose value is absent from the list/subquery

Examples

Example 1: Filtering with a list of values

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT,
  salary REAL
);

INSERT INTO employees (id, name, department, salary) VALUES
  (1, 'Alice Chen', 'Engineering', 95000),
  (2, 'Bob Diaz', 'Sales', 62000),
  (3, 'Carla Smith', 'Marketing', 58000),
  (4, 'David Lee', 'Engineering', 88000),
  (5, 'Erin Ford', 'HR', 54000);

SELECT name, department
FROM employees
WHERE department IN ('Engineering', 'Sales');

Result:

name	department
Alice Chen	Engineering
Bob Diaz	Sales
David Lee	Engineering

Only rows whose department matches one of the two listed values are returned — three of the five employees. Writing this with OR would require department = 'Engineering' OR department = 'Sales', which becomes unwieldy once the list grows past two or three values.

Example 2: NOT IN to exclude values

SELECT name, department
FROM employees
WHERE department NOT IN ('Engineering', 'Sales');

Result:

name	department
Carla Smith	Marketing
Erin Ford	HR

This time only the rows whose department is not in the list come back. NOT IN is the mirror image of IN — useful, but see the NULL warning below before you rely on it with subqueries.

Example 3: IN with a subquery

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT,
  country TEXT
);

CREATE TABLE orders (
  order_id INTEGER,
  customer_id INTEGER,
  amount REAL
);

INSERT INTO customers VALUES
  (1, 'Maria Gonzalez', 'USA'),
  (2, 'James Wright', 'UK'),
  (3, 'Sophie Turner', 'Canada');

INSERT INTO orders VALUES
  (101, 1, 250.00),
  (102, 1, 80.00),
  (103, 2, 500.00);

SELECT customer_name
FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders WHERE amount > 100
);

Result:

customer_name
Maria Gonzalez
James Wright

The inner query first finds which customer_id values placed an order over 100 — that’s customer 1 (order 101, amount 250) and customer 2 (order 103, amount 500). The outer query then returns any customer whose customer_id appears in that set. Sophie Turner is excluded because she has no matching order at all. This pattern — filtering one table by the existence of qualifying rows in another — is one of the most common real-world uses of IN.

How It Works Step by Step

When a query contains IN with a subquery, the logical processing order is:

1. Inner subquery runs first. The database evaluates the subquery’s own FROM, WHERE, and SELECT clauses to produce a result set — conceptually a list of values.
2. Outer FROM and WHERE evaluate next. For each row produced by the outer query’s FROM clause, the engine checks whether the tested column’s value exists in the subquery’s result set.
3. Matching rows survive. Rows where the value is found (for IN) or not found (for NOT IN) pass the filter and continue to GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT as usual.

In practice, query optimizers don’t always execute the subquery exactly once in isolation — they may rewrite IN (SELECT ...) into a semi-join and interleave execution for performance. But reasoning about it as “run the subquery, then filter the outer rows against its results” gives you the correct logical result every time, which is what matters when you’re writing and debugging queries.

Common Mistakes

Mistake 1: NOT IN silently returns nothing because of NULL

This is the single most common IN-related bug. If the subquery used with NOT IN can return a NULL, the entire condition can become impossible to satisfy.

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);
CREATE TABLE orders (
  order_id INTEGER,
  customer_id INTEGER
);

INSERT INTO customers VALUES (1, 'Maria Gonzalez'), (2, 'James Wright');
INSERT INTO orders VALUES (101, 1), (102, NULL);

-- Intended: customers with no orders at all
SELECT customer_name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Result: zero rows — even though James Wright (customer_id 2) never placed an order.

Why: the subquery returns (1, NULL). SQL compares 2 NOT IN (1, NULL) as 2 <> 1 AND 2 <> NULL. The second comparison evaluates to UNKNOWN (not TRUE), and ANDing anything with UNKNOWN can never produce TRUE, so the row is dropped. The fix is to exclude NULLs from the subquery, or use NOT EXISTS instead:

SELECT customer_name
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

Mistake 2: subquery returns more than one column

IN requires the subquery to return exactly one column. Returning more raises an error.

SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id, country FROM customers);

This fails with an error such as “sub-select returns 2 columns – expected 1”. The fix is simply to select only the one column the outer comparison needs: SELECT customer_id FROM customers.

Best Practices

Prefer IN over long chains of OR — it’s shorter, clearer, and just as fast (often faster) for the engine to plan.
Be very careful mixing NOT IN with a subquery that might contain NULL; add an IS NOT NULL filter to the subquery, or switch to NOT EXISTS, which doesn’t have this problem.
When the list of literal values is very large, consider whether a temporary table or a join against a lookup table would be clearer and faster to maintain than a huge IN (...) list.
Keep the subquery selecting a single, indexed column when possible — this helps the optimizer turn the IN into an efficient semi-join.
Use EXISTS instead of IN when you only care whether a matching row exists and don’t need to compare specific values — it can be more efficient on some engines for correlated checks.
Test NOT IN queries against real data that includes NULLs before trusting them in production.

Practice Exercises

Exercise 1: Using the employees table from Example 1, write a query that returns the names of employees who work in 'Marketing' or 'HR' using IN.
Exercise 2: Using the customers and orders tables from Example 3, write a query that returns the names of customers who have not placed any order, using NOT IN with a subquery that safely excludes NULL customer IDs.
Exercise 3: Rewrite the query from Exercise 2 using NOT EXISTS instead of NOT IN, and think about why this version doesn’t need the extra IS NOT NULL filter.

Summary

IN checks whether a value matches any value in a literal list or a subquery’s results — shorthand for multiple OR-joined equality checks.
The subquery form of IN must return exactly one column.
NOT IN negates the check, but returns zero rows if the list or subquery contains a NULL — a very common and confusing bug.
NOT EXISTS is often a safer alternative to NOT IN when NULLs might be present.
For indexed columns, IN is typically as efficient as an equivalent chain of OR conditions, and the subquery form can often be optimized into a semi-join.

SQL In

Overview / How It Works

Syntax

Examples

Example 1: Filtering with a list of values

Example 2: NOT IN to exclude values

Example 3: IN with a subquery

How It Works Step by Step

Common Mistakes

Mistake 1: NOT IN silently returns nothing because of NULL

Mistake 2: subquery returns more than one column

Best Practices

Practice Exercises

Summary

Table of Contents