skip to content

In SQLAlchemy Core, when do you call subquery(), cte(), scalar_subquery() or exists() on a select(), and what does each return?

level: middleimportance: should knowfreq 42%

answer

  1. FROM clause versus column expression
  2. the .c collection of a derived table
  3. WITH rendered at the top
  4. auto-correlation to the enclosing select
  5. correlate() when it guesses wrong

basics

~20 s

subquery() and cte() turn a select() into FROM-clause objects with a .c collection, rendered inline or in a WITH clause. scalar_subquery() and exists() turn it into column expressions, a value or a true/false test, that auto-correlate to the enclosing select.

solid answer

~40 s

A `Select` is neither a table nor a column, so you convert it for where it goes. `stmt.subquery("rev")` returns a `Subquery` and `stmt.cte("rev")` a `CTE`: both are `FromClause` objects you `join()` or `select_from()`, whose `.c` collection is keyed by the inner column names and labels; the CTE renders in a `WITH` clause at the top of the statement. `scalar_subquery()` returns a `ScalarSelect`, a `ColumnElement` you `.label()` in the columns clause or compare in `where()`, and `exists()` returns an `Exists` you negate with `~`. Both column forms **auto-correlate**: tables that the enclosing select already has in its FROM are dropped from the inner FROM. When every inner table would be dropped, compiling raises `returned no FROM clauses due to auto-correlation`, and `correlate(customers)` says which one to correlate.

code

python · 47 lines
python
from sqlalchemy import (Column, DateTime, ForeignKey, Integer, MetaData, Numeric,
                        String, Table, func, select)

metadata = MetaData()
customers = Table(
    "customers", metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(100), nullable=False),
)
orders = Table(
    "orders", metadata,
    Column("id", Integer, primary_key=True),
    Column("customer_id", ForeignKey("customers.id"), nullable=False),
    Column("placed_at", DateTime, nullable=False),
)
order_lines = Table(
    "order_lines", metadata,
    Column("id", Integer, primary_key=True),
    Column("order_id", ForeignKey("orders.id"), nullable=False),
    Column("quantity", Integer, nullable=False),
    Column("unit_price", Numeric(10, 2), nullable=False),
)

# FROM-clause object: a CTE with a .c collection named after its labels
revenue = (
    select(orders.c.customer_id,
           func.sum(order_lines.c.quantity * order_lines.c.unit_price).label("total"))
    .join(order_lines)
    .group_by(orders.c.customer_id)
    .cte("revenue")
)
# column expression: correlated to the enclosing customers automatically
latest = (
    select(func.max(orders.c.placed_at))
    .where(orders.c.customer_id == customers.c.id)
    .scalar_subquery()
)
has_orders = select(orders.c.id).where(orders.c.customer_id == customers.c.id).exists()

report = (
    select(customers.c.name, revenue.c.total, latest.label("latest_order_at"))
    .outerjoin(revenue, revenue.c.customer_id == customers.c.id)
    .order_by(customers.c.name)
)
never_ordered = select(customers.c.name).where(~has_orders)
print(report)
print(never_ordered)

go deeper

for a junior

Recall the four methods by name and that subquery() and cte() give FROM objects while scalar_subquery() and exists() give column expressions.

for a middle

Explain the .c collection, labels on aggregates, how a CTE renders, and how auto-correlation removes outer tables from an inner FROM.

for a senior

Diagnose the auto-correlation error and the implicit coercion warning, and choose correlate() or correlate_except() deliberately.

for a principal

Set expectations that report queries label every derived column and convert selects explicitly, so reviews can read nesting at a glance.

