`joinedload` vs `selectinload`: when each one wins

Capsule overview

You already know how to detect N+1 (capsules 02-03). Now it's time to eliminate it. SQLAlchemy offers you three eager loading strategies: joinedload, selectinload, and subqueryload. The last one (subqueryload) is legacy and is covered briefly in capsule 05. The first two are 95% of your daily decisions.

The most common mistake of someone just learning eager loading is treating joinedload as "the universal solution". It sounds ideal: a single query, everything loaded. Why selectinload, which fires two queries? The answer is cartesian explosion: for 1 author × 100 books × 50 reviews, joinedload returns 5,000 duplicated rows to load 5,151 unique records. SQLAlchemy reconstructs the objects correctly, but you sent 5,000 rows over the network for nothing.

This capsule gives you:

• The exact SQL each strategy generates (you'll see it with echo=True).
• The plans PostgreSQL runs for each one.
• A clear decision matrix: cardinality, pagination, response shape → strategy.
• Cases where you combine both in a single query.

By the end, you'll be able to look at an endpoint and decide in 10 seconds which strategy to use, justifying it with data.

Mental model: two ways to "bring everything together"

Imagine you go to a library to look for:

"All of author X's books, along with all the reviews of each book."

Strategy 1 (joinedload): you ask the librarian for a single giant query that returns a table with: author, book, review. For each review, the full author + book row is repeated. If author X has 100 books and each one 50 reviews, you receive 5,000 rows (one per review), all with author X repeated and the book repeated 50 times each. SQLAlchemy deduplicates everything and hands you the clean objects, but the librarian made one enormous query and you received a table with a ton of redundancy.

Strategy 2 (selectinload): you ask for two separate and efficient queries:

1. "Give me author X and their 100 books."
2. "Give me all the reviews for the books with id IN (1, 2, ..., 100)."

You receive 1 author row + 100 book rows + 5,000 review rows. No duplication. Two queries instead of one, but the total number of rows transferred is much lower.

The central trade-off

Quick heuristic:

1:1 or 1:N with N ≤ 10 → joinedload. 1:N with N > 10 → selectinload. When in doubt, selectinload.

joinedload: one query with a LEFT JOIN

joinedload modifies the main query to include a LEFT OUTER JOIN with the relationship's table. A single trip to the database, everything loaded.

Basic syntax

from sqlalchemy import select
from sqlalchemy.orm import joinedload

stmt = (
    select(Author)
    .options(joinedload(Author.books))
    .where(Author.name == "tolkien")
)
result = (await session.scalars(stmt)).unique().all()

Key detail: selectinload and joinedload are passed as options(...) to the query. joinedload additionally requires .unique() when it brings lists of 1:N relationships (without it, SQLAlchemy gives you a warning).

Generated SQL

With echo=True, you see:

SELECT
    authors.id, authors.name,
    books_1.id AS books_1_id,
    books_1.title AS books_1_title,
    books_1.author_id AS books_1_author_id
FROM authors
LEFT OUTER JOIN books AS books_1 ON authors.id = books_1.author_id
WHERE authors.name = 'tolkien'

A single query. The alias books_1 is because SQLAlchemy can do multiple cascading joins with different aliases.

PostgreSQL plan

EXPLAIN ANALYZE
SELECT authors.id, authors.name, books_1.id, books_1.title
FROM authors
LEFT OUTER JOIN books AS books_1 ON authors.id = books_1.author_id
WHERE authors.name = 'tolkien';

Assuming indexes authors(name) and books(author_id):

Nested Loop Left Join  (cost=8.45..245.32 rows=20 width=64) (actual time=0.124..0.612 rows=20 loops=1)
  ->  Index Scan using idx_authors_name on authors  (cost=0.42..8.44 rows=1 width=20) (actual time=0.045..0.046 rows=1 loops=1)
        Index Cond: (name = 'tolkien')
  ->  Index Scan using idx_books_author_id on books books_1  (cost=0.42..236.50 rows=20 width=44) (actual time=0.075..0.560 rows=20 loops=1)
        Index Cond: (author_id = authors.id)
Planning Time: 0.245 ms
Execution Time: 0.692 ms

One operation, two indexed lookups, 20 rows returned, sub-millisecond. Ideal.

When joinedload wins

1. 1:1 relationship.

# user → profile (1:1)
stmt = select(User).options(joinedload(User.profile)).where(User.id == 42)

A single result row, no possible duplication. It's the optimal case for joinedload.

2. Small and predictable 1:N relationship (≤ 10 children).

# author → books (typically 1-10 books per author in many domains)
stmt = select(Author).options(joinedload(Author.books))

If on average each author has 5 books, you bring 5 rows per author. Manageable.

3. When you do NOT need LIMIT/OFFSET on the parent.

joinedload with LIMIT gets complicated: the LIMIT is applied to the rows of the JOIN, not to the authors. If you ask for LIMIT 10 expecting 10 authors, you can end up with 10 rows that belong to a single author who has 100 books.

To avoid it, SQLAlchemy offers joinedload(...).innerjoin() or subquery patterns, but it gets verbose. selectinload doesn't have this problem.

When joinedload LOSES: cartesian explosion

# author → books → reviews (nested 1:N, both N large)
stmt = (
    select(Author)
    .options(
        joinedload(Author.books).joinedload(Book.reviews)
    )
    .where(Author.name == "tolkien")
)
result = (await session.scalars(stmt)).unique().all()

Generated SQL:

SELECT
    authors.id, authors.name,
    books_1.id, books_1.title,
    reviews_1.id, reviews_1.rating, reviews_1.comment
FROM authors
LEFT OUTER JOIN books AS books_1 ON authors.id = books_1.author_id
LEFT OUTER JOIN reviews AS reviews_1 ON books_1.id = reviews_1.book_id
WHERE authors.name = 'tolkien'

Rows returned with tolkien (50 books × 100 reviews per book):

1 author × 50 books × 100 reviews = 5,000 rows

Each row contains the author (repeated 5,000 times), the book (repeated 100 times each), the review.

PostgreSQL plan:

Nested Loop Left Join  (cost=...)
  ->  Nested Loop Left Join  (cost=...)
        ->  Index Scan using idx_authors_name on authors  (rows=1)
        ->  Index Scan using idx_books_author_id  (rows=50)
  ->  Index Scan using idx_reviews_book_id  (rows=100 loops=50)
Rows: 5000
Execution Time: 145ms

Compare it with selectinload:

stmt = (
    select(Author)
    .options(
        selectinload(Author.books).selectinload(Book.reviews)
    )
    .where(Author.name == "tolkien")
)

3 queries:

1. SELECT authors WHERE name = 'tolkien' → 1 row.
2. SELECT books WHERE author_id IN (1) → 50 rows.
3. SELECT reviews WHERE book_id IN (1, 2, ..., 50) → 5,000 rows.

Total: 5,051 unique rows, in 3 queries. Vs 5,000 duplicated rows in 1 query with joinedload.

Why does the duplication hurt?

• More bytes over the network.
• More memory on the Python client to build the objects.
• SQLAlchemy spends CPU deduplicating.
• On high-RPS endpoints, this shows.

Typical result: nested selectinload is 5-10x faster for cases like this.

selectinload: two queries with WHERE id IN (...)

selectinload runs two queries: the main one (parent) and then a separate query for the children using WHERE parent_id IN (id_list).

Basic syntax

from sqlalchemy.orm import selectinload

stmt = (
    select(Author)
    .options(selectinload(Author.books))
    .where(Author.name == "tolkien")
)
result = (await session.scalars(stmt)).all()

Note: you don't need .unique() with selectinload because the queries are separate — there's no row duplication to clean up.

Generated SQL

-- Query 1: parent
SELECT authors.id, authors.name FROM authors WHERE authors.name = 'tolkien'

-- Query 2: children with WHERE id IN
SELECT books.id, books.title, books.author_id
FROM books
WHERE books.author_id IN (41)

If the parent query returned multiple authors, the second query would use WHERE author_id IN (41, 42, 43, ...). SQLAlchemy gathers the IDs automatically.

PostgreSQL plan

Query 1:

Index Scan using idx_authors_name on authors  (rows=1, time=0.05ms)

Query 2:

Bitmap Heap Scan on books  (rows=50, time=0.45ms)
  Recheck Cond: (author_id = ANY (ARRAY[41]))
  ->  Bitmap Index Scan on idx_books_author_id  (rows=50)

Two queries, sub-millisecond each. Total: ~1ms for 51 rows.

When selectinload wins

1. Large 1:N relationship (N > 10).

# author → books (50+ books each)
stmt = select(Author).options(selectinload(Author.books))

No cartesian explosion. Each relationship brings its unique rows.

2. Multiple authors at once.

stmt = (
    select(Author)
    .options(selectinload(Author.books))
    .where(Author.country == "AR")
    .limit(20)
)

If LIMIT 20 returns 20 authors, selectinload fires a second query: WHERE author_id IN (id1, id2, ..., id20). That brings all the books in a single query. Total: 2 fixed queries, no matter how many books each author has.

3. Nested relationship with potential explosion.

stmt = (
    select(Author)
    .options(
        selectinload(Author.books).selectinload(Book.reviews)
    )
)

Three queries: authors + books + reviews. Each one with WHERE id IN (...). No duplication.

4. When you need LIMIT/OFFSET on the parent.

stmt = (
    select(Author)
    .options(selectinload(Author.books))
    .order_by(Author.name)
    .limit(20)
    .offset(40)
)

The LIMIT is applied to the parent query, exactly what you want. The books query brings only the books of those 20 authors.

When selectinload LOSES

1. 1:1 relationships.

# user → profile (1:1)
stmt = select(User).options(selectinload(User.profile)).where(User.id == 42)

You fire 2 queries for something joinedload solves in 1. It's not disastrous, but it's suboptimal.

2. When the IN list gets enormous.

If your parent query returns 10,000 authors, the children query would be:

SELECT * FROM books WHERE author_id IN (1, 2, 3, ..., 10000)

PostgreSQL handles this, but the giant IN list can be inefficient. If you frequently load thousands of parents at once, consider using raw SQL or more aggressive pagination.

3. Tables with a non-indexed FK.

If books(author_id) has no index, WHERE author_id IN (...) does a seq scan. But if you don't have that index, you already have a bigger problem from module 3.

The decision matrix

The golden rule of SQLAlchemy 2.0

"When in doubt, use selectinload. It's the default recommended by the SQLAlchemy team since version 1.4."

Reason: selectinload has the fewest trade-offs in the general case. joinedload wins only in specific cases (1:1, small 1:N without LIMIT). If you're learning, assume selectinload and validate with EXPLAIN that the plan is reasonable.

Combining strategies

Often you need different strategies for different relationships in the same query.

Example: book with author (1:1) and reviews (large 1:N)

stmt = (
    select(Book)
    .options(
        joinedload(Book.author),       # 1:1, joinedload optimal
        selectinload(Book.reviews),    # large 1:N, selectinload optimal
    )
    .where(Book.id == 42)
)
result = await session.scalar(stmt)

Generated SQL:

-- Query 1: book with a join to the author
SELECT books.id, books.title, books.author_id,
       authors_1.id, authors_1.name
FROM books
LEFT OUTER JOIN authors AS authors_1 ON books.author_id = authors_1.id
WHERE books.id = 42

-- Query 2: the book's reviews
SELECT reviews.id, reviews.book_id, reviews.rating, reviews.comment
FROM reviews
WHERE reviews.book_id IN (42)

Total: 2 queries. No duplication, no N+1. joinedload for the author, which is 1:1 (optimal), and selectinload for reviews, which can be many (optimal).

Example: author with books (large 1:N), books with author (back-ref)

Sometimes you have circular references and want to load them efficiently:

stmt = (
    select(Author)
    .options(
        selectinload(Author.books).joinedload(Book.author),
    )
    .where(Author.name == "tolkien")
)

This loads: 1) the author, 2) books with WHERE author_id IN (...) that includes a join to the author on each book.

