In Django's ORM, how do select_related() and prefetch_related() differ, and which relations can each one load?

middlemust knowfreq 84%

answer

one statement versus several
database join or Python stitching
single-valued versus many-valued links
IN list over collected keys

basics

~20 s

select_related() adds a SQL JOIN and loads single-valued links (ForeignKey, OneToOneField) in the same query. prefetch_related() runs one extra query per relation hop with an IN list of keys and matches rows in Python, so it also loads reverse ForeignKey and ManyToMany links.

solid answer

~40 s

select_related('customer') extends the main query with a JOIN, so each order comes back with its customer in one round trip. It is limited to single-valued links (forward ForeignKey, OneToOneField, and a reverse one-to-one), because joining a many side would repeat each parent row per child. prefetch_related('items') leaves the main query alone: after it runs, Django collects the orders' keys, runs one more query like WHERE order_id IN (...), groups the items by order in Python, and pre-fills each order.items.all(). That handles reverse ForeignKey, ManyToManyField and generic relations, and it works on a ForeignKey too. They combine: Order.objects.select_related('customer').prefetch_related('items__product') runs three queries however many orders are listed.

`select_related('customer')` extends the main query with a `JOIN`, so each order comes back with its customer in one round trip. It is limited to single-valued links (forward `ForeignKey`, `OneToOneField`, and a reverse one-to-one), because joining a many side would repeat each parent row per child. `prefetch_related('items')` leaves the main query alone: after it runs, Django collects the orders' keys, runs one more query like `WHERE order_id IN (...)`, groups the items by order in Python, and pre-fills each `order.items.all()`. That handles reverse `ForeignKey`, `ManyToManyField` and generic relations, and it works on a `ForeignKey` too. They combine: `Order.objects.select_related('customer').prefetch_related('items__product')` runs three queries however many orders are listed.

code

python · 12 lines

python

from django.shortcuts import render

from .models import Order


def order_list(request):
    orders = (
        Order.objects.select_related('customer')      # JOIN: orders + customers
        .prefetch_related('items__product')          # +1 items query, +1 products query
        .order_by('-placed_at')[:50]
    )
    return render(request, 'shop/order_list.html', {'orders': orders})

go deeper

for a junior

Know that select_related joins single-valued links and prefetch_related loads collections with a separate query, and give one example of each.

for a middle

Walk through the prefetch steps: main query, IN query on collected keys, grouping in Python, and the pre-filled related manager; then count queries for a combined chain.

for a senior

Discuss when a prefetch beats a join on a foreign key, the cost of huge IN lists and held memory, and when a bare string must become a Prefetch object.

for a principal

Treat the choice as a trade between round trips, row width and memory, made per page from measured query counts rather than a blanket rule.

Two tools for one problem

Both select_related() and prefetch_related() are QuerySet methods that load related objects ahead of time, so that code reading order.customer or looping over order.items.all() finds the data already in memory instead of triggering a query per row. They differ in how they fetch, and that difference decides which relations each can handle.

The examples use a shop app: Order has a ForeignKey to Customer; OrderItem has a ForeignKey to Order with related_name='items' and a ForeignKey to Product.

select_related(): one query with joins

select_related('customer') adds a JOIN to the main query and selects the customer's columns alongside the order's. Django then builds both objects from each result row and caches the customer on its order.

It follows single-valued links only: forward ForeignKey, forward OneToOneField, and a reverse OneToOneField named by its related_name.
Asking it for items (a reverse ForeignKey) or a ManyToManyField raises FieldError, because joining a many side would repeat every order row once per item.
Everything arrives in one round trip; the cost is a wider row and a more complex query plan.

prefetch_related(): one extra query per lookup

prefetch_related('items') leaves the main query alone. When the QuerySet is evaluated, Django:

runs the main query and builds the Order instances;
collects their keys and runs one more query, roughly SELECT ... FROM shop_orderitem WHERE order_id IN (...);
groups the returned items by order_id in Python;
stores each group as the pre-filled result of that order's order.items.all().

