skip to content

In SQLAlchemy 2.1, how do selectinload() and joinedload() differ in the SQL they emit, and which suits a collection versus a many-to-one?

level: middleimportance: must knowfreq 75%

answer

  1. second statement versus same statement
  2. IN list of parent primary keys
  3. LEFT OUTER JOIN to an alias
  4. unique() for joined collections

basics

~20 s

selectinload() runs a second SELECT whose IN list holds the parents' keys; joinedload() adds a LEFT OUTER JOIN to the main SELECT. The guide recommends selectinload() for collections and joinedload() for many-to-ones, and a joined collection needs unique().

solid answer

~40 s

`selectinload(Order.lines)` leaves the orders SELECT alone and, once a batch of orders is loaded, emits `SELECT ... FROM lines WHERE lines.order_id IN (...)` with up to 500 parent keys per statement. `joinedload(Order.customer)` adds a `LEFT OUTER JOIN customers AS customers_1` to the main statement (`innerjoin=True` makes it an inner join when the foreign key is NOT NULL). Joining a collection repeats each parent once per child row, so in the 2.0-style API the result must be called with `.unique()`, or SQLAlchemy raises `InvalidRequestError`. The query guide calls `selectinload()` generally the best choice for one-to-many and many-to-many collections, and `joinedload()` the most general choice for a many-to-one. Options chain, so `selectinload(Order.lines).joinedload(Line.product)` covers a whole order page in two statements.

code

python · 65 lines
python
from sqlalchemy import ForeignKey, create_engine, event, select
from sqlalchemy.orm import (DeclarativeBase, Mapped, Session, joinedload,
                            mapped_column, relationship, selectinload)


class Base(DeclarativeBase):
    pass


class Customer(Base):
    __tablename__ = "customers"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]


class Product(Base):
    __tablename__ = "products"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    customer: Mapped[Customer] = relationship()
    lines: Mapped[list["Line"]] = relationship()


class Line(Base):
    __tablename__ = "lines"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"))
    product_id: Mapped[int] = mapped_column(ForeignKey("products.id"))
    product: Mapped[Product] = relationship()


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as s, s.begin():
    customers = [Customer(name=f"c{i}") for i in range(5)]
    products = [Product(title=f"p{i}") for i in range(3)]
    for i in range(50):
        s.add(Order(customer=customers[i % 5],
                    lines=[Line(product=products[0]), Line(product=products[1])]))

statements = []
event.listen(engine, "before_cursor_execute",
             lambda conn, cur, stmt, params, ctx, many: statements.append(stmt))

stmt = select(Order).order_by(Order.id).limit(50).options(
    joinedload(Order.customer, innerjoin=True),               # many-to-one: JOIN
    selectinload(Order.lines).joinedload(Line.product),       # collection: SELECT ... IN
)
with Session(engine) as s:
    orders = s.scalars(stmt).all()
    for order in orders:
        order.customer.name, [line.product.title for line in order.lines]
    print(len(statements))  # 2
    for sql in statements:
        print(sql, end="\n\n")

with Session(engine) as s:
    joined = select(Order).options(joinedload(Order.lines))
    print(len(s.scalars(joined).unique().all()))  # 50; without unique() -> InvalidRequestError

go deeper

for a junior

Know that both options remove per-object lazy loads: selectinload() with a second SELECT and an IN list, joinedload() with a join in the same SELECT.

for a middle

Explain the SQL each emits, why a joined collection repeats parents and needs unique(), and the guide's advice: selectinload() for collections, joinedload() for many-to-ones.

for a senior

Chain options down a page's whole graph, read the resulting statements in the log, and explain the IN batch size, innerjoin=True and the LIMIT subquery wrapping.

for a principal

Weigh setting lazy='selectin' on mappings against per-query options: defaults reach every caller, options keep each read explicit but must be maintained.

