Out of interest, are you talking about SQLAlchemy, or ORMs in general? # a subquery demo_accounts = db.query(Account.id).join(Client).filter(Client.nam

Out of interest, are you talking about SQLAlchemy, or ORMs in general? # a subquery demo_accounts = db.query(Account.id).join(Client).filter(Client.name=='Demo') # used inside a query print(db.query(Account.name).filter(Account.id.in_(demo_accounts))) SELECT account.name AS account_name FROM account WHERE account.id IN (SELECT account.id AS account_id FROM account JOIN client ON client.id = account.client_id WHERE client.name = :name_1) # or as a cte da_as_cte = demo_accounts.cte() print(db.query(Account.name).join(da_as_cte, da_as_cte.c.id==Account.id)) WITH anon_1 AS (SELECT account.id AS id FROM account JOIN client ON client.id = account.client_id WHERE client.name = :name_1) SELECT account.name AS account_name FROM account JOIN anon_1 ON anon_1.id = account.id There are obviously much more complex cases, but SA tends to handle things in a pretty sane way. You can compose query segments (like above) and if you really need to go back to the raw sql, you can and still have the results mapped into your python objects.