There are different types of subquery in SQL and SQLAlchemy. In this blog, we are going to talking about:

  • Subquery
  • CTE (Common Table Expresssion)
  • Scalar Subquery & Non-Scalar Subquery
  • Correlated Subquery
  • Set Result As Subquery
  • Exsits Subquery
  • Relationship As Subquery

Scalar & Non-Scalar Subquery

Concepts

Based on words in SQLAlchemy Docs:

A scalar subquery is a subquery that returns exactly zero or one row and exactly one column. The subquery is then used in the COLUMNS or WHERE clause of an enclosing SELECT statement.

Scalar query is different than a regular (or, non-scalar) subquery in that it is not used in the FROM clause.

Read more

In conclusions:

Scalar: Return exactly one thing. Used in COLUMNS or WHERE Non-scalar: Return a selectable, usually used in FROM

Construct Scalar Subquery

As we may all already known that Select.subquery() could create non-scalar subquery. If we want to create a scalar subquery, we need to use another method: Select.scalar_subquery()

Correlated Subquery

Concept

Correlated subquery is a scalar subquery that related to the table of it's enclosing select:

select user_table.uid where (
  select count(addresses.aid) 
  where addresses.uid = user_table.uid
) > 1;

This query will return user id of whom that have more than one addresses. Note this subquery is correlated to the outer selection, since the where condition uses user_table.uid which comes from outer table.

SQLAlchemy Auto Correlation

In some simple cases, SQLAlchemy will take care of correlations:

TODO

Set Operations As Subquery

Set Operations Explained

SQLAlchemy allows construct subquery from a CompoundSelect which is what we got after set operations of several selectables.

from sqlalchemy import union_all
# several selectables.
# selectables usually created using select()
stmt1: Select = ...
stmt2: Select = ...

# get a CompoundSelect
union_selection: CompoundSelect = union_all(stmt1, stmt2, ...)

CompoundSelect To Subquery

It's extremly simple to convert a CompoundSelect object to a useable non-scalar subquery: Just use .subquery() method:

union_subquery = union_selection.subquery()

Fetching ORM Entities

However, using .subquery() alone doesn't returns an ORM class instance since SQLAlchemy don't know what schemes that query returns.

To bind the subquery to an ORM entity, we could exploit aliased() feature:

some_user = aliased(User, union_subquery)

This usage of aliased(<orm_class>, <sub_query>) could be used anywhere not just in this case.

A Quicker Way

SQLAlchemy provides a shortcut called select(<orm_class>).from_statement(<statements>), if we don't need to add any other criteria after this, we could do something like below:

select(User).from_statement(union_selection)

Note that in this case, we use CompoundSelect object directly and there's no need to convert it into the subquery.

ORM Relationship Subquery

We could use relationships we defined in ORM entities to construct scalar subquerys that used in WHERE clauses.

Most of the times, we would use something like r.any(), r.has() or some Python Comparators.

Any

Relationship.any() is used at one-to-many side, limiting result of enclosing select.

Consider we want to select user that has no contact info, one way is to use relationship subquery:

select(User).where(~User.addresses.any())

~ is Bitwise Not operator in Python, here is used to indicate NOT in SQL.

Also, we could select user that has email addresses end with google@com:

select(User).where(User.addresses.any(Address.address.like('%@google.com')))

Has

Relationship.has() could be considered the complementary version of has(), working at many-to-one side:

For example, we could select all addresses rows that associated to user that named bob.

select(Address).where(Address.user.has(User.name == 'bob'))

== & != & contains()

The any() and has() both worked with ORM Entities in class level. We also have some tools in instances level:

user = session.get(User, 2)  # get user with primary key 2
select(Address).where(Address.user == user)

address = Address(...)
select(User).where(User.addresses.contains(address))

Refs