Question: Here's my SQL setup  create table a ( id serial primary key, ta text ); create table b ( id serial primary key, tb text, aid integer references a(id) not null );  Python: import sqlalchemy as sa import sqlalchemy.orm connection_url = "..." engine = sa.create_engine(connection_url, echo=True, future=True) mapper_registry = sa.orm.registry() class A: pass class B: pass mapper_registry.map_imperatively( B, sa.Table( 'b', mapper_registry.metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('tb', sa.String(50)), sa.Column('aid', sa.ForeignKey('a.id')), )) mapper_registry.map_imperatively( A, sa.Table( 'a', mapper_registry.metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('ta', sa.String(50)) ), properties={ 'blist': sa.orm.relationship(B, lazy='joined'), }, ) with sa.orm.Session(engine) as session: sel = sa.select(A) cur = session.execute(sel) for rec in cur.unique().all(): print(rec.A.ta, [b.tb for b in rec.A.blist])  This works fine so far, but now I need to apply a filter to the subtable (B) to include only rows that match the criteria.  sel = sa.select(A).where(?WHAT?.like('search'))  In other words, how do I write an equivalent of the following SQL in SqlAlchemy? SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.aid WHERE b.tb like 'search'  How about this one (which I expect to produce empty lists in the target class): SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.aid AND b.tb like 'search' 

Answer: The two solutions (for 2 asked questions) presented below rely on two sqlalchemy functions:  sqlalchemy.orm.contains_eager to trick sqlalchemy that the desired relationship is already part of the query; and sqlalchemy.orm.Query.options to disable the default joinedload configured on the relationship.   Question 1 SELECT * FROM a OUTER JOIN b ON a.id = b.aid WHERE b.tb like 'search'  Answer 1 is achieved by this query with explanations in line: sel = ( sa.select(A) # disable 'joinedload' if it remains configured on the mapper; otherwise, the line below can be removed .options(sa.orm.lazyload(A.blist)) # join A.blist explicitely .outerjoin(B, A.blist) # or: .outerjoin(B, A.id == B.aid) # add the filter .filter(B.tb.like('search')) # trick/hint to SQ that the relationship objects are already returned in the query .options(sa.orm.contains_eager(A.blist)) )  Question 2 SELECT * FROM a OUTER JOIN b ON a.id = b.aid AND b.tb like 'search'  Answer 2 is achieved by this query with explanations in line, but basically the .filter condition is moved into the join condition: sel = ( sa.select(A) # disable 'joinedload' if it remains configured on the mapper; otherwise, the line below can be removed .options(sa.orm.lazyload(A.blist)) # join A.blist explicitely including the filter .outerjoin(B, sa.and_(B.aid == A.id, B.tb.like('search'))) # trick/hint to SQ that the relationship objects are already returned in the query .options(sa.orm.contains_eager(A.blist)) )   Warning: you should be careful when using contains_eager and use it in the well defined scope, because you are basically "lying" to the SA model that you have loaded "all" related objects when you might not be. For the purpose of just querying the data it is usually totally fine, but working on modifying and adding to the relationships might lead to some strange results.