IN Operator in SQL
Introduction
The SQL IN operator tests whether an expression matches any member of a specified set. That set can be a literal list written in the query, a list supplied safely by an application, or the single-column result of a subquery. IN is especially useful when several equality conditions apply to the same expression.
SELECT employee_id, employee_name, department
FROM employees
WHERE department IN ('IT', 'HR', 'Finance');
This query returns employees whose department equals any of the three listed values. The same rule could be written as repeated OR conditions, but IN states the business idea more directly: department must belong to the approved set. Correct use still depends on compatible data types, deliberate NULL behavior, secure parameter handling, and suitable alternatives when the set becomes large or dynamic.
Basic IN Syntax
expression IN (value1, value2, value3)
The database compares the expression with each candidate using equality semantics. If at least one comparison is TRUE, the IN predicate is TRUE. If every comparison is FALSE, it is FALSE unless a NULL introduces SQL's UNKNOWN result, which is discussed later.
SELECT customer_id, customer_name
FROM customers
WHERE state_code IN ('IL', 'WI', 'IN');
Values should be compatible with the tested expression. Numeric IDs normally use numeric parameters, dates use typed date parameters, and business codes use the same character type and collation rules as the stored column.
Why the IN Operator Exists
Without IN, a same-column membership test becomes repetitive:
WHERE status = 'ACTIVE'
OR status = 'PENDING'
OR status = 'REVIEW'
IN expresses the same ordinary non-NULL logic more compactly:
WHERE status IN ('ACTIVE', 'PENDING', 'REVIEW')
The shorter form reduces repeated column names and makes additions or removals easier to review. It also separates one membership rule from other conditions. IN is not inherently faster than equivalent OR predicates; modern optimizers may transform both into similar internal operations. Choose IN primarily because it represents the requirement clearly.
IN Means Match Any Listed Value
For known non-NULL values, the following formulas are equivalent:
category_id IN (10, 20, 30)
category_id = 10
OR category_id = 20
OR category_id = 30
A product with category 20 satisfies the predicate. A product with category 40 does not. The list defines discrete choices, not all values between the smallest and largest member.
IN versus OR
Use IN when alternatives compare the same expression for equality. Use OR when alternatives concern different columns, different operators, or independent business rules.
-- Clear same-column membership.
WHERE priority IN ('HIGH', 'URGENT')
-- Different conditions require OR.
WHERE priority = 'URGENT'
OR overdue_days > 30
Do not force unrelated conditions into an IN list. An expression such as status IN ('OPEN', total > 1000) mixes incompatible values and predicates. Parenthesize OR groups when combining them with mandatory tenant, ownership, or soft-delete conditions.
IN Is Not a Range Operator
IN matches exact members. It does not fill gaps between them. A score of 75 does not match score IN (60, 100). Use BETWEEN for a genuinely inclusive closed range, or explicit comparison operators for exclusive and mixed boundaries.
-- Exact allowed scores.
WHERE score IN (60, 80, 100)
-- Every score from 60 through 100.
WHERE score BETWEEN 60 AND 100
This distinction matters for dates as well. IN can select particular holidays or snapshot dates, while a reporting period should use range predicates.
Numeric, String, and Date Lists
IN works with comparable scalar values. Numeric membership is common for identifiers and category codes:
WHERE product_id IN (101, 205, 319)
String membership is useful for controlled statuses, regions, roles, and payment methods:
WHERE payment_method IN ('CARD', 'BANK_TRANSFER', 'WALLET')
Date membership selects exact calendar dates rather than a continuous period:
WHERE settlement_date IN (:first_close_date, :second_close_date)
Bind dates as date values instead of relying on locale-sensitive strings. For timestamps, exact IN membership is uncommon because fractional seconds and time-zone conversion make exact instants difficult to predict. Use timestamp ranges for periods.
Combining IN with AND
Membership rules often sit beside mandatory scope and lifecycle filters. AND requires all predicates to be true.
SELECT order_id, status, total_amount
FROM orders
WHERE tenant_id = :tenant_id
AND status IN ('PAID', 'PACKED', 'SHIPPED')
AND deleted_at IS NULL;
The tenant and soft-delete predicates are not optional decorations. They define which rows the caller may see. Keep them in repository methods, views, row-level policies, or another centralized boundary rather than trusting each caller to remember them.
Combining IN with OR
Operator precedence can change the meaning of a query. Parentheses make the intended group explicit:
WHERE tenant_id = :tenant_id
AND (status IN ('FAILED', 'CANCELLED')
OR requires_review = 1)
Without the parentheses, rows requiring review might bypass the tenant condition depending on the expression. Security-sensitive SQL should favor visible grouping over assumptions about precedence.
IN as a Business Allowlist
An IN list can model a small, stable set of accepted states. For example, cancellation may be legal only while an order is pending or confirmed:
UPDATE orders
SET status = 'CANCELLED', cancelled_at = CURRENT_TIMESTAMP
WHERE tenant_id = :tenant_id
AND order_id = :order_id
AND status IN ('PENDING', 'CONFIRMED');
This protects the transition inside the database statement and avoids a race between reading and updating. The application should still verify the affected-row count. Zero rows may mean the order does not exist, belongs to another tenant, or has already advanced to a disallowed state.
IN in UPDATE and DELETE
UPDATE and DELETE can use IN, but their consequences require a preview. First execute a SELECT with the identical WHERE clause, inspect representative rows, and compare the count with an expected maximum.
-- Preview.
SELECT order_id, status
FROM orders
WHERE tenant_id = :tenant_id
AND status IN ('TEST', 'DUPLICATE');
-- Perform only after validation and with a recovery plan.
DELETE FROM orders
WHERE tenant_id = :tenant_id
AND status IN ('TEST', 'DUPLICATE');
Use transactions, audit records, backups, approvals, and batch limits appropriate to the environment. An IN list does not make a destructive statement safe by itself.
IN in CASE and CHECK Constraints
CASE can group several discrete values into a shared output:
SELECT order_id,
CASE
WHEN status IN ('PAID', 'PACKED', 'SHIPPED') THEN 'ACTIVE'
WHEN status IN ('DELIVERED', 'CANCELLED') THEN 'CLOSED'
ELSE 'REVIEW'
END AS lifecycle_group
FROM orders;
A CHECK constraint can enforce a small stable domain:
status VARCHAR(20) NOT NULL
CHECK (status IN ('PENDING', 'PAID', 'CANCELLED'))
Combine CHECK with NOT NULL when missing values are forbidden. Because a CHECK usually rejects FALSE but may accept UNKNOWN, the IN constraint alone may not prevent NULL. For a domain that changes often or carries attributes, use a referenced lookup table rather than repeatedly changing hardcoded constraints.
IN with a Subquery
The right side of IN can be a subquery returning one comparable column. This allows membership to be derived from current data.
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE status = 'PAID'
);
The query returns customers whose identifiers appear among paid orders. The subquery may return many rows and duplicates; duplicates do not cause the outer customer row to repeat. IN asks whether a match exists, so it behaves conceptually like a semi-join.
Subquery Requirements
An ordinary scalar IN predicate expects one column from its subquery. The selected type should be compatible with the outer expression. Additional columns belong in subquery filters, not its projection.
WHERE product_id IN (
SELECT product_id
FROM order_items
WHERE order_date >= :period_start
AND order_date < :period_end
)
Some products support row-value membership such as (country_code, customer_no) IN (...), but support and optimization vary. A correlated EXISTS predicate is often a more portable way to test composite membership.
IN versus EXISTS
IN emphasizes that one value belongs to a returned set. EXISTS emphasizes that at least one related row satisfies a correlated condition.
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.status = 'PAID'
);
Both forms may produce equivalent results for positive existence tests, and optimizers often transform them into similar plans. EXISTS becomes especially clear when correlation involves several columns or when the relationship itself is the business concept. Do not assume one keyword is always faster; compare actual plans, row estimates, indexes, and production-scale data.
IN versus JOIN
Use IN or EXISTS when the related table only determines eligibility. Use JOIN when columns from both tables are needed in the result.
SELECT o.order_id, p.product_name
FROM orders o
JOIN products p ON p.product_id = o.product_id
WHERE p.category_id IN (10, 20);
A one-to-many JOIN can multiply outer rows. Developers sometimes add DISTINCT to hide that multiplication, but doing so can mask an incorrect relationship or add unnecessary work. If the requirement is merely “customers who have at least one paid order,” IN or EXISTS expresses it without join-created duplicates.
Duplicate Values in an IN List
Repeating a candidate does not change membership:
WHERE category_id IN (10, 10, 20)
This has the same logical result as IN (10, 20). Remove duplicates before generating dynamic lists because they increase SQL text and parameter count without adding meaning. Likewise, duplicate values returned by an IN subquery do not duplicate outer rows, though reducing unnecessary subquery work may still help performance.
NULL and Three-Valued Logic
SQL comparisons can be TRUE, FALSE, or UNKNOWN. NULL represents missing or unknown information, so column = NULL is not TRUE. The same rule affects IN.
WHERE assigned_team IN ('OPS', 'SUPPORT')
A NULL assigned_team does not match. To include missing assignments, state that requirement explicitly:
WHERE assigned_team IN ('OPS', 'SUPPORT')
OR assigned_team IS NULL
Adding NULL to the candidate list does not match NULL:
-- This does not select rows where assigned_team is NULL.
WHERE assigned_team IN ('OPS', 'SUPPORT', NULL)
Use IS NULL or IS NOT NULL for missing-value tests.
How NULL Inside IN Is Evaluated
IN behaves like an OR chain of equality comparisons. A TRUE comparison wins even if another candidate is NULL. If no comparison is TRUE and one comparison is UNKNOWN because of NULL, the overall result is UNKNOWN rather than FALSE.
5 IN (3, 5, NULL) -- TRUE because 5 = 5
5 IN (3, 4, NULL) -- UNKNOWN because no TRUE match exists
5 IN (3, 4) -- FALSE
A WHERE clause retains only TRUE rows, so FALSE and UNKNOWN are both filtered out. This distinction becomes critical when IN is negated.
NOT IN and the NULL Trap
NOT IN tests that a value differs from every member. For known values, it resembles a chain of AND comparisons:
status NOT IN ('CANCELLED', 'DELETED')
status <> 'CANCELLED'
AND status <> 'DELETED'
If the list or subquery contains NULL, one comparison becomes UNKNOWN. When no equality match exists, NOT UNKNOWN remains UNKNOWN, so rows that appear eligible may disappear.
-- Dangerous if blocked_customer.customer_id can contain NULL.
WHERE customer_id NOT IN (
SELECT customer_id FROM blocked_customer
)
This is one of the most important SQL interview and production rules: a single NULL from the subquery can prevent every nonmatching row from passing.
Safer Anti-Existence with NOT EXISTS
NOT EXISTS expresses absence directly and avoids the nullable-set surprise:
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM blocked_customer b
WHERE b.customer_id = c.customer_id
);
Alternatively, filter NULL explicitly in a NOT IN subquery only when that behavior is correct and enforced:
WHERE customer_id NOT IN (
SELECT customer_id
FROM blocked_customer
WHERE customer_id IS NOT NULL
)
NOT IN is reliable for a small literal list of known non-NULL constants, but nullable data should trigger careful review.
Empty IN Lists
Many SQL dialects reject IN () as invalid syntax. Applications generating a dynamic list must define empty-set behavior before constructing SQL. For a positive IN filter, an empty requested set usually means no row can match. The application can return an empty result immediately or emit a deliberate always-false predicate such as WHERE 1 = 0.
For NOT IN, an empty set may mean every otherwise eligible row, which can be dangerous for updates and deletes. Handle empty collections in application logic rather than relying on generated syntax or accidental omission of the filter.
Parameterized Dynamic IN Lists
Never concatenate raw user text into an IN clause. Validate each item, enforce a reasonable count, and bind one parameter per value when the driver supports that pattern.
WHERE product_id IN (:product_id_1, :product_id_2, :product_id_3)
Parameterization protects syntax and preserves type information. It does not replace authorization: an authenticated caller who supplies another tenant's valid product ID must still be blocked by a tenant or ownership predicate.
WHERE tenant_id = :tenant_id
AND product_id IN (:id_1, :id_2, :id_3)
Database libraries differ in whether a collection can be bound as one array parameter. Follow the documented API instead of placing a comma-separated string into one scalar placeholder.
Alternatives to Very Large IN Lists
A list containing hundreds or thousands of values can increase parsing cost, SQL text size, network traffic, parameter-management complexity, and plan variability. Better options depend on the platform:
- Load identifiers into a temporary or staging table and join to it.
- Use a table-valued parameter where supported.
- Use a typed array parameter with a database-specific membership operator.
- Persist a selection set with a request identifier and join to its rows.
- Batch requests only when the business operation can safely be divided.
A table-based approach gives the optimizer statistics and provides a place to enforce uniqueness, ownership, and audit information. Measure rather than choosing an arbitrary universal list limit.
IN and Data Types
Candidate values should use the same logical type as the tested column. Comparing an integer identifier with character parameters may trigger implicit conversion, fail, or prevent efficient index access. Comparing decimal values with binary floating-point constants can create equality surprises. Store money and exact thresholds in exact numeric types.
Character membership follows collation rules. Case sensitivity, accents, trailing spaces, and normalization can affect whether values compare equal. Normalize controlled business codes at the input boundary and enforce one representation instead of applying a function to every row during every query.
IN Does Not Perform Pattern Matching
Wildcards inside IN are ordinary characters, not LIKE patterns:
-- Does not mean names beginning with A or B.
WHERE customer_name IN ('A%', 'B%')
-- Pattern matching requires LIKE conditions.
WHERE customer_name LIKE 'A%'
OR customer_name LIKE 'B%'
Likewise, IN cannot correctly search a comma-separated list stored in one column. Normalize repeated values into related rows and query them with joins, IN, or EXISTS.
Lookup Tables versus Hardcoded Lists
A literal IN list is appropriate for a small rule that is stable, local, and easy to audit. A lookup table is better when allowed values change without deployment, have labels or sort order, require effective dates, differ by tenant, or need referential integrity.
SELECT o.order_id, o.status
FROM orders o
JOIN allowed_status a
ON a.status_code = o.status
WHERE a.operation_code = 'CANCEL'
AND a.is_active = 1;
Do not turn every three-value rule into a configuration table, but do not scatter frequently changing status lists across application code, reports, constraints, and scheduled jobs.
IN Does Not Preserve List Order
SQL result sets are unordered unless ORDER BY is specified. The order of values inside IN does not control output order.
WHERE status IN ('URGENT', 'HIGH', 'NORMAL')
ORDER BY CASE status
WHEN 'URGENT' THEN 1
WHEN 'HIGH' THEN 2
WHEN 'NORMAL' THEN 3
ELSE 4
END,
ticket_id;
Add a unique tiebreaker for stable pagination. A membership predicate filters eligible rows; it neither sorts them nor removes duplicate rows already present in the source data.
IN with Pagination and Batch Retrieval
Batch APIs often fetch a selected collection of IDs. Keep authorization and lifecycle conditions alongside the list, then apply deterministic ordering.
SELECT document_id, title, updated_at
FROM documents
WHERE tenant_id = :tenant_id
AND document_id IN (:id_1, :id_2, :id_3)
AND deleted_at IS NULL
ORDER BY updated_at DESC, document_id DESC;
Do not assume that receiving three identifiers guarantees three rows. Values may be duplicated, missing, unauthorized, or deleted. Return explicit per-ID outcomes when the API contract requires callers to distinguish those cases.
IN with GROUP BY, HAVING, and DISTINCT
IN can filter source rows before aggregation or filter calculated groups afterward. WHERE is evaluated before grouping, so it is appropriate when only selected categories should contribute to totals.
SELECT region_code, COUNT(*) AS paid_order_count
FROM orders
WHERE status IN ('PAID', 'SHIPPED', 'DELIVERED')
GROUP BY region_code;
HAVING filters grouped results and can apply membership to a grouped expression or classification. Use it only when the condition genuinely depends on aggregation; ordinary row membership belongs in WHERE so fewer rows enter the grouping work.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) IN (1, 5, 10);
IN does not remove duplicates from selected data. If several qualifying orders have the same customer ID, all remain unless grouping or DISTINCT changes the result. Add DISTINCT only when the result contract requires unique projected values, not as a routine companion to IN.
Designing IN Filters for APIs and Reports
An API that accepts multiple statuses, regions, or IDs should document the maximum item count, allowed value format, duplicate handling, empty-list behavior, authorization rules, and whether unknown values cause rejection or are ignored. Normalize the collection once, then bind typed values through the database library.
For reports, display the applied selection in human-readable metadata. A report labeled “selected regions” is incomplete if readers cannot see which regions were included. Store stable business codes in the query while translating them to labels at the presentation boundary.
-- Report rows use trusted, normalized region codes.
WHERE organization_id = :organization_id
AND region_code IN (:region_1, :region_2, :region_3)
AND created_at >= :period_start
AND created_at < :period_end
Operational logs should record the collection size, normalized filter category, execution duration, and result count without exposing sensitive values unnecessarily. These details help distinguish an unexpectedly broad list, poor selectivity, authorization filtering, and a genuine query-performance regression.
IN and Indexes
An index on the tested column can support IN efficiently, especially for selective identifiers or codes. The optimizer may perform several seeks, combine ranges, or use another equivalent strategy. A composite index can also help when equality scope precedes membership:
WHERE tenant_id = :tenant_id
AND status IN ('PAID', 'SHIPPED')
An index beginning with tenant_id and then status may serve this pattern. Actual usefulness depends on distribution, returned columns, result size, table statistics, and competing query needs. A low-selectivity status list covering most rows may favor a scan.
Plans, Statistics, and Skew
Different list members can have very different frequencies. A status of ARCHIVED may cover millions of rows while FAILED covers only a few. Estimates based on uniform distribution can lead to poor joins, memory grants, or access methods.
Inspect estimated and actual row counts, reads, CPU, spills, and parameter behavior using representative lists. Keep statistics current and avoid functions or implicit conversions on the indexed column. Prepared statements with variable list lengths may generate different SQL shapes; monitor plan cache behavior according to the database and driver.
Partition Pruning
An IN predicate on a partition key may allow the database to skip unrelated partitions. For example, a region-partitioned table queried with a small region list can prune other regions. The values must be type compatible and the predicate must remain visible to the optimizer.
Pruning is product- and design-dependent. Confirm it in execution plans rather than assuming that partitioning automatically improves every membership query.
Security and Authorization
IN is useful for a positive allowlist, but authorization must be enforced from trusted server-side context. Never accept a client-supplied role list and treat it as proof of access.
WHERE organization_id = :authorized_organization
AND required_role IN ('REVIEWER', 'APPROVER')
Positive permission checks are usually easier to reason about than broad deny lists. New workflow states can accidentally become allowed by negative logic such as status NOT IN (...). Explicitly list states that permit a sensitive action, centralize the rule, and test new-state behavior.
Testing IN Predicates
A focused test set should include every listed member, a value outside the list, duplicate candidates, a NULL tested value, a NULL candidate, different type representations, case and collation variants, and an empty dynamic collection. For a subquery, test zero rows, one row, duplicates, many rows, and NULL output.
Mutation tests should verify mandatory scope, affected-row counts, transactions, and retries. API tests should include unauthorized but syntactically valid IDs, mixed authorized and unauthorized IDs, oversized lists, duplicate IDs, malformed values, and attempts at SQL injection.
Testing NOT IN Safely
Every NOT IN subquery test must include a NULL in the returned set, even if current production data supposedly forbids it. Verify the schema constraint that guarantees non-NULL values or prefer NOT EXISTS. Also test a NULL outer value because it normally does not pass NOT IN.
For destructive negative filters, add a test for newly introduced states. A query that deletes everything not in a known list can start deleting valid new rows after a release expands the domain.
Common IN Mistakes
- Using IN as though it represented a continuous range.
- Expecting
IN (..., NULL)to select NULL values. - Using NOT IN with a nullable list or subquery.
- Generating invalid
IN ()syntax for an empty collection. - Concatenating raw IDs, codes, or names into SQL.
- Passing a comma-separated string as one bound parameter.
- Creating huge literal lists when a table-based input is clearer.
- Assuming list order controls result order.
- Assuming IN removes duplicate rows from the source.
- Using wildcards inside IN instead of LIKE.
- Comparing incompatible types and relying on implicit conversion.
- Forgetting tenant, ownership, or soft-delete conditions.
- Assuming IN is always faster than OR, EXISTS, or JOIN.
Production Review Checklist
- Confirm that the requirement is discrete membership, not a range or pattern.
- Use values and parameters compatible with the tested expression.
- Define behavior for NULL tested values and NULL candidates.
- Prefer NOT EXISTS when an exclusion subquery can return NULL.
- Handle empty dynamic collections explicitly.
- Validate count, type, length, domain, and authorization of inputs.
- Bind parameters instead of concatenating SQL text.
- Keep mandatory scope predicates beside the membership condition.
- Use a lookup or input table when the set is large or frequently changing.
- Add ORDER BY when result order or pagination matters.
- Preview range-based mutations and verify affected-row counts.
- Inspect actual plans with representative list sizes and data skew.
Interview Questions and Answers
What does IN do? It tests whether an expression equals any member of a literal list or single-column subquery result.
How is IN related to OR? For ordinary known values, x IN (a, b) is equivalent to x = a OR x = b.
How does IN differ from BETWEEN? IN selects discrete exact members; BETWEEN selects every comparable value within an inclusive range.
Can IN use a subquery? Yes. The ordinary form expects one compatible projected column.
Does IN duplicate outer rows? No. Duplicate candidates do not multiply the outer result.
Why does NULL not match inside IN? Equality with NULL is UNKNOWN; use IS NULL explicitly.
Why is NOT IN dangerous with NULL? If no equal value exists but the candidate set includes NULL, the result can be UNKNOWN and filtered out.
When is NOT EXISTS safer? It is safer and clearer for anti-membership against nullable or correlated data.
Can IN use an index? Yes. Compatible membership predicates on indexed columns commonly support index-based access, subject to selectivity and cost.
Does IN preserve list order? No. Use ORDER BY for deterministic output.
What happens with an empty list? Many dialects reject IN (); application logic must define empty-set behavior.
IN or JOIN? Use IN or EXISTS for eligibility and JOIN when columns from related rows are required.
Key Takeaway
The IN operator is the clearest SQL expression for discrete membership. It replaces repetitive same-column equality conditions, supports data-driven subqueries, and works well for controlled states, selected identifiers, and positive allowlists.
RELIABLE MEMBERSHIP = DISCRETE VALUES
+ COMPATIBLE TYPES
+ EXPLICIT NULL POLICY
+ SAFE PARAMETERS
+ MANDATORY SECURITY SCOPE
+ DEFINED EMPTY-LIST BEHAVIOR
+ APPROPRIATE INDEXES
+ REPRESENTATIVE TESTS
Use IN when any listed value should match. Use ranges for continuous intervals, LIKE for patterns, EXISTS for relationship-focused membership, and NOT EXISTS when nullable anti-membership would make NOT IN unsafe.