RIGHT OUTER JOIN in SQLAlchemy

In SQL, A RIGHT OUTER JOIN B is equivalent of B LEFT OUTER JOIN A. So, technically there is no need in the RIGHT OUTER JOIN API - it is possible to do the same by switching the places of the target "selectable" and joined "selectable". SQL Alchemy provides an API for this:

# this **fictional** API:query(A).join(B, right_outer_join=True)  # right_outer_join doesn't exist in SQLA!# can be implemented in SQLA like this:query(A).select_entity_from(B).join(A, isouter=True)

See SQLA Query.join() doc, section "Controlling what to Join From".

From @Francis P's suggestion I came up with this snippet:

q1 = session.\     query(beard.person.label('person'),           beard.beardID.label('beardID'),           beard.beardStyle.label('beardStyle'),           sqlalchemy.sql.null().label('moustachID'),           sqlalchemy.sql.null().label('moustachStyle'),     ).\     filter(beard.person == 'bob')q2 = session.\     query(moustache.person.label('person'),           sqlalchemy.sql.null().label('beardID'),            sqlalchemy.sql.null().label('beardStyle'),           moustache.moustachID,           moustache.moustachStyle,     ).\     filter(moustache.person == 'bob')result = q1.union(q2).all()

However this works but you can't call it as an answer because it appears as a hack. This is one more reason why there should be RIGHT OUTER JOIN in sqlalchemy.

If A,B are tables, you can achieve:
SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id WHERE B.id = my_id
by:
SELECT A.* FROM B JOIN ON A.id = B.a_id WHERE B.id = my_id
in sqlalchemy:

from sqlalchemy import selectresult = session.query(A).select_entity_from(select([B]))\    .join(A, A.id == B.a_id)\    .filter(B.id == my_id).first()

for example:

# import ...class User(Base):    __tablenane = "user"    id = Column(Integer, primary_key=True)    group_id = Column(Integer, ForeignKey("group.id"))class Group(Base):    __tablename = "group"    id = Column(Integer, primary_key=True)    name = Column(String(100))

You can get user group name by user id with the follow code:

# import ...from sqlalchemy import selectuser_group_name, = session.query(Group.name)\    .select_entity_from(select([User]))\    .join(Group, User.group_id == Group.id)\    .filter(User.id == 1).first()

If you want a outer join, use outerjoin() instead of join().

This answer is a complement to the previous one(Timur's answer).