Introduction

DynamoDB does support an IN operator, but only in expressions such as filter expressions and condition expressions. It is not a SQL-style general-purpose IN clause, and it cannot be used inside a KeyConditionExpression. That distinction matters because many developers expect IN to narrow the read before it happens, while in DynamoDB it often filters results after the key lookup or scan has already read them.

Where IN Is Supported

The IN operator can be used in filter expressions and condition expressions. For example, a Scan or Query may apply a filter that keeps only matching values:

1import boto3
2
3client = boto3.client("dynamodb")
4
5response = client.scan(
6    TableName="Products",
7    FilterExpression="#category IN (:books, :electronics)",
8    ExpressionAttributeNames={"#category": "Category"},
9    ExpressionAttributeValues={
10        ":books": {"S": "Books"},
11        ":electronics": {"S": "Electronics"},
12    },
13)
14
15print(response["Count"])

That works, but it does not mean DynamoDB is doing an efficient keyed lookup on arbitrary values. A filter expression is applied after the items are read.

IN Is Not for Key Conditions

This is the limitation that usually matters most. You cannot write a key condition like:

partitionKey IN (...)

A Query still requires an exact partition key equality condition, plus an optional sort-key condition using the operators allowed for key conditions.

So if you need items for several partition key values, the usual options are:

• issue multiple Query requests
• redesign the access pattern
• use BatchGetItem if you already know exact keys

That is a very different model from a relational database WHERE key IN (...) query.

Use IN Carefully with Query

You can combine a valid KeyConditionExpression with a filter expression that uses IN:

1response = client.query(
2    TableName="Orders",
3    KeyConditionExpression="CustomerId = :customer_id",
4    FilterExpression="#status IN (:pending, :shipped)",
5    ExpressionAttributeNames={"#status": "Status"},
6    ExpressionAttributeValues={
7        ":customer_id": {"S": "cust-123"},
8        ":pending": {"S": "PENDING"},
9        ":shipped": {"S": "SHIPPED"},
10    },
11)

This is valid, but remember what it means operationally: DynamoDB first reads the items that match the key condition, then applies the filter. Read capacity is consumed before the filter removes non-matching items.

When IN Is the Wrong Tool

If your main access pattern is "get items by one of several values", model the table or an index for that access path instead of relying on Scan plus IN.

For example, if you often need all products by category, a better schema might make category part of a key design or expose it through a GSI. Then you can query efficiently instead of reading a broad set of items and filtering afterward.

IN is fine for refining a reasonably small result set. It is a poor substitute for access-pattern-driven schema design.

Condition Expressions Also Support IN

IN can be useful on write conditions too, for example when you want to allow an update only if a status is one of a small approved set.

1client.update_item(
2    TableName="Orders",
3    Key={"OrderId": {"S": "ord-100"}},
4    UpdateExpression="SET #status = :new_status",
5    ConditionExpression="#status IN (:pending, :queued)",
6    ExpressionAttributeNames={"#status": "Status"},
7    ExpressionAttributeValues={
8        ":new_status": {"S": "PROCESSING"},
9        ":pending": {"S": "PENDING"},
10        ":queued": {"S": "QUEUED"},
11    },
12)

That is often a clean way to enforce state-transition rules.

Common Pitfalls

The biggest mistake is assuming IN in DynamoDB behaves like SQL and can be used freely against key attributes in a single efficient query. It cannot.

Another issue is forgetting that filter expressions run after items are read. A query or scan with IN in a filter can still be expensive even if the final returned item count is small.

People also often reach for Scan plus IN before thinking about data modeling. In DynamoDB, access patterns should drive schema and index design. Expression tricks are rarely a substitute for that.

Finally, remember that reserved attribute names may need aliases through ExpressionAttributeNames, even when the logic itself is correct.

Summary

• DynamoDB supports IN in filter expressions and condition expressions.
• 'IN cannot be used in a KeyConditionExpression.'
• Filter expressions with IN do not reduce read cost before the read happens.
• For multi-key lookup patterns, use multiple queries, BatchGetItem, or better schema design.
• Treat IN as an expression tool, not as a replacement for DynamoDB access-pattern modeling.