SQLAlchemy Lazy vs Eager Loading: joinedload and selectinload
Understand why SQLAlchemy's default lazy loading causes N+1 queries, and how eager loading with joinedload and selectinload avoids the problem.

SQLAlchemy's default lazy loading issues a separate SELECT when you access a relationship that has not already been loaded. That is convenient for single-object access, but in bulk operations it creates the N+1 query problem. Switching to eager loading with joinedload or selectinload is the usual fix. This article explains how both strategies work, where they differ, and how to choose between them.
The N+1 Problem and Why Lazy Loading Is the Default
Consider two models: User and Address, where a user has many addresses.
from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy import Column, Integer, ForeignKey, String
Base = declarative_base()
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
name = Column(String)
addresses = relationship("Address", back_populates="user")
class Address(Base):
__tablename__ = "addresses"
id = Column(Integer, primary_key=True)
email = Column(String)
user_id = Column(Integer, ForeignKey("users.id"))
user = relationship("User", back_populates="addresses")If you load all users and then iterate over their addresses, lazy loading triggers a new query for each user:
users = session.query(User).all()
for user in users:
print(user.addresses) # one SELECT per userWith 100 users, you get 1 query for the users and 100 queries for addresses. This is the classic N+1 pattern. Lazy loading is the default because it avoids loading related data until you actually need it, which can be efficient for single-object access. But in bulk operations, it becomes a performance problem.
Eager Loading with joinedload: How It Works and When It Fits
joinedload tells SQLAlchemy to fetch the related objects in the same SQL query using a JOIN. For a many-to-one relationship, this is often a single LEFT OUTER JOIN. For a one-to-many collection, it produces a cartesian product of parent and child rows, which can become large if the collection is big.
from sqlalchemy.orm import joinedload
users = session.query(User).options(joinedload(User.addresses)).all()This generates a single SQL query that joins users with addresses. SQLAlchemy then deduplicates the parent rows in Python, so you get a list of User objects with populated addresses collections without extra queries.
joinedload is most effective for many-to-one references where each parent has exactly one related object. For example, loading an Address with its User:
addresses = session.query(Address).options(joinedload(Address.user)).all()This avoids the N+1 for the reverse direction. However, when the collection is large, the JOIN can produce a huge result set, increasing memory and network transfer. The deduplication step also adds CPU overhead.
Eager Loading with selectinload: A Second Query Strategy
selectinload takes a different approach. Instead of joining in the original query, it issues a second SELECT statement that loads all related objects for the parent set at once, using a WHERE ... IN clause on the foreign key.
from sqlalchemy.orm import selectinload
users = session.query(User).options(selectinload(User.addresses)).all()This results in two queries total: one for the users, and one for all addresses whose user_id is in the list of loaded user IDs. The second query is efficient because it uses a single IN clause, and the result is processed into collections in Python.
selectinload avoids the large cartesian product that joinedload can create for collections. It also works well for nested eager loading, because each level adds one extra query, not a massive join.
Comparing joinedload and selectinload in Practice
The choice between the two often comes down to the cardinality of the relationship and the shape of your data.
| Criterion | joinedload | selectinload |
|---|---|---|
| SQL queries | One query with JOIN | Two queries (parent + IN subquery) |
| Result set size | Can blow up with large collections | Linear with number of related rows |
| Best for | Many-to-one references | One-to-many collections |
| Nested eager loading | Can become unwieldy with deep joins | Each level adds a separate query |
| Database compatibility | Uses standard SQL joins | Uses standard SQL IN clauses |
| Memory on client | Higher due to duplicate parent rows | Lower, since no duplication |
For a many-to-one relationship like Address.user, joinedload is usually the better choice because the JOIN adds only one extra column set and does not multiply rows. For a one-to-many relationship like User.addresses, selectinload is generally safer because it avoids the row multiplication.
There is also a subtle difference in how filtering interacts with each strategy. Because joinedload loads related rows through a JOIN, filters that reference the joined table can affect the query shape. selectinload runs separately, so it does not interfere with the primary query's WHERE clause.
Common Pitfalls: Filtering, Collection Size, and Query Complexity
A frequent mistake is using joinedload on a collection and expecting a filter on the relationship to limit the loaded collection. For example:
# This filters the parent rows, not the collection
users = session.query(User).options(joinedload(User.addresses)).filter(User.addresses.any(email="a@b.com")).all()This query returns only users that have at least one matching address, but it still loads all addresses for each returned user. If you need to filter the collection itself, you need a separate relationship or use contains_eager with an explicit join, which is more complex.
selectinload avoids this pitfall because the collection loading is a separate query, so you can safely filter the parent query without affecting the related objects.
Another consideration is query complexity. If you have multiple collections on the same parent, joinedload can create a massive join with multiple tables, leading to a combinatorial explosion. selectinload keeps each collection loading separate, so the query count increases linearly with the number of collections, but each query stays simple.
Choosing the Right Strategy for Your Relationship
The decision should be based on the relationship type and the typical size of the related data.
- Use
joinedloadfor many-to-one references where you always need the related object. The extra JOIN is cheap and avoids a second round trip. - Use
selectinloadfor one-to-many collections where the collection can be large. The separate query is more predictable and avoids row multiplication. - If you need to eager load multiple collections, prefer
selectinloadto keep the query plan manageable. - If you are loading a single parent object and its small collection, either works;
joinedloadmight be marginally faster because it is one query, butselectinloadis often clearer.
A practical pattern is to define the loading strategy at the query level, not on the relationship itself, so you can vary it per use case. This keeps the default lazy but allows you to opt into eager loading where it matters.
For example, in a REST API endpoint that returns a user with their addresses, you would write:
user = session.query(User).options(selectinload(User.addresses)).filter(User.id == user_id).first()This gives you a single user and all addresses in two queries, which is predictable and efficient.
When you have deeply nested relationships, such as User.addresses.city, you can chain the loading options. Assuming Address has a city relationship:
from sqlalchemy.orm import selectinload
users = session.query(User).options(
selectinload(User.addresses).selectinload(Address.city)
).all()This will produce three queries: users, addresses, and cities. Each level is loaded independently, which is often more efficient than a deep join.
Remember that eager loading is not a substitute for proper indexing. The second query in selectinload uses a foreign key, so ensure that column is indexed. For joinedload, the join condition benefits from indexes on both sides. Without indexes, even eager loading will be slow.
Finally, be aware of the difference between eager loading and explicit joins. joinedload and selectinload are purely for loading related objects; they do not let you filter or modify the query based on the related table. If you need to filter on the related table, use join and contains_eager instead.
In summary, the default lazy loading is fine for single-object access but breaks down in bulk operations. joinedload and selectinload give you control over when related data is fetched. Choose joinedload for many-to-one and selectinload for collections, and always consider the size of the result set and the number of queries you are willing to execute.
Try matching browser tools
All toolsSmall utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related Python guides
All Python guidesPython iter(): How the Built-in Function Works
Explains Python's iter() built-in: how it creates iterators, the two-argument sentinel form, custom iterator classes, and common edge cases.
Python Pika: Queues, Exchanges, Routing Keys, and Acknowledgements
Learn how to use Python's Pika client with RabbitMQ: declare queues and exchanges, publish with routing keys, consume messages, and handle acknowledgements reliably.
Python Kafka: kafka-python vs Confluent Client
python kafka python vs confluent kafka: Compare kafka-python and Confluent Kafka Python clients: API differences, performance, feature coverage, and when to choose eac...
Python Kafka Offsets, Partitions, and Manual Commits
Learn how Kafka offsets and partitions work and how to manually commit offsets in Python consumers with kafka-python and confluent-kafka. Covers sync vs async commits, rebalances, and seeking.
More in Python
All Python guidesThe guides filed next to this one in Python, in library order.