Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Python SQLAlchemy Subqueries, EXISTS, and Raw SQL

Learn how to use SQLAlchemy subqueries and EXISTS clauses, and when raw SQL is the better choice, with practical Python examples and tradeoffs.

~5 min readPython
#SQLAlchemy#subqueries#EXISTS clause#raw SQL
A diagram showing a SQLAlchemy query with a subquery and EXISTS clause, with a raw SQL fragment on the side, representing the choice between ORM and raw SQL.

When a query needs to filter rows based on the existence of related records, or when the ORM's query API cannot express a complex join, developers often consider raw SQL. SQLAlchemy already provides subqueries and EXISTS constructs that handle many of these cases while keeping queries structured and composable. This article explains how to use those constructs and when raw SQL is the better choice.

Subqueries in SQLAlchemy: Core and ORM

SQLAlchemy has two layers: Core, which is the SQL expression language, and ORM, which builds on Core and adds object mapping. Both layers support subqueries, but the syntax differs slightly.

In Core, you build a subquery using select() and then call .subquery() to make it reusable. For example, to compute total order amounts per user:

python
from sqlalchemy import select, func order_totals = ( select(Order.user_id, func.sum(Order.amount).label('total')) .group_by(Order.user_id) .subquery() )

In the ORM, the same select() style works with mapped classes. Older SQLAlchemy code sometimes uses a Query object instead:

python
order_totals = ( session.query(Order.user_id, func.sum(Order.amount).label('total')) .group_by(Order.user_id) .subquery() )

Both produce a Subquery object that can be used in joins, filters, or as a derived table in a larger select(). The Core version is more explicit, while the ORM version integrates with the session's query machinery.

Using a Subquery to Filter the Main Query

A common pattern is to use a subquery to filter the main query. Suppose you want to find users whose total order amount exceeds 1000. You can join the subquery and filter on its computed total column:

python
from sqlalchemy import select stmt = ( select(User) .join(order_totals, User.id == order_totals.c.user_id) .where(order_totals.c.total > 1000) ) users = session.execute(stmt).scalars().all()

Here order_totals.c.total refers to the column label defined in the subquery. The ORM maps the result to User objects because the query selects User directly.

You can also use a subquery in a WHERE clause with IN. For example, to find users who have placed at least one order, you can write:

python
user_ids = select(Order.user_id).distinct() stmt = select(User).where(User.id.in_(user_ids))

This is equivalent to WHERE id IN (SELECT DISTINCT user_id FROM orders). While this works, EXISTS is often more efficient and clearer for existence checks.

Using EXISTS to Filter Rows

The EXISTS predicate in SQL checks whether a subquery returns any rows. SQLAlchemy provides the exists() construct for this. It is ideal for filtering based on the presence of related records without needing to join and deduplicate.

python
from sqlalchemy import exists has_orders = exists().where(Order.user_id == User.id) stmt = select(User).where(has_orders) users_with_orders = session.execute(stmt).scalars().all()

SQLAlchemy correlates the outer User table automatically here, so the subquery references User.id from the enclosing query. If you want to make that correlation explicit, you can add .correlate(User) to the exists() expression.

You can also use EXISTS with a full select():

python
has_orders = exists(select(Order.id).where(Order.user_id == User.id)) stmt = select(User).where(has_orders)

This form passes the subquery directly to exists() and can read more clearly when the subquery needs several conditions.

EXISTS can be more efficient than IN for existence checks when the subquery can return many rows, because the database can stop scanning as soon as one row is found. The actual performance depends on indexing and the query planner, so test with your data.

When Raw SQL Makes Sense

Despite the flexibility of SQLAlchemy's query API, there are cases where raw SQL is the pragmatic choice:

  • Database-specific features: For example, PostgreSQL's DISTINCT ON, recursive CTEs, or FILTER clauses where you prefer native database syntax or need a feature not covered by the generic SQLAlchemy API.
  • Complex window functions: While SQLAlchemy supports window functions, writing them in raw SQL can be more readable for intricate calculations.
  • Performance tuning: If you need to hand-optimize a query with hints or specific join orders, raw SQL gives you full control.
  • Legacy queries: When migrating an existing application, you may want to keep SQL that is already tested and optimized.

Raw SQL in SQLAlchemy is written using text(). For example:

python
from sqlalchemy import text stmt = text('SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > :min_amount)') result = session.execute(stmt, {'min_amount': 100})

This keeps the query in SQL but still uses SQLAlchemy's connection pooling and transaction management.

Combining Raw SQL with SQLAlchemy Constructs

You do not have to choose between raw SQL and SQLAlchemy constructs. You can embed raw SQL fragments inside a select() using literal_column() or text(). For instance, to use a raw SQL fragment in a WHERE clause:

python
from sqlalchemy import select, literal_column stmt = select(User).where( literal_column('EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)') )

This is risky because literal_column() does not escape or parameterize anything, so only use it with trusted constants. A safer approach is to use text() with bind parameters:

python
from sqlalchemy import text stmt = select(User).where( text('EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id AND orders.amount > :amt)') ).params(amt=100)

You can also map raw SQL results to ORM entities using from_statement():

python
stmt = select(User).from_statement(text('SELECT * FROM users WHERE id = :id')) user = session.execute(stmt, {'id': 1}).scalar_one()

SQLAlchemy maps the rows to User and registers the loaded object in the session's identity map, so changes are tracked normally. What you give up is SQLAlchemy's SQL generation and composability: the statement is a plain string, and the ORM cannot extend or adapt that SQL through the usual expression API. Make sure the raw statement returns columns that match the User mapping.

Performance and Maintainability Tradeoffs

Subqueries and EXISTS are not inherently slow; their performance depends on how the database executes them. A correlated subquery that references outer columns can cause a nested loop, which may be inefficient if the outer table is large. In such cases, a join with proper indexes might be faster. EXISTS often stops early, but it still requires an index on the referenced column.

Raw SQL gives you full control over the execution plan, but it also removes SQLAlchemy's ability to generate dialect-specific SQL. If you later switch databases, raw SQL may break. Maintainability also suffers: raw SQL strings are harder to refactor, and you lose the ability to compose queries programmatically.

For most applications, using SQLAlchemy's subqueries and EXISTS is the right balance. They are readable, portable, and integrate with the ORM's session and transaction logic. Reserve raw SQL for cases where the ORM cannot express the query or where you have measured a performance problem that cannot be solved otherwise.

Choosing the Right Approach

Use ORM subqueries when you need to reuse a derived table in multiple places or when you want the result to map directly to model objects. Use EXISTS when you only need to check for the presence of related rows and want to avoid joining and deduplicating. Use raw SQL when you need database-specific syntax, complex analytical queries, or when you are porting an existing SQL codebase.

A practical decision rule: if the query can be written with SQLAlchemy's expression language in about the same number of lines as raw SQL, prefer the expression language. If the raw SQL is significantly clearer or uses a feature SQLAlchemy does not support, use text() and keep the SQL string in a dedicated module with comments explaining why it cannot be expressed in SQLAlchemy.

Try matching browser tools

All tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.