## Two eager strategies, two shapes of SQL An **eager loader** fills a relationship while the main query runs, so later attribute reads emit nothing. SQLAlchemy 2.1 offers several; the two in daily use are `selectinload()` and `joinedload()`, applied per statement with `select(Order).options(...)` or set as a mapping default with `relationship(lazy="selectin")` or `lazy="joined"`. A per-query option always overrides the mapping for that statement. **`selectinload()` adds statements.** The main SELECT runs unchanged. After a batch of parent rows is loaded, SQLAlchemy collects their primary keys and emits one more SELECT per relationship: - For a one-to-many such as `Order.lines`, the query is `SELECT ... FROM lines WHERE lines.order_id IN (?, ?, ...)`; the child table's foreign key carries the parent ids, so no join is needed. - For a simple many-to-one such as `Order.customer`, the IN list holds the distinct foreign key values from the parent rows: `SELECT ... FROM customers WHERE customers.id IN (...)`. - At most 500 keys go into one IN list by default; SQLAlchemy 2.1 adds `selectinload(..., chunksize=...)` to change that. - A composite primary key needs the tuple form of IN, `WHERE (a, b) IN ((?, ?), ...)`; a backend without tuple IN support is the main case where `selectinload()` cannot be used. PostgreSQL, MySQL and current SQLite support it. **`joinedload()` changes the main statement.** It adds a `LEFT OUTER JOIN` to an aliased copy of the related table (`customers AS customers_1`) and fills the attribute from the same rows. `joinedload(Order.customer, innerjoin=True)`, or `innerjoin=True` on the relationship, uses an inner join when the foreign key is NOT NULL and a match always exists. ## Why a joined collection needs unique() When `joinedload()` targets a collection, each order row is repeated once per line. In the 2.0-style API that 2.1 keeps, ORM results are not de-duplicated automatically, so SQLAlchemy refuses to hand back the repeated parents silently: ```text InvalidRequestError: The unique() method must be invoked on this Result, as it contains results that include joined eager loads against collections ``` The fix is `session.scalars(stmt).unique().all()`, which de-duplicates by primary key. A joined many-to-one does not repeat parents and needs no `unique()`. When the statement has `LIMIT` or `OFFSET` and joins a collection, SQLAlchemy wraps the original SELECT in a subquery and applies the eager join outside it, so the limit still counts parents. Why a JOIN multiplies parent rows in any ORM is the ORM concepts material on collection fetch multiplication. ## Which one for which relationship | | `selectinload()` | `joinedload()` | |---|---|---| | SQL | extra SELECT with `IN` | `LEFT OUTER JOIN` in the same SELECT | | Statements | 1 + one per relationship, per 500 keys | 1 | | Parent rows repeated | no | yes, for a collection | | `unique()` needed | no | yes, for a collection | | Query guide's advice | collections (one-to-many, many-to-many) | many-to-one | The guide's reasoning: `selectinload()` touches as few tables as possible and leaves the original statement alone, which suits collections; `joinedload()` adds a single join and no extra round trip, which suits a reference to one row. `subqueryload()` still exists, but the guide marks it legacy: its second SELECT re-runs the original query as a subquery, and `selectinload()` is simpler and usually faster. ## Chaining options down a path Options chain one level at a time, and each link names its own strategy: 1. `selectinload(Order.lines)` loads every order's lines in one extra SELECT. 2. `.joinedload(Line.product)` joins `products` into that second SELECT, so each line arrives with its product. 3. A sibling option such as `joinedload(Order.customer, innerjoin=True)` fills the customer in the main SELECT. The whole order page, 50 orders with customers, lines and products, then runs in two statements instead of dozens. To reach a deeper relationship without changing how the one above it loads, start the chain with `defaultload()`. ## Mapping default or per-query option The same strategies can be set in two places, and the choice changes who pays: - **`relationship(lazy="selectin")` on the mapping** applies to every statement that loads the class, including ones that never read the relationship. It suits a small collection that almost every caller needs. - **An option on the statement** applies to that read only and documents, next to the query, what the page needs. It suits list pages whose needs differ. - **A wildcard** such as `selectinload("*")` sets the strategy for every relationship the statement does not name, which is blunt but useful in a one-off report. A per-query option always wins over the mapping for its statement. One option to retire rather than reach for: `noload()` is deprecated in SQLAlchemy 2.1, because it returns `None` for related items that do exist.

  • Why does joinedload() on a collection need unique() when joinedload() on Order.customer does not?
    A collection join returns one row per child, so each Order repeats; the 2.0-style result will not drop the repeats silently and raises InvalidRequestError until unique() is called. A many-to-one join returns at most one customer row per order, so there are no repeated parents to remove.
  • What happens to joinedload() of a collection when the statement also has limit(10)?
    SQLAlchemy wraps the original SELECT with its LIMIT in a subquery and applies the eager LEFT OUTER JOIN outside it, so the limit counts ten parents rather than ten joined rows.
  • When would you still reach for subqueryload()?
    Rarely. The guide keeps it mainly for an eager-loaded collection on a composite primary key against a backend that lacks the tuple IN that selectinload() needs; otherwise selectinload() is simpler and usually emits better SQL.

selectinload() is a clerk who first collects all the order numbers on the page, then fetches every matching line slip in one trip; joinedload() is stapling each customer card to its order before handing the stack over, fine for one card per order but wasteful when one order carries many slips.

  • joinedload() on a collection returns de-duplicated objects without unique() in 2.0-style results
  • selectinload() emits one SELECT per parent object, like lazy loading
  • joinedload() always uses an INNER JOIN
  • selectinload() re-runs the original query as a subquery
  • A LIMIT on a joined-collection query cuts off children rather than parents

Assumes SQLAlchemy 2.1; the explicit unique() call comes from the 2.0-style API (the legacy Query uniquified joined rows itself), and 2.1 adds selectinload(chunksize=...) and deprecates noload().