RIGHT OUTER JOIN in SQLAlchemy

Question

I have two tables beard and moustache defined below:

+--------+---------+------------+-------------+
| person | beardID | beardStyle | beardLength |
+--------+---------+------------+-------------+

+--------+-------------+----------------+
| person | moustacheID | moustacheStyle |
+--------+-------------+----------------+

I have created a SQL Query in PostgreSQL which will combine these two tables and generate following result:

+--------+---------+------------+-------------+-------------+----------------+
| person | beardID | beardStyle | beardLength | moustacheID | moustacheStyle |
+--------+---------+------------+-------------+-------------+----------------+
| bob    | 1       | rasputin   | 1           |             |                |
+--------+---------+------------+-------------+-------------+----------------+
| bob    | 2       | samson     | 12          |             |                |
+--------+---------+------------+-------------+-------------+----------------+
| bob    |         |            |             | 1           | fu manchu      |
+--------+---------+------------+-------------+-------------+----------------+

Query:

SELECT * FROM beards LEFT OUTER JOIN mustaches ON (false) WHERE  person = "bob"
UNION ALL
SELECT * FROM beards b RIGHT OUTER JOIN mustaches ON (false) WHERE  person = "bob"

However I can not create SQLAlchemy representation of it. I tried several ways from implementing from_statement to outerjoin but none of them really worked. Can anyone help me with it?

Answer 1

Unfortunately, SQLAlchemy only provides API for LEFT OUTER JOIN as .outerjoin(). As mentioned above, we could get a RIGHT OUTER JOIN by reversing the operands of LEFT OUTER JOIN; eg. A RIGHT JOIN B is the same as B LEFT JOIN A. In SQL, the following statements are equivalent:

SELECT * FROM A RIGHT OUTER JOIN B ON A.common = B.common;
SELECT * FROM B LEFT OUTER JOIN A ON A.common = B.common;

However, in SQLAlchemy, we need to query on a class then perform join. The tricky part is rewriting the SQLAlchemy statement to reverse the tables. For example, the results of the first two queries below are different as they return different objects.

# No such API (rightouterjoin()) but this is what we want.
# This should return the result of A RIGHT JOIN B in a list of object A
session.query(A).rightouterjoin(B).all()   # SELECT A.* FROM A RIGHT OUTER JOIN B ...

# We could reverse A and B but this returns a list of object B
session.query(B).outerjoin(A).all()        # SELECT B.* FROM B LEFT OUTER JOIN A ...

# This returns a list of object A by choosing the 'left' side to be B using select_from()
session.query(A).select_from(B).outerjoin(A).all()   # SELECT A.* FROM B LEFT OUTER JOIN A ...

# For OP's example, assuming we want to return a list of beard object:
session.query(beard).select_from(moustache).outerjoin(beard).all()

Just adding to the answers, you can find the use of select_from from the SQLAlchemy doc.

Answer 2

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".

Answer 3

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.