Careful: in this case the joinedload(Book.author) is loading the same author we already have (via back_populates). SQLAlchemy is smart enough to reuse the object, but the SQL includes the JOIN anyway. If you don't need book.author explicitly accessible, you can omit the inner joinedload.

Why this matters in real work

1. It's the classic senior backend Python interview question.

"When do you use joinedload vs selectinload?" — junior answer: "one does a JOIN and the other does an IN, I use them interchangeably". Senior answer: "for 1:1 and small 1:N without pagination, joinedload. For large 1:N or when I need LIMIT on the parent, selectinload. The reason is the cartesian explosion of the JOIN when the many side is large." That precision opens doors.

2. The difference between 1 and 50 seconds in production.

A misconfigured endpoint with joinedload over a large 1:N can be 10-50x slower than with selectinload. In real production, that's the difference between a usable endpoint and one that throws timeouts.

3. Productive PR reviews.

When a colleague submits a PR with joinedload and you know the cardinality is large, you can suggest the change with data: "this joinedload will generate 5,000 duplicated rows; I suggest selectinload for 50 rows + 5,000 rows in a separate query, without duplication".

4. Refactoring legacy code.

Old Python apps with SQLAlchemy 1.x tend to overuse joinedload (it was the most documented one). Migrating to selectinload where appropriate is one of the most impactful refactors you can do in a week of work.

