Querying — 2.0 select() style

Updated 18 Aug 2026 6 interview angles 5 min read source

Querying — 2.0 select() style

SQLAlchemy 2.0 unified Core and ORM queries around select(). The 1.x session.query() API still works but is “legacy.” New code should use the modern style.

The basic pattern

python

from sqlalchemy import select

# All users
stmt = select(User)
users = session.scalars(stmt).all()

# Filter
stmt = select(User).where(User.is_active == True)
users = session.scalars(stmt).all()

# One row or None
stmt = select(User).where(User.email == "alice@example.com")
user = session.scalars(stmt).one_or_none()

# Exactly one (raises if 0 or 2+)
user = session.scalars(stmt).one()

# Just the first row (no error if multiple)
user = session.scalars(stmt).first()

`execute()` vs `scalars()`

This trips people up.

python

stmt = select(User)

# execute() returns Row objects:
result = session.execute(stmt)
for row in result:
    user = row[0]     # or row.User
    print(user.name)

# scalars() unwraps each Row to its first column (or the only entity):
for user in session.scalars(stmt):
    print(user.name)

For “select model instances,” scalars() is what you want. For “select columns / multiple entities” use execute():

python

# selecting tuples, not the model
stmt = select(User.id, User.name)
for row in session.execute(stmt):
    print(row.id, row.name)

Method	Use when
`session.execute(stmt)`	columns, tuples, multiple entities
`session.scalars(stmt)`	single entity per row (the common case)

Result terminators

After execute() or scalars():

Method	Returns
`.all()`	list (materializes everything)
`.first()`	first row or None
`.one()`	exactly one row; raises if 0 or 2+
`.one_or_none()`	one row or None; raises if 2+
`iter(result)`	iterate without materializing all at once
`.partitions(N)`	yield chunks of N rows
`.unique()`	dedupe rows (needed when joins produce duplicates)

WHERE — comparison operators

python

select(User).where(User.id == 42)
select(User).where(User.name != "alice")
select(User).where(User.age > 18)
select(User).where(User.email.like("%@example.com"))
# case-insensitive
select(User).where(User.email.ilike("%@EXAMPLE.com"))
select(User).where(User.name.in_(["alice", "bob", "carol"]))
select(User).where(User.name.not_in(["spam", "test"]))
# IS NULL
select(User).where(User.deleted_at.is_(None))
select(User).where(User.deleted_at.is_not(None))
select(User).where(User.bio.contains("python"))                # LIKE %python%
select(User).where(User.tags.any(Tag.name == "vip"))           # for collection relationship
select(User).where(User.posts.any())                           # has at least one post

Multiple where() calls AND together:

python

select(User).where(User.is_active == True).where(User.age >= 18)

For explicit AND/OR/NOT:

python

from sqlalchemy import and_, or_, not_

select(User).where(
    and_(
        User.is_active == True,
        or_(User.age >= 18, User.has_consent == True),
    )
)

The &, |, ~ operators also work on column expressions (but parentheses matter due to operator precedence):

python

select(User).where((User.age >= 18) | (User.has_consent == True))

ORDER BY, LIMIT, OFFSET

python

select(User).order_by(User.created_at.desc())
select(User).order_by(User.created_at.desc(), User.name)       # multiple
select(User).limit(10).offset(20)                              # pagination

For keyset / cursor pagination (much better at scale than LIMIT/OFFSET):

python

select(User).where(User.id > last_seen_id).order_by(User.id).limit(50)

JOINs

python

# Implicit join via relationship — SQLAlchemy infers the ON clause
stmt = select(Post).join(Post.author).where(User.name == "alice")

# Explicit
stmt = select(Post).join(User, Post.author_id == User.id)

# LEFT OUTER JOIN
stmt = select(User).outerjoin(Post, User.id == Post.author_id)

Selecting from multiple tables:

python

stmt = select(User.name, Post.title).join(Post, Post.author_id == User.id)
for name, title in session.execute(stmt):
    print(f"{name}: {title}")

Aggregates and GROUP BY

python

from sqlalchemy import func

stmt = (
    select(User.id, func.count(Post.id).label("post_count"))
    .join(Post, Post.author_id == User.id)
    .group_by(User.id)
    .having(func.count(Post.id) > 5)
)

for user_id, count in session.execute(stmt):
    print(user_id, count)

func.<anything> is the SQL function namespace (works for any DB function: func.now(), func.coalesce(...), func.json_build_object(...)).

Subqueries

python

from sqlalchemy import select

# Subquery in WHERE
active_user_ids = select(User.id).where(User.is_active == True).scalar_subquery()
stmt = select(Post).where(Post.author_id.in_(active_user_ids))

# Subquery as a derived table
post_counts = (
    select(Post.author_id, func.count(Post.id).label("n"))
    .group_by(Post.author_id)
    .subquery()
)
stmt = (
    select(User.name, post_counts.c.n)
    .join(post_counts, post_counts.c.author_id == User.id)
)

