How to execute "left outer join" in SqlAlchemy

Solution 1:

q = session.query(Table1.field1, Table1.field2)\
    .outerjoin(Table2)\ # use in case you have relationship defined
    # .outerjoin(Table2, Table1.id == Table2.table_id)\ # use if you do not have relationship defined
    .filter(Table2.tbl2_id == None)

should do it, assuming that field1 and field2 are from Table1, and that you define a relationship:

class Table2(Base):
    # ...
    table1 = relationship(Table1, backref="table2s")

Solution 2:

You can also do that using SQLAlchemy Core only:

session.execute(
    select(['field11', 'field12'])
    .select_from(
        Table1.outerjoin(Table2, Table1.tbl1_id == Table2.tbl1_id))
    .where(Table2.tbl2_id.is_(None))
)

PS .outerjoin(table, condition) is equivalent to .join(table, condition, isouter=True).