Because the stitching happens in Python, this works for many-valued links: reverse ForeignKey, ManyToManyField and GenericRelation. It also works on a forward ForeignKey, costing a second query instead of a join. A multi-hop lookup such as 'items__product' adds one query per hop, and if an earlier hop was already loaded by select_related(), Django detects that and skips re-fetching it.

Side by side

Aspect	`select_related()`	`prefetch_related()`
SQL shape	JOIN inside the main query	separate query with an `IN` list
Queries added	none	one per lookup hop
Relations	forward FK, one-to-one in both directions	reverse FK, many-to-many, generic relations, and FK too
Where rows are matched	in the database	in Python
Main risk	wide or repeated joined data	very large `IN` lists on big result sets
When it runs	as part of the main query	right after the main query is evaluated

The order list page with both

The page shows each order's customer and its line items with product names:

python

orders = (
    Order.objects
    .select_related('customer')
    .prefetch_related('items__product')
    .order_by('-placed_at')[:50]
)

This runs three queries no matter how many orders are listed: orders joined to customers, then all items for those orders, then all products for those items. Without either call the same page would run one query for the orders plus, per order, one for the customer and one for the items, plus one per item for its product.

Choosing between them

A single-valued link rendered on every row: select_related() is usually the answer.
A collection per row: prefetch_related() is the only one of the two that can load it.
A foreign key pointing at a few wide rows shared by many parents can be cheaper to prefetch, because a join repeats the wide row for every parent.
A prefetch over tens of thousands of parents produces an equally long IN list, which some databases handle poorly; profile before assuming it is free.
When the related rows need filtering, ordering or their own joins, prefetch with a Prefetch object instead of a bare string.

Details that trip people up

The lookup is the attribute name on the instance: 'items' here because of related_name='items'; without a related_name, the reverse accessor and the lookup would be 'orderitem_set'.
The prefetch query runs after the main query, and prefetch_related() does nothing to stop another connection from changing rows in between, so an order deleted in that window can appear with no items.
Chaining prefetch_related() calls accumulates lookups, and prefetch_related(None) clears them; select_related(None) does the same for joins.

One interview trap: prefetch_related() is not a join done later. It is a separate batched query whose results are matched to parents in Python, which is why its query count grows with the number of lookups, not the number of rows. Another: it is not free memory-wise, because the main result and every prefetched related object are held in memory together once the QuerySet is evaluated.

