Django — select_related vs prefetch_related

FrameworksMiddjango

The Question

What is the difference between select_related and prefetch_related in Django?

What a Strong Answer Covers

• JOIN vs separate query
• FK vs M2M
• N+1.

Senior-Level Answer

Both methods solve the N+1 query problem in Django, but they work differently and apply to different relationship types. Choosing the wrong one still causes N+1 or unexpected query counts.

**`select_related`** performs a SQL JOIN. It fetches the related objects in a single query by joining the related table. It only works for **ForeignKey** and **OneToOne** relationships (relations that follow a single join path). It is depth-unlimited but each level adds a JOIN.

```python # Without select_related: 1 + N queries orders = Order.objects.all() for order in orders: # N queries for order.customer print(order.customer.name)

# With select_related: 1 query with JOIN orders = Order.objects.select_related('customer').all() ```

Generated SQL: `SELECT order.*, customer.* FROM order INNER JOIN customer ON order.customer_id = customer.id`

`select_related('customer__address')` traverses multiple levels with chained JOINs.

**`prefetch_related`** executes separate queries — one per relationship — and performs the join in Python. It handles **ManyToMany**, **reverse ForeignKey** (accessing the many side of a FK relation), and GenericRelatedObjectManager. It can also handle ForeignKey/OneToOne, but `select_related` is more efficient for those.

```python # Fetches authors, then a separate query for all books of those authors authors = Author.objects.prefetch_related('book_set').all() for author in authors: for book in author.book_set.all(): # uses prefetch cache, no extra query print(book.title) ```

Generated SQL: Query 1: `SELECT * FROM author`; Query 2: `SELECT * FROM book WHERE author_id IN (1, 2, 3, ...)`

**`Prefetch` object with custom querysets:** `prefetch_related(Prefetch('book_set', queryset=Book.objects.filter(published=True)))` — lets you apply filters, ordering, or annotations to the prefetched queryset. Critical when you need a subset of related objects.

**Common mistakes:** - Using `select_related` on a ManyToMany — it doesn't work; use `prefetch_related` - Calling `.filter()` on a prefetched relation in a loop — this invalidates the cache and causes N+1 again. Use `Prefetch()` with the filter applied in advance - Not using either — relying on Django's lazy evaluation in templates where the N+1 is invisible

Use `django-debug-toolbar` or `django.db.connection.queries` to verify query count.

Key Differences

What Separates a 2/3 from a 3/3

Common Mistakes

• Using select_related on ManyToMany — it doesn't apply
• Filtering a prefetched relation in a loop, invalidating the prefetch cache
• Not using django-debug-toolbar to verify the actual query count
• Confusing 'one query' (select_related) with 'fewer queries' (prefetch_related) — the goal is avoiding N+1, not always minimizing query count

Follow-Up Questions

• What happens if you do author.book_set.filter(published=True) in a loop after prefetch_related('book_set')? — The .filter() creates a new queryset — it doesn't use the prefetch cache. You get N queries. Use Prefetch('book_set', queryset=Book.objects.filter(published=True)).
• When would you choose prefetch_related over select_related for a ForeignKey? — When the related objects are large or duplicated across many rows — JOINs can produce a large result set with repeated data. Separate queries may be more efficient.
• How would you prefetch a related queryset with custom ordering and annotation? — Prefetch('reviews', queryset=Review.objects.annotate(score=Avg('rating')).order_by('-score'))
• How do you verify how many queries a view is executing? — connection.queries in DEBUG mode, django-debug-toolbar's SQL panel, or assertNumQueries() in tests.

Related Questions

• FastAPI — Async vs Sync
• Django — Signals
• SQLAlchemy — Session
• Django — Middleware
• FastAPI — Pydantic