Traps and common mistakes

Mistake 1 (conceptual): assuming joinedload is always better "because it's 1 query"

Symptom: "I put joinedload on all relationships and rest easy."

Why it's wrong: for large 1:N, joinedload causes a cartesian explosion. More rows transferred, more client memory, more CPU deduplicating. Result: slower than selectinload, sometimes 10x.

How to distinguish: estimate the relationship's cardinality. If on average the many side has >10 elements, joinedload is suboptimal. Validate with EXPLAIN and by measuring bytes transferred.

How to fix it: the decision matrix. 1:1 → joinedload. Large 1:N → selectinload.

Mistake 2 (practical): forgetting .unique() with joinedload 1:N

Symptom: SQLAlchemy emits a warning: "The unique() method must be invoked..." or your list contains duplicated authors.

Why it happens: joinedload with 1:N produces duplicated rows (an author repeated N times, one for each book). SQLAlchemy deduplicates them but needs you to call .unique() explicitly.

How to fix it: whenever you use joinedload with 1:N relationships (not 1:1), add .unique():

result = (await session.scalars(stmt)).unique().all()

For 1:1 it's not necessary (.scalars().all() works).

Mistake 3 (conceptual): applying joinedload with LIMIT expecting it to limit parents

Symptom: "I asked for LIMIT 10 expecting 10 authors and received rows that belong to just 1 author."