## Four conversions for one `Select` A `Select` describes a whole statement. To nest it in another statement you convert it into the kind of object the position needs, and SQLAlchemy has two families: | Method | Returns | Family | Where it goes | |---|---|---|---| | `subquery(name=None)` | `Subquery` | `FromClause` | `join()`, `join_from()`, `select_from()` | | `cte(name=None, recursive=False)` | `CTE` | `FromClause` | the same, rendered in a `WITH` clause | | `scalar_subquery()` | `ScalarSelect` | `ColumnElement` | the columns clause with `.label()`, or `where()` | | `exists()` | `Exists` | `ColumnElement` | `where()`, negated with `~` | The SQL meaning of derived tables, CTEs, scalar subqueries and EXISTS is the SQL lesson's subject; what matters here is which SQLAlchemy object each call gives you and how it behaves. ## FROM-clause objects: `subquery()` and `cte()` Both return objects that behave like a table: - **A `.c` collection.** `rev.c.customer_id` and `rev.c.total` refer to the inner select's columns, keyed by column name or by the name passed to `.label()`. Label every aggregate: an unlabelled `func.count()` gets a generated name in the SQL, such as `count_1`, which is awkward to refer to. - **A name.** `subquery("rev")` renders `(...) AS rev`; with no name, SQLAlchemy generates an anonymous one such as `anon_1`. - **Joins.** `select(customers.c.name, rev.c.total).join(rev, rev.c.customer_id == customers.c.id)` joins like any table. The subquery's columns keep the foreign key of the column they came from, so inference can also work, but an explicit ON clause reads better. `cte()` differs mainly in rendering: the body moves to a `WITH rev AS (...)` clause at the top of the outermost statement, and the FROM clause names `rev`. A CTE can select from another CTE, and both are rendered in one `WITH` list. `cte(recursive=True)` combined with `union_all()` builds a recursive CTE. ## Column expressions: `scalar_subquery()` and `exists()` `scalar_subquery()` wraps a select of one column in a `ScalarSelect`, part of the `ColumnElement` hierarchy, so it behaves like a column: `.label("latest_order_at")` in the columns clause, or `latest > cutoff` in `where()`. For each customer's latest order: ```python latest = ( select(func.max(orders.c.placed_at)) .where(orders.c.customer_id == customers.c.id) .scalar_subquery() ) stmt = select(customers.c.name, latest.label("latest_order_at")) ``` `exists()` on a select returns an `Exists`, a boolean column expression. `select(customers.c.name).where(~has_orders)`, with `has_orders = select(orders.c.id).where(orders.c.customer_id == customers.c.id).exists()`, lists customers who never ordered. The standalone function form, `exists().where(orders.c.customer_id == customers.c.id)`, builds the same construct with `SELECT *` inside. ## Auto-correlation Printed alone, the `latest` subquery above shows `FROM orders, customers`. Embedded in a select that already has `customers` in its FROM, SQLAlchemy **auto-correlates**: it removes `customers` from the inner FROM, so the inner `customers.id` refers to the outer row. This happens without any call on your part, and it is usually right. It goes wrong when the enclosing select contains every table the subquery references. If the outer statement joins `customers` to `orders` and the subquery uses both, auto-correlation removes both and compiling raises `InvalidRequestError`: `returned no FROM clauses due to auto-correlation; specify correlate(<tables>) to control correlation manually.` The fixes: 1. `scalar_subquery().correlate(customers)`: correlate only `customers`, keep `orders` inside. 2. `correlate_except(orders)`: correlate everything except `orders`. 3. `correlate(None)`: correlate nothing, for a subquery that must stand alone. ## Choosing between them For a sales report over `customers`, `orders` and `order_lines`, each conversion has a natural job: - **Revenue per customer**, an aggregate joined back to customers: `subquery()` for a one-off derived table, or `cte()` when you want it named once at the top or reused by another CTE. - **Each customer's latest order date**, one value per outer row: `scalar_subquery()` with `.label()`. - **Customers who never ordered**, a yes-or-no test per row: `exists()` negated with `~`. The choice between a correlated scalar subquery and a join to an aggregate is a SQL and performance question; SQLAlchemy builds either one with the same few calls, so compare the SQL each emits against your database before settling on one. ## Forgetting the conversion SQLAlchemy refuses most ambiguous placements instead of guessing: - A `Select` passed to `join()` raises `ArgumentError: Join target, typically a FROM expression, or ORM relationship attribute expected`. - A `Select` passed as a column in `select()` raises an `ArgumentError` that tells you to use `.subquery()`. - A `Select` on one side of a comparison in `where()` still works but emits `SAWarning: implicitly coercing SELECT object to scalar subquery; please use the .scalar_subquery() method`. Treat that warning as a bug: the coercion guesses your intent, and an explicit `scalar_subquery()` states it.

  • How do you fix `returned no FROM clauses due to auto-correlation` on a scalar subquery?
    The enclosing select already has every table the subquery uses, so auto-correlation removed them all. Say which tables are outer references with `.correlate(customers)`, or which must stay inside with `.correlate_except(orders)`; the subquery then keeps its own `FROM orders` and refers to the outer `customers` row.
  • When would you choose `cte()` over `subquery()` for the same select in SQLAlchemy?
    Both give a `FromClause` with the same `.c` collection, so the Python code barely changes. Choose `cte()` when you want the body named once in a `WITH` clause, when another CTE builds on it, or when you need `recursive=True`; choose `subquery()` for a one-off derived table rendered inline where it is used.

  • A select() can be passed straight to join() as a table
  • scalar_subquery() returns a FROM clause you can join to
  • Correlation must always be declared with correlate()
  • cte() renders the subquery inline where it is joined
  • The implicit coercion warning is harmless and can be ignored