inner joins vs joinloads in sqlalchemy

Question

I read about sqlalchemy joinloads like mentioned here and I little confused about the benefits or special usages over simply joining two tables like mentioned here

I would like to know about when to use each method, currently I don't see any benefit for using joinloads for now, can you please explain the difference? And the use cases to prefer joinloads

Answer 1

Sqlalchemy docs says joinedload() is not a replacement for join() and joinedload() doesn't affect the query result :

Query.join()

Query.options(joinedload())

Let's say if you wants to get same date that already related with data you are querying, but when you get this related data it won't change the result of the query it is like an attachment. Better to look sqlalchemy docs joinedload

class User(db.Model):
    ...
    addresses = relationship('Address', backref='user')
class Address(db.Model):
    ...
    user_id = Column(Integer, ForeignKey('users.id'))

The code below query user filter and return that user and optionally you can getting that user addresses.

user = db.session.query(User).options(joinedload(User.addresses)).filter(id==1).one()

Now lets look at join:

user = db.session.query(User).join(Address).filter(User.id==Address.user_id).one()

Conclusion

The query with joinedload() get that user addresses.

Other query, query on both table, check for user id on both table, so the result depend on this. But joinedload() if user doesn't have any address you will have user but no address. in join() if user doesn't have address there will not result.