Why it happens: with joinedload, the LIMIT is applied to the result of the JOIN, not to the parent. If the first author has 100 books, the first 100 rows of the JOIN are that author's. LIMIT 10 gives you 10 books from the first author and 0 additional authors.

How to fix it: for LIMIT/OFFSET on the parent with eager loading, use selectinload:

stmt = (
    select(Author)
    .options(selectinload(Author.books))
    .order_by(Author.name)
    .limit(10)
)
# Returns 10 authors with their books loaded.

Or joinedload with a more complex subquery (from_self() in SQLAlchemy 2.0 is uglier than it's worth).

Mistake 4 (conceptual): thinking selectinload is "always 2 queries"

Symptom: "I have 3 levels of nested selectinload and I see only 3 queries."

Why it happens: selectinload is effectively N+1 fixed queries where N is the nesting depth. If you nest 3 levels, that's 3 total queries (parent + level 1 + level 2). It's not a multiplicative N+1.

This is good — it confirms that selectinload scales with nesting depth, not with the amount of data.

How to distinguish: capture with echo=True and count. 3 levels → 3 queries. If you see more, there's a hidden N+1.

Mistake 5 (practical): combining joinedload and selectinload in the wrong order

Symptom: SQLAlchemy throws an error or the generated SQL is absurd.

Why it happens: the chain of loaders matters. joinedload(A.b).selectinload(B.c) means: load b with a JOIN, then load c with WHERE id IN (...). But selectinload(A.b).joinedload(B.c) means: load b with WHERE id IN (...), then within each b load c with a JOIN. Different SQL, different result.

How to fix it: think of the chain as "first the parent, then its descendants". Each .X applies to the child of the previous level. Verify with echo=True that the SQL is what you expected.

Mistake 6 (conceptual): using joinedload with many parallel relationships

Symptom: select(Book).options(joinedload(Book.author), joinedload(Book.reviews), joinedload(Book.tags)). Giant SQL, slow, massive duplication.

Why it's wrong: each joinedload adds a LEFT JOIN. Three parallel joinedloads = SQL with 3 JOINs and rows multiplied across all dimensions. If the book has 5 reviews, 3 tags, and 1 author, that's 5 × 3 × 1 = 15 rows per book.

How to fix it: joinedload for 1:1, selectinload for 1:N:

stmt = select(Book).options(
    joinedload(Book.author),         # 1:1
    selectinload(Book.reviews),      # 1:N
    selectinload(Book.tags),         # 1:N
)

3 total queries instead of 1 with 15× duplication.

Exercises

Exercise 1: predict the generated SQL

For each strategy combination, predict the number of SQL queries emitted and briefly describe the SQL.

Snippet A:

stmt = (
    select(User)
    .options(joinedload(User.profile))
    .where(User.id == 42)
)

Snippet B:

stmt = (
    select(Author)
    .options(selectinload(Author.books))
    .limit(5)
)

Snippet C:

stmt = (
    select(Author)
    .options(
        selectinload(Author.books).selectinload(Book.reviews)
    )
    .where(Author.name == "tolkien")
)

SELECT users.id, users.name, profiles_1.id, profiles_1.bio
FROM users
LEFT OUTER JOIN profiles AS profiles_1 ON users.id = profiles_1.user_id
WHERE users.id = 42

-- Query 1
SELECT authors.id, authors.name FROM authors LIMIT 5

-- Query 2
SELECT books.id, books.title, books.author_id
FROM books
WHERE books.author_id IN (id1, id2, id3, id4, id5)

-- Query 1
SELECT authors.id, authors.name FROM authors WHERE authors.name = 'tolkien'

-- Query 2
SELECT books.id, books.title, books.author_id
FROM books WHERE books.author_id IN (41)

-- Query 3
SELECT reviews.id, reviews.book_id, reviews.rating, reviews.comment
FROM reviews WHERE reviews.book_id IN (1, 2, 3, ..., 50)

Exercise 2: choose between joinedload and selectinload

For each endpoint, decide the strategy and justify it with the expected cardinality:

A. GET /users/{id} — returns a user with their profile (1:1).

B. GET /authors — list of authors with their books (each author has 1-200 books).

C. GET /orders/{id} — returns an order with its items (each order has 1-10 items) and customer (1:1).

D. GET /products?category=X — list of products with their reviews (each product has 0-500 reviews).

Exercise 3: measure a real cartesian explosion

Take the bookstore. Modify the /books-with-author endpoint with two versions:

Version A: joinedload(Author.books).joinedload(Book.reviews). Version B: selectinload(Author.books).selectinload(Book.reviews).

For tolkien (assuming ~50 books with ~5 reviews each), capture with echo=True:

1. Number of queries.
2. Approximate number of rows returned.
3. Execution Time of the main query with EXPLAIN ANALYZE.

joinedload: 1 query with 5,000 duplicated rows → 100-200ms
selectinload: 3 queries with 5,151 unique rows → 10-20ms

Exercise 4: fix an endpoint with a detected N+1

Your CI breaks with:

NPlusOneError: Potential n+1 query detected on `Order.items`

The endpoint:

@app.get("/orders")
async def list_orders(session: AsyncSession = Depends(get_db)):
    orders_q = select(Order).where(Order.status == "completed").limit(20)
    orders = (await session.scalars(orders_q)).all()
    result = []
    for order in orders:
        items = order.items  # ← lazy load: N+1
        total = sum(i.price * i.quantity for i in items)
        result.append({"id": order.id, "total": total})
    return result

Data: each order has 1-15 items. Apply the fix with the correct strategy. Justify your choice.

from sqlalchemy.orm import selectinload

@app.get("/orders")
async def list_orders(session: AsyncSession = Depends(get_db)):
    orders_q = (
        select(Order)
        .options(selectinload(Order.items))
        .where(Order.status == "completed")
        .limit(20)
    )
    orders = (await session.scalars(orders_q)).all()

    result = []
    for order in orders:
        total = sum(i.price * i.quantity for i in order.items)
        result.append({"id": order.id, "total": total})
    return result

-- Query 1
SELECT orders.id, orders.status, orders.user_id
FROM orders
WHERE orders.status = 'completed'
LIMIT 20

-- Query 2
SELECT items.id, items.order_id, items.price, items.quantity
FROM items
WHERE items.order_id IN (1, 5, 7, 12, ..., 89)

.options(joinedload(Order.items))

Exercise 5: combine strategies in a complex endpoint

Your endpoint returns a list of books with:

• The book's author (1:1)
• The book's reviews (large 1:N, up to 200 reviews per book)
• The book's tags (small 1:N, maximum 5 tags)

Design the query with eager loading. Justify each choice.

from sqlalchemy.orm import joinedload, selectinload

stmt = (
    select(Book)
    .options(
        joinedload(Book.author),         # 1:1
        selectinload(Book.reviews),      # large 1:N
        selectinload(Book.tags),         # small 1:N, but combined avoids duplication with reviews
    )
    .where(Book.published_year >= 2020)
    .limit(50)
)
result = (await session.scalars(stmt)).unique().all()

-- Query 1: books with a join to the author (1:1)
SELECT books.id, books.title, books.published_year, books.author_id,
       authors_1.id, authors_1.name
FROM books
LEFT OUTER JOIN authors AS authors_1 ON books.author_id = authors_1.id
WHERE books.published_year >= 2020
LIMIT 50

-- Query 2: reviews
SELECT reviews.id, reviews.book_id, reviews.rating, reviews.comment
FROM reviews
WHERE reviews.book_id IN (id1, id2, ..., id50)

-- Query 3: tags
SELECT tags.id, tags.name, book_tag.book_id, book_tag.tag_id
FROM book_tag
JOIN tags ON tags.id = book_tag.tag_id
WHERE book_tag.book_id IN (id1, id2, ..., id50)

.options(
    joinedload(Book.author),
    joinedload(Book.reviews),
    joinedload(Book.tags),
)

Exercise 6: validate with EXPLAIN ANALYZE

Capture the PostgreSQL plan for your selectinload(Author.books) query with WHERE name = 'tolkien'. What nodes do you see? Does it use indexes?

from sqlalchemy import select
from sqlalchemy.orm import selectinload

stmt = (
    select(Author)
    .options(selectinload(Author.books))
    .where(Author.name == "tolkien")
)

# Enable echo=True to capture the SQL
result = (await session.scalars(stmt)).all()

-- Query 1
SELECT authors.id, authors.name FROM authors WHERE authors.name = 'tolkien';

-- Query 2
SELECT books.id, books.title, books.author_id, books.published_year
FROM books WHERE books.author_id IN (41);

EXPLAIN ANALYZE SELECT * FROM authors WHERE name = 'tolkien';

Index Scan using idx_authors_name on authors  (cost=0.42..8.44 rows=1 width=20) (actual time=0.045..0.046 rows=1 loops=1)
  Index Cond: (name = 'tolkien')
Planning Time: 0.215 ms
Execution Time: 0.062 ms

EXPLAIN ANALYZE SELECT * FROM books WHERE author_id IN (41);

Bitmap Heap Scan on books  (cost=4.45..82.50 rows=20 width=44) (actual time=0.124..0.382 rows=20 loops=1)
  Recheck Cond: (author_id = ANY ('{41}'::integer[]))
  Heap Blocks: exact=20
  ->  Bitmap Index Scan on idx_books_author_id  (cost=0.00..4.44 rows=20 width=0) (actual time=0.085..0.085 rows=20 loops=1)
        Index Cond: (author_id = ANY ('{41}'::integer[]))
Planning Time: 0.245 ms
Execution Time: 0.452 ms

Summary and next step

In this capsule you learned:

• joinedload: 1 query with a LEFT JOIN. Optimal for 1:1 and small 1:N without LIMIT on the parent.
• selectinload: 2 queries (parent + WHERE id IN (...)). Optimal for large 1:N, nested loads, and queries with LIMIT/OFFSET on the parent.
• Cartesian explosion: joinedload with large 1:N multiplies duplicated rows. For tolkien × 50 books × 100 reviews = 5,000 rows vs 5,151 unique with selectinload.
• Decision matrix: 1:1 → joinedload. Large 1:N → selectinload. When in doubt, selectinload.
• Combine strategies in one query: joinedload for 1:1 + selectinload for 1:N in parallel.
• .unique() mandatory with joinedload 1:N so SQLAlchemy deduplicates.
• echo=True and EXPLAIN ANALYZE to validate that the SQL and the plan are what you expected.

Before moving on you should be able to:

• Predict how many SQL queries any select().options(...) generates.
• Look at an endpoint and choose between joinedload and selectinload, justifying it with cardinality.
• Capture SQL with echo=True and validate with EXPLAIN.
• Combine strategies for queries with multiple relationships.

Next capsule — subqueryload and when to use it. SQLAlchemy offers a third strategy: subqueryload. It's legacy (predates selectinload), generates SQL with a correlated subquery, and is almost never the right choice in SQLAlchemy 2.0. But it still appears in legacy code and sometimes there are cases where it wins. Capsule 05 shows you the SQL it generates, the cases where it still makes sense, and the logic for recognizing it in legacy code and migrating it.

Resources

1. SQLAlchemy 2.0 — Joined Eager Loading — the official reference for joinedload.
2. SQLAlchemy 2.0 — Select IN Loading — the official reference for selectinload.
3. SQLAlchemy 2.0 — Cartesian Products and Aliasing — explains why JOINs duplicate rows.
4. Mike Bayer — "Why selectin loading is the new default" — the 2.0 migration section explaining why selectinload is preferred.
5. Use The Index, Luke — "Joins" — PostgreSQL JOIN fundamentals useful for understanding why a cartesian explosion is expensive.
6. PostgreSQL Documentation — IN Operator — how PostgreSQL handles WHERE col IN (list), which is the basis of selectinload.
7. Real Python — "Eager vs Lazy Loading in SQLAlchemy" — an accessible review of the concepts with examples.

Module 4 — Database Performance & Query Tuning Guide