SQLAlchemy 2.0 #6 Advanced Queries: Joins, Aggregation, Subqueries, Bulk Operations

With models, sessions, and relationships in place, it is time to widen the query vocabulary. The good news about 2.0 is that nothing new appears here: everything is assembled on select(), in the same shape in Core and in the ORM. This part is a collection of the patterns you end up writing over and over. The examples continue with the User and Address models from part 5.

scalars vs execute: two shapes of results #

Getting the result-receiving rules straight first prevents most confusion.

from sqlalchemy import select

# querying one entity: scalars()
users = session.scalars(select(User).where(User.name.like("A%"))).all()
# → [User, User, ...] a list of objects

# querying multiple values: execute()
rows = session.execute(select(User.name, User.email)).all()
# → [('Alice', 'alice@example.com'), ...] a list of Rows
for name, email in rows:
    print(name, email)

One rule: if the SELECT target is a single entity, use scalars(); for multiple columns or an entity mixed with aggregates, use execute(). Query a single entity through execute() and each result comes wrapped in a one-element Row like (User,) — the single most common beginner stumble.

Single-row lookups have dedicated methods.

user = session.get(User, 1)                  # primary-key lookup; None if absent
user = session.scalars(stmt).first()         # first row or None
user = session.scalars(stmt).one()           # exception unless exactly one row

one() is for lookups that must return exactly one row (unique-condition queries). Zero rows or two rows both raise, so data anomalies surface early.

Combining conditions: and, or, in #

from sqlalchemy import or_

stmt = select(User).where(
    User.name.like("A%"),                    # listing conditions = AND
    User.id.in_([1, 2, 3]),
)

stmt = select(User).where(
    or_(User.name == "Alice", User.name == "Bob")
)

Conditions listed in where() combine with AND. Only OR needs wrapping in or_(). in_() accepts not just lists but subqueries too (covered below).

Joins: the relationship knows the ON clause #

# ORM join: the ON clause is inferred from the relationship declaration
stmt = (
    select(User.name, Address.email_address)
    .join(User.addresses)
    .where(Address.email_address.like("%@work.com"))
)

# no relationship, or an ambiguous one: state the ON clause
stmt = select(User.name, Address.email_address).join(
    Address, User.id == Address.user_id
)

# LEFT OUTER JOIN: include users without addresses
stmt = select(User.name, Address.email_address).join(User.addresses, isouter=True)

Pass a relationship attribute to join(User.addresses) and no ON clause is needed. This is easy to confuse with part 5’s eager loading (selectinload), but the roles differ: join() exists to use another table in the SQL’s WHERE and SELECT; selectinload() exists to pre-fill a relationship attribute. “Find users whose address is at work.com” is a join; “show a list of users, each with all their addresses” is selectinload.

Aggregation: group_by and label #

from sqlalchemy import func

stmt = (
    select(User.name, func.count(Address.id).label("address_count"))
    .join(User.addresses, isouter=True)
    .group_by(User.id)
    .having(func.count(Address.id) >= 2)
    .order_by(func.count(Address.id).desc())
)
for row in session.execute(stmt):
    print(row.name, row.address_count)  # accessible by name thanks to label

• func.anything() calls the SQL function of that name as-is — func.count, func.sum, func.max, and database-specific functions alike.
• label() makes the value readable by that name on the result Row. Make it a habit on aggregate columns.
• Conditions on aggregates go in having, not where — straight SQL rules.

Subqueries and EXISTS #

“Users who have at least one address” can be written two ways.

# IN + subquery
subq = select(Address.user_id)
stmt = select(User).where(User.id.in_(subq))

# EXISTS: correlated subquery
from sqlalchemy import exists

stmt = select(User).where(
    exists().where(Address.user_id == User.id)
)

To join against an aggregate, build a named derived table with subquery().

addr_count = (
    select(Address.user_id, func.count(Address.id).label("cnt"))
    .group_by(Address.user_id)
    .subquery()
)
stmt = (
    select(User.name, addr_count.c.cnt)
    .join(addr_count, User.id == addr_count.c.user_id)
)

Subquery columns are accessed as subq.c.column_name — the same interface as Core’s Table.c.

Pagination: LIMIT-OFFSET and its limits #

page, per_page = 3, 20
stmt = (
    select(User)
    .order_by(User.id)                 # without a fixed order, pages shuffle
    .limit(per_page)
    .offset((page - 1) * per_page)
)

Two things to remember. First, pagination without order_by is undefined behavior. The database guarantees no order, so rows can repeat or vanish between pages. Second, OFFSET still reads the rows it skips. Deep pages (OFFSET 100000) get proportionally slow, so for infinite-scroll interfaces, keyset pagination — filtering on “greater than the last seen id” — is the better tool.

# keyset pagination: constant speed regardless of depth
stmt = select(User).where(User.id > last_seen_id).order_by(User.id).limit(per_page)

Bulk operations: bypassing the unit of work #

Insert tens of thousands of rows via session.add() and the change-tracking cost piles up accordingly. For bulk work, the right move is to give up ORM conveniences and execute expressions directly.

from sqlalchemy import insert, update

# bulk INSERT: executemany from a list of dicts
session.execute(
    insert(User),
    [{"name": f"user{i}", "email": f"user{i}@example.com"} for i in range(10_000)],
)

# bulk UPDATE: every matching row in a single statement
session.execute(
    update(User).where(User.name.like("test%")).values(name="cleaned")
)
session.commit()

This path creates no objects and skips the session’s identity map. That makes it fast, at a price: objects already loaded in the session do not see these changes automatically. If the same session needs those rows again after a bulk UPDATE, re-query after commit to be safe.

Summary #

• Receive single entities with scalars() and mixed columns with execute(). For single rows, use get(), first(), and one() by intent.
• Joins infer the ON clause from relationship attributes. join() is for filtering; selectinload() is for loading relationship attributes — different jobs.
• Aggregation is func + label + group_by, with aggregate conditions in having. Subqueries assemble via in_(), exists(), and subquery().
• Pagination requires order_by, and deep pages should use keyset instead of OFFSET.
• Bulk INSERT and UPDATE bypass session change tracking by executing expressions directly — just mind the mismatch with already-loaded objects.
• The final part covers Alembic for schema changes, async support, and production project layout.