8 min readLast updated
How do you join tables in SQLAlchemy?
Short answer: add .join() to a select(). select(Client.name, Order.id).join(Client.orders) runs an INNER JOIN and returns only the clients that have orders. Use .outerjoin() for a LEFT OUTER JOIN that also keeps the clients without orders.
python
from sqlalchemy import select
stmt = select(Client.name, Order.id).join(Client.orders)
print(session.execute(stmt).all())
# [('Ana', 101), ('Ana', 102), ('Eva', 103)]
stmt = select(Client.name, Order.id).outerjoin(Client.orders)
print(session.execute(stmt).all())
# [('Ana', 101), ('Ana', 102), ('Luis', None), ('Eva', 103)]The examples use SQLAlchemy 2.1 and work the same in 2.0. With Flask-SQLAlchemy, write db.select() and db.session instead.
join() keeps only the rows that match, outerjoin() keeps every row of the left table and print() shows the SQL that SQLAlchemy sends.
Every type of JOIN in SQLAlchemy
| What you want | SQLAlchemy | SQL |
|---|---|---|
| Only the rows that match | .join(Client.orders) | INNER JOIN |
| Every row on the left, matches on the right | .outerjoin(Client.orders) or .join(Client.orders, isouter=True) | LEFT OUTER JOIN |
| Every row on both sides | .join(Order, full=True) | FULL OUTER JOIN |
| Every row on the right | Swap the tables: select_from(Order).outerjoin(Client) | RIGHT JOIN (no method of its own) |
| Join with your own condition | .join(Order, Client.id == Order.client_id) | JOIN ... ON ... |
| Rows with no match | .outerjoin(Client.orders).where(Order.id.is_(None)) | LEFT JOIN ... WHERE ... IS NULL |
| The same table twice | aliased(User) and one .join() for each alias | JOIN users AS users_1 |
The models used in the examples
Two tables: each client can have many orders. The ForeignKey connects them, and relationship() lets you write Client.orders in your joins.
python
from sqlalchemy import ForeignKey, String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship
class Base(DeclarativeBase):
pass
class Client(Base):
__tablename__ = "clients"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(50))
orders: Mapped[list["Order"]] = relationship(back_populates="client")
class Order(Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(primary_key=True)
client_id: Mapped[int] = mapped_column(ForeignKey("clients.id"))
total: Mapped[int]
client: Mapped["Client"] = relationship(back_populates="orders")
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
session = Session(engine)
session.add_all([
Client(id=1, name="Ana", orders=[Order(id=101, total=50), Order(id=102, total=20)]),
Client(id=2, name="Luis"),
Client(id=3, name="Eva", orders=[Order(id=103, total=80)]),
])
session.commit()Ana has two orders, Eva has one and Luis has none. Watch what happens to Luis in each join.