skip to content

In Django's ORM, what SQL do the field lookups __iexact, __icontains, __in, __range and __isnull produce when searching parking permits?

level: juniorimportance: should knowfreq 55%

answer

  1. double underscore then operator
  2. exact is the default
  3. case folding with UPPER
  4. BETWEEN includes both ends
  5. None becomes IS NULL

basics

~10 s

A lookup is field__operator=value. __iexact and __icontains compare case-insensitively (UPPER ... LIKE on PostgreSQL), __in becomes IN (...), __range becomes an inclusive BETWEEN, and __isnull=True becomes IS NULL; exact=None also becomes IS NULL.

solid answer

~30 s

Django lookups follow `field__lookup=value`, and a bare `field=value` means `__exact`. On PostgreSQL, `plate__iexact='ab123'` becomes `UPPER(plate::text) = UPPER('ab123')` and `plate__icontains='12'` becomes `UPPER(plate::text) LIKE UPPER('%12%')`, with `%` and `_` in the value escaped. `status__in=['active', 'suspended']` becomes `IN (...)`; `None` is dropped from the list, and an empty list returns no rows without sending any query. `valid_from__range=(start, end)` becomes `BETWEEN start AND end`, inclusive at both ends. `valid_until__isnull=True` becomes `IS NULL`, and `valid_until=None` is translated to the same thing, while `valid_until__gt=None` raises `ValueError`. Lookups chain across relations: `zone__code__iexact='a'` joins to Zone.

code

python · 14 lines
python
from datetime import date

from permits.models import Permit

open_ended_q1 = Permit.objects.filter(
    status__in=[Permit.Status.ACTIVE, Permit.Status.SUSPENDED],
    zone__code__in=['A', 'B'],
    valid_from__range=(date(2026, 1, 1), date(2026, 3, 31)),  # inclusive
    valid_until__isnull=True,
)

by_plate = Permit.objects.filter(plate__icontains='12')      # UPPER(...) LIKE UPPER('%12%')
nothing = Permit.objects.filter(status__in=[])               # empty result, no SQL sent
# Permit.objects.filter(valid_until__gt=None)  -> ValueError

go deeper

for a junior

Recall the double-underscore syntax, that plain field=value means exact, and what in, range, isnull and icontains roughly do.

for a middle

Explain the SQL each lookup generates on your database, BETWEEN's inclusive ends, and how None is handled by exact, in and other lookups.

for a senior

Anticipate performance and correctness traps: UPPER() defeating plain indexes, DateTimeField ranges losing the last day, and duplicates from multi-valued joins.

for a principal

Decide where case-insensitive search belongs, in functional indexes, normalised columns or a search engine, as the data and query mix grow.