.scalar_subquery() for “a subquery that returns one value/column” (for IN, scalar comparisons). .subquery() for “a subquery used as a derived table in FROM.”

CTEs:

python

cte = (
    select(Post.author_id, func.count(Post.id).label("n"))
    .group_by(Post.author_id)
    .cte("post_counts")
)
stmt = select(User.name, cte.c.n).join(cte, cte.c.author_id == User.id)

EXISTS

python

from sqlalchemy import exists

# Users with at least one post
stmt = select(User).where(
    exists().where(Post.author_id == User.id)
)

# Easier idiom for relationships:
stmt = select(User).where(User.posts.any())
stmt = select(User).where(User.posts.any(Post.is_published == True))

UPDATE and DELETE

2.0 style for bulk updates:

python

from sqlalchemy import update, delete

session.execute(
    update(User)
    .where(User.email == "alice@old.com")
    .values(email="alice@new.com")
)

session.execute(
    delete(User).where(User.deleted_at < cutoff)
)

session.commit()

These bypass the unit of work — they don’t load objects, don’t trigger cascades, don’t run Python-side hooks. For “delete 100k stale rows,” this is what you want. For “delete one user and their cascaded posts via Python,” use session.delete(user).

RETURNING (Postgres / SQLite)

python

from sqlalchemy import insert

stmt = insert(User).values(name="alice", email="alice@example.com").returning(User.id, User.created_at)
result = session.execute(stmt)
new_id, created_at = result.one()

Useful for getting DB-computed columns (auto-IDs, server defaults) without a second SELECT.

Composing queries — build incrementally

python

stmt = select(User)
if name_filter:
    stmt = stmt.where(User.name.ilike(f"%{name_filter}%"))
if active_only:
    stmt = stmt.where(User.is_active == True)
stmt = stmt.order_by(User.id).limit(50)

users = session.scalars(stmt).all()

Statements are immutable; each method returns a new statement. This makes them composable and safe to pass around.

1.x style — what you’ll see in older code

python

# Equivalent of select(User).where(User.is_active == True):
users = session.query(User).filter(User.is_active == True).all()

# Equivalent of session.get(User, 42):
user = session.query(User).get(42)

Both styles work; they hit the same code paths underneath. Don’t mix them randomly within one codebase — pick one (2.0 for new code).

Common pitfalls

Forgetting scalars() when selecting a model — session.execute(select(User)) returns Row objects, not User instances. Iterating gives you tuples.
session.execute(select(User)).all() returns list of Row, not list of User. Use scalars().all().
.unique() not called when join produces duplicates due to one-to-many JOIN — same parent row appears N times. SQLAlchemy raises MultipleResultsFound in some cases; otherwise you get duplicates.
.first() returns None not error when no rows match. Use .one() if you expect exactly one (and want the exception).
.like("%foo%") is case-sensitive in Postgres. Use .ilike("%foo%") for case-insensitive, or func.lower(column) == "foo".

Common interview confusions

“session.query() is gone in 2.0.” — still works, called “legacy.” 2.0 just discourages new use.
“select(User).all() works.” — select() returns a Statement, not a Result. You need session.scalars(stmt).all().
“.where() replaces previous WHERE.” — accumulates. Each .where() ANDs another clause.

Interview angle 6

“What’s the difference between session.execute() and session.scalars()?” — execute() returns Row objects (tuple-like); scalars() unwraps each row to its first column (typically the model instance). For “give me User objects,” use scalars().
“How do you build a query that conditionally adds filters?” — start with stmt = select(User), then if cond: stmt = stmt.where(...). Statements are immutable; each method returns a new one.
“Difference between .one(), .first(), .one_or_none(), .all()?” — one() requires exactly 1 (else raises), first() returns 1 or None, one_or_none() allows 0 or 1 (raises on 2+), all() materializes all.
“How do you JOIN to a related table?” — select(Post).join(Post.author) (uses the relationship to infer the ON clause), or explicit join(User, Post.author_id == User.id).
“update()/delete() constructs vs session.delete()?” — bulk Core constructs bypass the unit of work — no cascades, no Python hooks, much faster for many rows. session.delete(instance) is the per-object path that runs cascades.
“How do you use RETURNING with SQLAlchemy?” — insert(...).returning(Model.col1, Model.col2) then session.execute(stmt).one(). Returns server-computed columns without a second SELECT.

Querying — 2.0 select() style

Querying — 2.0 select() style

The basic pattern

execute() vs scalars()

Result terminators

WHERE — comparison operators

ORDER BY, LIMIT, OFFSET

JOINs

Aggregates and GROUP BY

Subqueries

EXISTS

UPDATE and DELETE

RETURNING (Postgres / SQLite)

Composing queries — build incrementally

1.x style — what you’ll see in older code

Common pitfalls

Common interview confusions

Interview angle 6

`execute()` vs `scalars()`