Django Conditional Expressions: Case, When & Q

Django conditional expressions let you embed SQL CASE/WHEN logic directly inside an ORM query, so the database evaluates if … elif … else branches per row and returns a computed value — without extra queries or Python-side loops. The building blocks are Case and When (plus Q, F, and Value), and they work inside annotate(), aggregate(), filter(), and update().

Before conditional expressions existed (added in Django 1.8 and refined ever since), the only options were raw SQL or hitting the database multiple times. Today, on Django 5.x, you can express branching logic once and let the database do the work in a single round-trip.

Key takeaways

• Case + When compile to SQL CASE WHEN … THEN … ELSE … END; conditions are checked top to bottom and the first match wins.
• Wrap literal values in Value(), and set output_field whenever the result type is ambiguous (mixed or non-obvious types).
• Conditional expressions run in the database, not in Python — usable inside annotate(), aggregate(), filter(), and update().
• Filtered aggregation — Count('id', filter=Q(...)) — is the cleaner Django 5.x way to count or sum a subset.
• Collapsing several conditional UPDATEs into one Case cuts multiple round-trips down to a single query.
• Helpers like Coalesce, Greatest, and Least cover null handling and min/max without writing a full Case.

What are Django conditional expressions?

A conditional expression is an ORM construct that produces a value based on one or more conditions, evaluated per row by the database. It is the ORM equivalent of a Python if … elif … else, compiled to a SQL CASE statement. Because the logic runs server-side, you avoid pulling rows into Python just to branch on them.

All examples below use this Django 5.x model:

from django.db import models


class Employee(models.Model):
    ACCOUNT_TYPE_CHOICES = [
        ("REGULAR", "Regular"),
        ("GOLD", "Gold"),
        ("PLATINUM", "Platinum"),
    ]
    name = models.CharField(max_length=50)
    joined_on = models.DateField()
    salary = models.DecimalField(max_digits=10, decimal_places=2)
    bonus = models.DecimalField(max_digits=10, decimal_places=2, null=True, blank=True)
    account_type = models.CharField(
        max_length=10,
        choices=ACCOUNT_TYPE_CHOICES,
        default="REGULAR",
    )

How do Case and When work?

A When() object is a single condition-plus-result. A Case() is the ordered list of When() branches with an optional default: it tries each branch in order and returns the result of the first match, or default if nothing matches (or None when no default is given).

A When() takes a condition and a then value. The condition can be keyword lookups passed positionally, or a Q object passed as condition= for OR / complex logic. The then value can be a literal, a field reference via F(), or another expression.

from datetime import date
from django.db.models import F, Q, When

# then can reference another field's value
When(joined_on__lt=date(2014, 1, 1), then=F("salary"))

# then can be a fixed/literal value
When(joined_on__lt=date(2014, 1, 1), then=5)

# pass a Q object as condition= for OR / nested logic
When(condition=Q(name__startswith="John") | Q(name__startswith="Paul"), then=True)

Used inside annotate(), a Case becomes a computed column. Here we grade employees by salary band — exactly the if/elif/else ladder, expressed once and computed in SQL:

from django.db.models import Case, CharField, Value, When

Employee.objects.annotate(
    grade=Case(
        When(salary__gte=90000, then=Value("A")),
        When(salary__gte=70000, then=Value("B")),
        When(salary__gte=50000, then=Value("C")),
        default=Value("F"),
        output_field=CharField(),
    )
)

Two rules to remember. First, wrap literals in Value() so the ORM treats "A" as data, not a column name. Second, set output_field when the result type is ambiguous — here every branch returns text, so CharField() tells the ORM (and the database) the column type. The whole expression is evaluated by the database, so grade is available for further filtering, ordering, or aggregation in the same query.

How do you do conditional aggregation?

Conditional aggregation counts or sums only the rows that match a condition. On modern Django, the cleanest approach is the filter= argument on aggregate functions, which takes a Q object and compiles to COUNT(...) FILTER (WHERE ...) (or an equivalent CASE):

from django.db.models import Count, Q, Sum

Employee.objects.aggregate(
    total=Count("id"),
    platinum=Count("id", filter=Q(account_type="PLATINUM")),
    gold=Count("id", filter=Q(account_type="GOLD")),
    platinum_payroll=Sum("salary", filter=Q(account_type="PLATINUM")),
)

Before filter= existed, the same result needed Count(Case(When(...), then=1)) — still valid, just noisier. For min/max-style rollups across rows, see our guide to aggregating minimum and maximum values from a Django model. The Q objects driving these filters are covered in depth in querying with Django Q objects.

How do you use Case in an update()?

This is where conditional expressions pay off most. Say you want to set account_type to PLATINUM for 3+ years of tenure, GOLD for 2+ years, otherwise REGULAR. The naive version issues a separate UPDATE per tier:

from datetime import date, timedelta

