How to execute "left outer join" in SqlAlchemy
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")
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).