SQLAlchemy joinedload vs. join()
In SQLAlchemy, both the relationship loading option (.options(joinedload(...))) and the query builder method (.join(TableClass)) result in a SQL JOIN clause. However, they serve fundamentally different purposes and lead to distinct results in the ORM (Object Relational Mapper) layer.
Understanding this difference is crucial for avoiding the common "N+1 problem" and correctly shaping the data returned by your queries.
Key Difference Summary
| Feature | .options(joinedload(Relationship)) | .join(TableClass or Relationship) |
|---|---|---|
| Purpose | Eagerly load related objects to solve the N+1 problem. | Filter the primary objects based on the relationship. |
| Output Data | Returns primary objects with related objects pre-populated in the cache. | Returns only primary objects (unless contains_eager is used). |
| Relationship Required | Mandatory. Must be used with a defined ORM relationship(). | Optional (can join on columns too). Often uses relationships for cleaner syntax. |
| N+1 Problem | Solved. Related data is loaded in a single query. | Not Solved. Related data is not automatically attached to the relationship cache. |
| SQL Join Type | Defaults to LEFT OUTER JOIN (to keep primary objects even if the relationship is empty). | Defaults to INNER JOIN (which filters out primary objects if no matching relationship exists). |
Detailed Comparison and Use Cases
The joinedload Approach (Eager Loading)
The joinedload method is a loader option. It instructs SQLAlchemy on how to load the objects once the query has retrieved the rows. It is an optimization tool.
Use Case: Performance Optimization
You want to retrieve a list of all Author objects and simultaneously fetch all their associated Book objects in a single query, avoiding the N+1 problem when iterating through the authors.
from sqlalchemy.orm import joinedload
# Assume: Author.books is a defined relationship
# SQL generated: SELECT * FROM author LEFT OUTER JOIN book ON author.id = book.author_id
# Result: One combined result set.
authors_with_books = session.query(Author).options(
joinedload(Author.books)
).all()
# Accessing the books does NOT trigger a new database query:
for author in authors_with_books:
print(f"{author.name}: {len(author.books)} books")
Crucial Point: joinedload uses a LEFT OUTER JOIN by default. If an author has no books, the author is still included in the result set, and their books attribute will be an empty list ([])-no further query is needed.
The .join() Approach (Filtering and Shaping)
The .join() method is a query builder method. It modifies the generated SQL to perform a join, primarily to apply a WHERE clause based on the joined table's columns.
Use Case: Filtering the Primary Objects
You want to retrieve only Author objects who have at least one Book with the title "The Hitchhiker's Guide to the Galaxy".
from sqlalchemy import or_
# SQL generated: SELECT * FROM author INNER JOIN book ON author.id = book.author_id WHERE book.title = '...'
authors_of_specific_book = session.query(Author).join(
Author.books
).filter(
Book.title == "The Hitchhiker's Guide to the Galaxy"
).all()
# Accessing author.books WILL trigger N+1 queries later!
for author in authors_of_specific_book:
# A separate query runs here for each author:
print(author.books)
Crucial Point: The default .join() uses an INNER JOIN. If the author does not meet the join condition (i.e., they don't have the specific book), the author is filtered out of the primary result set.
Advanced Combination: The Hybrid Approach (.join() + contains_eager)
If you need to filter the primary objects using a join (using .join()) and simultaneously eagerly load the related objects for performance, you must use the contains_eager() option.
Use Case: Filter AND Eager Load
Retrieve only Authors with a published book, and ensure the books relationship is eagerly loaded.
from sqlalchemy.orm import contains_eager
# We must instruct the join type to be INNER if we want to filter
query = session.query(Author).join(Author.books, isouter=False)
# Apply the eager loading option:
authors_and_eager_books = query.options(
# This tells SQLAlchemy that the Book data is ALREADY in the result rows
contains_eager(Author.books)
).all()
# Result:
# 1. Authors without books were filtered out by the INNER JOIN.
# 2. Accessing author.books does NOT trigger a new query (solved N+1).
# Annotation: This is the fastest way to perform a filtered query while
# ensuring the related data is attached to the ORM session cache.
When to Use Which Method
| Scenario | Recommended Method |
|---|---|
| Basic Optimization: Load all children for every parent. | joinedload |
| Filtering: Find parents based on a property of their children. | .join() (with filtering) |
| Performance + Filtering: Filter parents AND ensure children are eagerly loaded. | .join() + contains_eager |
| Optional Children: Load children only if they exist, but don't filter parents if they don't. | joinedload (default LEFT OUTER JOIN) |