How to implement table joins when using SQLAlchemy?

In SQLAlchemy, the join() method is commonly used to combine tables, allowing you to link two tables based on a specific condition (for example, via a foreign key).

An example of joining two tables, User and Address, on the user_id field:

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
session = Session()

# Assume we have models User and Address
query = session.query(User).join(Address, User.id == Address.user_id)

for user in query.all():
    print(user.name)

You can also use outerjoin() for a left outer join. It is important that the join condition is correctly specified, or SQLAlchemy will automatically determine it based on foreign keys if they are configured in the models.