Introduction

In SQLAlchemy, the SQL IN clause is expressed with the .in_() method. You use it when a column should match any value from a list, tuple, subquery, or other selectable source.

Basic ORM Example

With a mapped model:

1from sqlalchemy import select
2from sqlalchemy.orm import Session
3
4ids = [1, 3, 5]
5
6stmt = select(User).where(User.id.in_(ids))
7
8with Session(engine) as session:
9    rows = session.scalars(stmt).all()
10    print(rows)

This generates SQL conceptually similar to:

SELECT * FROM user WHERE id IN (1, 3, 5)

The same pattern works with string columns, enums, and other comparable types.

Core Table Example

You do not need the ORM to use IN. SQLAlchemy Core looks similar:

1from sqlalchemy import select
2
3stmt = select(users_table).where(users_table.c.status.in_(["active", "pending"]))
4print(stmt)

The important idea is the same: call .in_(...) on the column expression.

Use not_in Logic with ~

To express NOT IN, invert the expression:

stmt = select(User).where(~User.id.in_([2, 4, 6]))

That is the normal SQLAlchemy style for negating the IN predicate.

IN with a Subquery

The right-hand side does not have to be a Python list. It can also be a subquery:

subq = select(Order.user_id).where(Order.total > 100)
stmt = select(User).where(User.id.in_(subq))

This corresponds to the SQL pattern:

WHERE user.id IN (SELECT order.user_id FROM order WHERE order.total > 100)

This is useful when the membership set comes from the database itself.

Empty Lists Need Attention

An important edge case is an empty Python list:

ids = []
stmt = select(User).where(User.id.in_(ids))

SQLAlchemy handles this case safely, but the resulting query will be designed to match no rows. That is usually correct behavior, though it can surprise people who expected a syntax error or special-case handling.

Sometimes it is still clearer to branch explicitly in application code if an empty list means "skip the query entirely."

Performance Considerations

Small IN lists are common and fine. Very large lists can make queries bulky and sometimes slower, depending on the database. If the list is extremely large, alternatives may be better:

• temporary tables
• joins
• bulk-loaded staging tables

So .in_() is great for ordinary filtering, but not automatically the best answer for massive membership sets.

SQLAlchemy Still Uses Bound Parameters

One advantage of .in_() is that you stay inside SQLAlchemy's normal parameter handling instead of building SQL strings manually. That keeps the query safer and more portable across database backends.

So even though the generated SQL looks familiar, it is still being built through SQLAlchemy expressions rather than string concatenation.

Common Pitfalls

The biggest pitfall is forgetting the underscore and writing .in(...) instead of .in_(...). In SQLAlchemy, the method name is in_.

Another common mistake is building raw SQL strings manually instead of using bound parameters through SQLAlchemy expressions. .in_() lets SQLAlchemy handle that safely.

People also overlook the empty-list case. Even though SQLAlchemy handles it, you should still decide whether "no values" means "no rows" or "skip this filter" in your application logic.

That decision is business logic, not only SQL syntax, and making it explicit usually keeps query code easier to reason about.

Summary

• Use .in_(...) to express a SQL IN condition in SQLAlchemy.
• The right-hand side can be a Python sequence or a subquery.
• Negate it with ~column.in_(...) for NOT IN.
• Empty lists are handled safely but still deserve explicit application-level thought.
• For very large value sets, consider whether a join or staging table would be more efficient.