## Two tools for one problem Both `select_related()` and `prefetch_related()` are `QuerySet` methods that load related objects **ahead of time**, so that code reading `order.customer` or looping over `order.items.all()` finds the data already in memory instead of triggering a query per row. They differ in *how* they fetch, and that difference decides which relations each can handle. The examples use a shop app: `Order` has a `ForeignKey` to `Customer`; `OrderItem` has a `ForeignKey` to `Order` with `related_name='items'` and a `ForeignKey` to `Product`. ## select_related(): one query with joins `select_related('customer')` adds a **JOIN** to the main query and selects the customer's columns alongside the order's. Django then builds both objects from each result row and caches the customer on its order. - It follows **single-valued** links only: forward `ForeignKey`, forward `OneToOneField`, and a reverse `OneToOneField` named by its `related_name`. - Asking it for `items` (a reverse `ForeignKey`) or a `ManyToManyField` raises `FieldError`, because joining a many side would repeat every order row once per item. - Everything arrives in **one round trip**; the cost is a wider row and a more complex query plan. ## prefetch_related(): one extra query per lookup `prefetch_related('items')` leaves the main query alone. When the QuerySet is evaluated, Django: 1. runs the main query and builds the `Order` instances; 2. collects their keys and runs **one more query**, roughly `SELECT ... FROM shop_orderitem WHERE order_id IN (...)`; 3. groups the returned items by `order_id` **in Python**; 4. stores each group as the pre-filled result of that order's `order.items.all()`. Because the stitching happens in Python, this works for **many-valued** links: reverse `ForeignKey`, `ManyToManyField` and `GenericRelation`. It also works on a forward `ForeignKey`, costing a second query instead of a join. A multi-hop lookup such as `'items__product'` adds one query per hop, and if an earlier hop was already loaded by `select_related()`, Django detects that and skips re-fetching it. ## Side by side | Aspect | `select_related()` | `prefetch_related()` | |---|---|---| | SQL shape | JOIN inside the main query | separate query with an `IN` list | | Queries added | none | one per lookup hop | | Relations | forward FK, one-to-one in both directions | reverse FK, many-to-many, generic relations, and FK too | | Where rows are matched | in the database | in Python | | Main risk | wide or repeated joined data | very large `IN` lists on big result sets | | When it runs | as part of the main query | right after the main query is evaluated | ## The order list page with both The page shows each order's customer and its line items with product names: ```python orders = ( Order.objects .select_related('customer') .prefetch_related('items__product') .order_by('-placed_at')[:50] ) ``` This runs **three** queries no matter how many orders are listed: orders joined to customers, then all items for those orders, then all products for those items. Without either call the same page would run one query for the orders plus, per order, one for the customer and one for the items, plus one per item for its product. ## Choosing between them - A single-valued link rendered on every row: `select_related()` is usually the answer. - A collection per row: `prefetch_related()` is the only one of the two that can load it. - A foreign key pointing at a few wide rows shared by many parents can be cheaper to prefetch, because a join repeats the wide row for every parent. - A prefetch over tens of thousands of parents produces an equally long `IN` list, which some databases handle poorly; profile before assuming it is free. - When the related rows need filtering, ordering or their own joins, prefetch with a `Prefetch` object instead of a bare string. ## Details that trip people up - The lookup is the **attribute name** on the instance: `'items'` here because of `related_name='items'`; without a `related_name`, the reverse accessor and the lookup would be `'orderitem_set'`. - The prefetch query runs **after** the main query, and `prefetch_related()` does nothing to stop another connection from changing rows in between, so an order deleted in that window can appear with no items. - Chaining `prefetch_related()` calls accumulates lookups, and `prefetch_related(None)` clears them; `select_related(None)` does the same for joins. One interview trap: `prefetch_related()` is not a join done later. It is a separate batched query whose results are matched to parents in Python, which is why its query count grows with the number of **lookups**, not the number of **rows**. Another: it is not free memory-wise, because the main result and every prefetched related object are held in memory together once the QuerySet is evaluated.

Can prefetch_related() be used on a plain ForeignKey such as customer?
Yes. It runs a second query selecting the customers whose ids appear in the loaded orders and attaches each to its order. That is usually worse than a join, but it can win when many orders share a few wide customer rows, or when the customer query itself needs a custom `Prefetch` queryset.
When do the prefetch queries actually run?
Not when `prefetch_related()` is called. The method only records the lookups; the extra queries run right after the main query when the QuerySet is evaluated, for example when a template loop starts iterating it.
How many queries does Order.objects.prefetch_related('items__product') run?
Three: one for the orders, one for all their items, and one for all products referenced by those items. Each hop in a prefetch lookup costs one query, regardless of how many orders or items there are.

select_related is one trip to the warehouse where each order box already has its customer card taped on. prefetch_related is a second trip carrying a list of every order number, bringing back one crate of items that you sort into the order boxes at home.

saying these in an interview costs you the question

prefetch_related() does a SQL JOIN, just a larger one than select_related()
select_related() works on a ManyToManyField if you name it explicitly
prefetch_related() runs one query per parent row, so it only moves the N+1
select_related() and prefetch_related() cannot be combined on one QuerySet
prefetch_related() executes its queries at the moment it is called

In Django's ORM, how do select_related() and prefetch_related() differ, and which relations can each one load?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Two tools for one problem

select_related(): one query with joins

prefetch_related(): one extra query per lookup

Side by side

The order list page with both

Choosing between them

Details that trip people up

▾common follow-ups3 questions

▾analogy~20 s

▾red flags5 flags

Two tools for one problem

select_related(): one query with joins

prefetch_related(): one extra query per lookup

Side by side

The order list page with both

Choosing between them

Details that trip people up

answer

go deeper