above_3yrs = date.today() - timedelta(days=365 * 3)
above_2yrs = date.today() - timedelta(days=365 * 2)

# Two database round-trips
Employee.objects.filter(joined_on__lt=above_3yrs).update(account_type="PLATINUM")
Employee.objects.filter(joined_on__lt=above_2yrs).update(account_type="GOLD")

Fold the branches into one Case and the whole thing becomes a single UPDATE statement:

from datetime import date, timedelta
from django.db.models import Case, Value, When

above_3yrs = date.today() - timedelta(days=365 * 3)
above_2yrs = date.today() - timedelta(days=365 * 2)

Employee.objects.update(
    account_type=Case(
        When(joined_on__lte=above_3yrs, then=Value("PLATINUM")),
        When(joined_on__lte=above_2yrs, then=Value("GOLD")),
        default=Value("REGULAR"),
    )
)

One round-trip instead of several means less lock contention and a faster response — a small win per call that compounds at scale. For more patterns like this, see Django database access optimization.

What about Q objects, F, and Value?

Three companions make conditional expressions practical:

• Q builds composable conditions with &, |, and ~ — pass one as When(condition=...) for anything beyond a single keyword lookup.
• F references a column's value, so you can compare or compute against other fields entirely in SQL.
• Value wraps a Python literal as a SQL value, which is required inside then=/default= so it isn't mistaken for a field name.

For common shapes you do not even need a full Case. The functions module ships Coalesce (first non-null), Greatest, and Least:

from django.db.models import F, Value
from django.db.models.functions import Coalesce, Greatest, Least

# Treat a NULL bonus as 0, then compute total comp in the database
Employee.objects.annotate(
    total_comp=F("salary") + Coalesce("bonus", Value(0)),
)

# Pick the larger / smaller of two values per row
Employee.objects.annotate(
    floor_pay=Greatest("salary", Value(50000)),
    capped_bonus=Least(Coalesce("bonus", Value(0)), Value(10000)),
)

Which approach should you use?

Conditional expressions are one of several ways to compute derived values. Pick by where the work should run and how readable the result is.

Approach	Runs in the database?	Readability	Best for
Case / When	Yes	Medium (verbose)	Per-row if/elif/else mapping to a computed column
Filtered aggregation Count('id', filter=Q(...))	Yes	High	Counting or summing a subset in one pass
Coalesce / Greatest / Least	Yes	High	Null handling and min/max without a full Case
Python-side post-processing	No	High (familiar)	Tiny result sets or logic SQL cannot express

When the ORM genuinely cannot express the logic, drop to raw SQL queries in Django or attach computed columns with the extra() queryset method — though both are last resorts compared to a clean Case.

Putting it together

Conditional expressions move branching logic out of Python and into the database, where it belongs — fewer queries, less data over the wire, and faster responses. Reach for Case/When when you need a computed column, filter= for subset rollups, and the function helpers for null/min/max.

Need help untangling slow ORM code or modelling complex business rules at the query layer? Our team offers Django development services and has shipped 50+ projects on Django and PostgreSQL.

Frequently Asked Questions

What are conditional expressions in Django?

Conditional expressions are ORM constructs — chiefly Case and When — that produce a value based on one or more conditions evaluated per row by the database. They compile to a SQL CASE WHEN … THEN … ELSE … END statement, so they behave like a Python if … elif … else but run inside the query rather than in your application code.

Do I always need output_field?

No. Django infers output_field when every branch clearly returns the same type. You must set it when the result type is ambiguous — for example mixing numeric and text results, or when the ORM cannot deduce a single type. When unsure, set it explicitly (e.g. output_field=CharField()) to avoid a FieldError at query time.

What is the difference between Case/When and filtered aggregation?

Case/When computes a per-row value (a derived column), while filtered aggregation — Count('id', filter=Q(...)) — rolls a subset of rows up into a single number. Use Case to label or transform each row; use filtered aggregation to count or sum only matching rows. Both run in the database, but filtered aggregation is shorter and clearer for subset totals.

Can I use conditional expressions in update()?

Yes. Passing a Case to .update() lets one statement set a field differently per row based on conditions, replacing several conditional UPDATE calls with a single database round-trip. This reduces lock contention and improves response time, which matters on large tables.

How do I use a Q object inside When()?

Pass it as the condition keyword: When(condition=Q(name__startswith="A") | Q(name__startswith="B"), then=Value("group1")). Use condition= whenever you need OR, NOT, or nested logic; simple single lookups can be passed positionally as keyword arguments instead, like When(salary__gte=90000, then=Value("A")).

Does a conditional expression run in Python or in the database?

It runs in the database. Django translates the expression to SQL (typically a CASE statement) and the database evaluates it for every row during query execution. That is why conditional expressions reduce query counts and data transfer compared with fetching rows and branching in Python — the work happens server-side before results are returned.