## The lookup syntax In Django's ORM, every keyword argument to `filter()`, `exclude()` or `get()` is a **field lookup**: a field name, optionally a path through relations, then two underscores and a lookup name. `status='active'` is shorthand for `status__exact='active'`. The lookup name picks the SQL operator; Django adds parameter binding, quoting and backend-specific details. The examples below search a `Permit` model with `plate`, `status`, `valid_from`, `valid_until` (nullable, open-ended permits) and a `zone` foreign key. ## What each lookup produces | Lookup | Example | SQL on PostgreSQL (simplified) | |---|---|---| | `exact` | `status='active'` | `status = 'active'` | | `exact` with `None` | `valid_until=None` | `valid_until IS NULL` | | `iexact` | `plate__iexact='ab123'` | `UPPER(plate::text) = UPPER('ab123')` | | `icontains` | `plate__icontains='12'` | `UPPER(plate::text) LIKE UPPER('%12%')` | | `in` | `status__in=['active', 'suspended']` | `status IN ('active', 'suspended')` | | `range` | `valid_from__range=(d1, d2)` | `valid_from BETWEEN d1 AND d2` | | `isnull` | `valid_until__isnull=True` | `valid_until IS NULL` | | spanning | `zone__code='A'` | `INNER JOIN zone ... WHERE zone.code = 'A'` | Other backends differ in detail: SQLite, for instance, uses `LIKE ... ESCAPE` for `iexact` and `icontains`. The meaning stays the same, which is the point of the lookup layer. ## Details that trip people up **Case-insensitive lookups.** On PostgreSQL Django uses `UPPER()` on both sides with `LIKE`, not `ILIKE`. An ordinary index on `plate` does not serve `UPPER(plate)`; a functional index on `Upper('plate')` does. In `contains` and `icontains`, Django escapes `%` and `_` in your value, so a search for `50%` matches the literal percent sign. **`__in`.** - Accepts any iterable, or a QuerySet, which becomes a subquery: `zone__in=Zone.objects.filter(city='Leeds')`. - `None` inside the list is discarded, because `NULL` never equals anything; add `Q(field__isnull=True)` if nulls should match. - An empty list short-circuits: Django knows no row can match and returns an empty result **without sending SQL**. **`__range`.** 1. `BETWEEN` is **inclusive** at both ends. 2. On a `DateField` like `valid_from`, `(date(2026, 1, 1), date(2026, 3, 31))` covers the whole first quarter. 3. On a `DateTimeField`, the end date means midnight at its start, so `created__range=(date(2026, 1, 1), date(2026, 3, 31))` misses almost all of 31 March; use `created__date__range` or a half-open `gte`/`lt` pair. **`__isnull` and `None`.** - `__isnull` takes only `True` or `False`; anything else raises `ValueError`. - `field=None` and `field__iexact=None` are converted to `IS NULL`; any other lookup with `None`, such as `valid_until__gt=None`, raises `ValueError: Cannot use None as a query value`. ## Spanning relations Double underscores also walk relations: `zone__code__iexact='a'` joins `Permit` to `Zone` and applies `iexact` to `Zone.code`. Forward foreign keys produce an inner join; reverse relations use the `related_name` or default query name, such as `violations__paid=False`. Filtering through a **multi-valued** relation can return the same permit several times, one per matching related row, which is why such queries often end with `.distinct()`. ## Transforms before the lookup Some path segments are **transforms** rather than fields: they change the value before the final lookup applies. - `valid_from__year=2026` extracts the year from a date; on many backends Django rewrites it as a `BETWEEN` over the year's first and last day so an index on `valid_from` can still be used. - `valid_from__month`, `__day`, `__week_day` and `__quarter` extract other parts. - On a `DateTimeField`, `created__date=date(2026, 3, 31)` compares the date part, taking the current time zone into account when `USE_TZ` is on. - Transforms can be followed by any lookup: `valid_from__year__gte=2025`. Knowing which segment is a relation, a transform or the final lookup is what lets you read a long lookup such as `zone__code__iexact` at a glance: `zone` is a relation, `code` a field, `iexact` the lookup. ## Errors you will meet - A misspelled field raises `FieldError` at `filter()` time, listing the valid choices. - An unknown lookup name, such as `status__like='a'`, raises `FieldError` for an unsupported lookup. - A wrong value type is converted by the field when possible; values that cannot be converted, such as a non-date string for a `DateField`, raise `ValidationError` or `ValueError` from the field's conversion. ## A realistic search The parking office wants active or suspended permits in zones A and B that start in the first quarter and have no end date: - `status__in=['active', 'suspended']` - `zone__code__in=['A', 'B']` - `valid_from__range=(date(2026, 1, 1), date(2026, 3, 31))` - `valid_until__isnull=True` All keyword arguments in one `filter()` call are joined with `AND`.

  • Why might plate__iexact be slow on a large PostgreSQL table even though plate is indexed?
    Django emits `UPPER(plate::text) = UPPER(%s)`, and a plain B-tree index on `plate` cannot serve an expression on the column. Add a functional index, for example `models.Index(Upper('plate'), name='permit_plate_upper')`, or store a normalised copy of the plate. `EXPLAIN` on the generated SQL confirms whether the index is used.
  • How do you match permits whose status is 'expired' or NULL?
    `status__in=['expired', None]` does not work, because Django drops `None` from `IN` lists. Combine two conditions with a `Q` object: `Permit.objects.filter(Q(status='expired') | Q(status__isnull=True))`. The same applies to any nullable column.

saying these in an interview costs you the question

  • icontains on PostgreSQL is translated to ILIKE
  • __range excludes the upper bound like Python's range()
  • status__in=[] makes Django send IN () to the database
  • filter(field__gt=None) is treated as IS NULL
  • Putting None in an __in list matches NULL rows