skip to content

In Django's ORM, when is QuerySet.exists() the right check, and when is it worse than if queryset: or count()?

level: middleimportance: should knowfreq 50%

answer

  1. do you need the rows later
  2. one row is enough to answer
  3. LIMIT 1 and a constant
  4. the result cache changes the answer
  5. per-row checks belong in Exists()

basics

~20 s

QuerySet.exists() sends a SELECT 1 ... LIMIT 1 and is right when you only need a yes/no; if you will iterate the rows anyway, if queryset: is cheaper because it fetches once and caches, and count() is for when you need the number.

solid answer

~40 s

`exists()` strips the select list and ordering, adds a constant and `LIMIT 1`, so the database can stop at the first match: ideal for "does this customer have any unredeemed vouchers?" when you do nothing else with them. `if qs:` evaluates the whole QuerySet and fills its result cache, which is wasteful for a yes/no but better than `exists()` followed by a loop, because that costs two queries. `count()` runs `SELECT COUNT(*)` over every match, so `count() > 0` is the wrong existence test. If the QuerySet is already evaluated, `exists()` and `count()` answer from the cache without a query. And calling `exists()` per row in a list is itself an N+1; annotate with `Exists(OuterRef(...))` instead.

code

python · 12 lines
python
from django.db.models import Exists, OuterRef

from shop.models import Customer, Order


def can_redeem(customer):
    return customer.vouchers.filter(redeemed=False).exists()


def customers_with_open_orders():
    open_orders = Order.objects.filter(customer=OuterRef("pk"), status="open")
    return Customer.objects.annotate(has_open=Exists(open_orders))

go deeper

for a junior

Recall that exists() answers yes or no with a tiny query, and that count() is for when you need the number.

for a middle

Explain the LIMIT 1 query, the result-cache shortcut, and why checking with exists() and then iterating costs two queries.

for a senior

Spot per-row exists() calls in list views and serializers and replace them with an Exists(OuterRef) annotation or filter.

for a principal

Encourage reviewers to ask what a check is for, since the right call depends on whether the rows are used afterwards.

## Three ways to ask "is there anything?" A Django `QuerySet` is lazy: building it runs nothing. Different methods then turn it into different SQL, and choosing the wrong one costs either rows you did not need or a query you did not need. | Check | SQL sent (if not yet evaluated) | Rows transferred | Fills the result cache? | |---|---|---|---| | `qs.exists()` | `SELECT 1 AS a ... LIMIT 1` | At most one | No | | `if qs:` / `bool(qs)` | The full `SELECT` | All matches | Yes | | `qs.count()` | `SELECT COUNT(*) ...` | One number | No | | `len(qs)` | The full `SELECT` | All matches | Yes | ## How `exists()` works `exists()` clones the query, clears the select list and the ordering, adds a constant annotation and a limit of one row. The database can stop at the first matching row and send nothing but that constant back. - If the QuerySet has **already been evaluated**, `exists()` does not query at all; it returns `bool()` of the cached results. `count()` behaves the same way, returning the cache's length. - `aexists()` is the async version. - For membership of a specific object, `qs.contains(obj)` (Django 4.0+) runs a similar cheap query, whereas `obj in qs` evaluates the whole QuerySet. ## When `exists()` is the wrong tool The documentation is explicit that `exists()` can do **more** work: if you check and then use the results, you have paid for two queries. ```python # Two queries: existence check, then the fetch if vouchers.exists(): for v in vouchers: ... # One query: fetch once, cache, test the cache if vouchers: for v in vouchers: ... ``` So the rule is about intent: 1. **Only need yes/no** → `exists()`. 2. **Need the rows if there are any** → evaluate once (`if qs:` or `list(qs)`) and reuse. 3. **Need the number** → `count()`, unless the rows are already cached. 4. **Never** use `count() > 0` or `len(qs) > 0` as an existence test on an unevaluated QuerySet; both touch every match. ## The per-row trap: `exists()` in a loop A list of customers showing a "has an open order" badge is a common place to write: ```python for customer in customers: customer.has_open = customer.orders.filter(status="open").exists() ``` Each call is cheap, but there is one per customer: an N+1 of tiny queries. The database-side version asks once, with a correlated `EXISTS` subquery: ```python from django.db.models import Exists, OuterRef open_orders = Order.objects.filter(customer=OuterRef("pk"), status="open") customers = Customer.objects.annotate(has_open=Exists(open_orders)) ``` `Exists()` is an expression, so it can also be used directly in `filter()`: `Customer.objects.filter(Exists(open_orders))`. ## What the SQL looks like For `customer.vouchers.filter(redeemed=False)` the three calls send, roughly: ```sql -- exists() SELECT 1 AS "a" FROM "shop_voucher" WHERE ("customer_id" = 42 AND NOT "redeemed") LIMIT 1; -- count() SELECT COUNT(*) AS "__count" FROM "shop_voucher" WHERE ("customer_id" = 42 AND NOT "redeemed"); -- bool(qs) SELECT "id", "code", "customer_id", "redeemed", ... FROM "shop_voucher" WHERE (...); ``` The `exists()` form can stop at the first index hit. `count()` must visit every match, and the full `SELECT` also ships every column of every row to Python and builds a model instance for each. On a customer with three vouchers none of this matters; on a table where the filter matches a hundred thousand rows it is the difference between a millisecond and a noticeable pause. Each method has an async twin (`aexists()`, `acount()`, `acontains()`) with the same SQL, for use in async views. ## Rules of thumb - A yes/no gate in a view or a permission check: `exists()`. - A template that shows "no results" or the list: evaluate once, then test the list. - A per-row flag: annotate with `Exists()`; never call `exists()` inside the loop. - A badge count: `count()`, or annotate a count if it is per row.

  • Does calling exists() on a QuerySet that has already been iterated hit the database again?
    No. `QuerySet.exists()` checks the result cache first: if the QuerySet has been evaluated, it returns whether the cache is non-empty without a query. `count()` likewise returns the cached length. Only an unevaluated QuerySet sends the `SELECT ... LIMIT 1`.
  • How do you filter customers who have at least one paid order without a JOIN producing duplicates?
    Use `Customer.objects.filter(Exists(Order.objects.filter(customer=OuterRef("pk"), status="paid")))`. The correlated `EXISTS` is a semi-join, so each customer appears once, and you avoid the `distinct()` that `filter(orders__status="paid")` often needs.

saying these in an interview costs you the question

  • count() > 0 is the cheapest way to test for rows
  • exists() is always cheaper than evaluating the QuerySet
  • exists() queries again even after the QuerySet was evaluated
  • obj in queryset runs a cheap membership query
  • Calling exists() per row in a list is fine because each is fast