Choosing Between joinedload and selectinload in SQLAlchemy 2.0

The problem: one query becomes fifty-one

Imagine an API endpoint that lists recent orders and renders each order's line items. With SQLAlchemy's default lazy loading, fetching 50 orders issues one SELECT for the orders. As your template or serializer touches order.items for each row, SQLAlchemy issues another SELECT per order. That is the N+1 query problem: one parent query plus N child queries.

Lazy loading means SQLAlchemy defers loading a relationship until your code accesses the attribute. It is convenient for exploratory scripts, but expensive in loops.

joinedload: one query, but watch the rows

joinedload tells SQLAlchemy to fetch parents and related rows in a single query using an OUTER JOIN. It works well for scalar relationships such as order.customer, where each parent has at most one related row.

It is risky for collections such as order.items. Joining a parent table to a collection multiplies rows: an order with three items appears three times in the result set. SQLAlchemy de-duplicates objects in memory, but the database still sends, and your application still processes, the extra rows. Stack several joinedload options on different collections and you create a Cartesian product.

from sqlalchemy import select
from sqlalchemy.orm import joinedload, selectinload

# Good: scalar relationship
stmt = select(Order).options(joinedload(Order.customer))

# Risky: collection via join
stmt = select(Order).options(joinedload(Order.items))

selectinload: an extra query, no row multiplication

selectinload runs a second query with an IN clause over the parent primary keys. For collections, this avoids duplicated parent rows entirely. You pay one additional round-trip, but you avoid the memory and network cost of a wide joined result.

# Preferred for collections
stmt = select(Order).options(selectinload(Order.items))
result = session.execute(stmt)
orders = result.scalars().all()

Run this in your application code or a Python shell with database read access. The placeholder session is a SQLAlchemy 2.0 Session bound to your engine. Expected check: the returned orders list contains each order once, even when orders have many items. Risk: if you load thousands of parents at once, the IN clause can exceed your database's parameter limit.

How to decide and verify

Strategy	Queries	Best for	Main risk
joinedload	1	Scalar many-to-one relationships	Row duplication on collections
selectinload	2+	One-to-many collections	Extra round-trip; IN clause limits

Set create_engine(..., echo=True) and inspect the SQL. joinedload produces a single JOIN statement; selectinload produces a parent SELECT followed by SELECT ... WHERE parent_id IN (...). You can also run the query against an in-memory SQLite database and assert that len(result.scalars().all()) equals the expected parent count with no duplicated rows.

Catch accidental lazy loading in tests

Add raiseload('*') to a query in your test suite. If code outside the query accesses a relationship, SQLAlchemy raises an exception instead of silently issuing a lazy load. This turns N+1 bugs into test failures rather than production slowdowns.

from sqlalchemy.orm import raiseload

stmt = select(Order).options(selectinload(Order.items), raiseload('*'))

Limitations

• selectinload IN clauses can exceed database parameter limits when the parent result set contains thousands of rows. Chunk the parent query or fall back to joinedload for scalar links.
• Loader options apply per query unless you configure lazy='selectin' or lazy='joined' directly on the relationship mapper.
• With AsyncSession, use the same options but await execute(); do not mix sync Session methods in async code.

Closing

Start with echo=True on a representative request, count the queries, and inspect whether collections are being joined. Use selectinload for collections, joinedload for scalar relationships, and raiseload in tests to prevent regressions. The right choice depends on your graph shape, not a universal rule.