Advanced ORM Queries

Correlated subqueries, window functions, and the raw SQL escape hatches

OuterRef bridges inner → outeronly meaningful inside Subquery()/Exists()
Exists() beats Count()==0stops at the first match instead of tallying every row
Window() keeps rows ungroupedunlike values()+annotate(), no rows are collapsed
params, never string interpolationthe only real defense against SQL injection in raw SQL

1.Subquery, OuterRef, and Exists

Subquery(inner_queryset) embeds a genuine correlated SQL subquery, computed per-row of the outer query, returning an actual value (e.g. the newest comment's email per post). OuterRef("field") is how the INNER queryset references a field from the OUTER query — it works like F(), but resolution is deferred until the outer QuerySet actually runs. Exists(inner_queryset) is a Subquery subclass returning only True/False, and stops scanning at the first match — faster than a Subquery when only presence matters, not an actual value.

Key features

OuterRef("field") references a field from the OUTER queryset, used inside the .filter() of the INNER (subquery) queryset
Subquery(queryset) embeds that inner queryset as an actual value-returning subquery — usually needs .values("field")[:1] to return exactly one scalar column
Exists(queryset) is a Subquery subclass returning True/False — no .values() needed, since it never returns actual row data, just presence
~Exists(queryset) expresses NOT EXISTS — useful for "posts with no comments," a shape a plain Count()==0 check would need an extra query for
Exists() typically outperforms a Subquery/Count()-based existence check, since the database can stop at the first matching row instead of scanning or counting every match

Subquery vs Exists

Subquery vs Exists
Tool	Returns	Use when
Subquery(qs.values(...)[:1])	an actual scalar value per outer row	you need the VALUE (e.g. newest comment's email)
Exists(qs)	True / False per outer row	you only need to know IF a match exists
~Exists(qs)	True / False, negated	"has none" queries — NOT EXISTS

OuterRef bridges the inner subquery back to each outer row

current rowused in.filter()annotatedback

Post.objects (outer)

one row at a time

OuterRef("pk")

points back to that row

Comment.objects.filter(post=OuterRef("pk"))

the inner queryset

Subquery(...) or Exists(...)

value, or True/False

Command

python

inner = Model.objects.filter(related=OuterRef("pk"))
Outer.objects.annotate(x=Subquery(inner.values("field")[:1]))
Outer.objects.annotate(y=Exists(inner))

OuterRef bridges an inner (subquery) queryset back to a field on the outer query — it only works inside something wrapped in Subquery()/Exists(). Subquery returns an actual value (usually via .values(...)[:1]); Exists returns just True/False and is the faster, correct tool whenever only presence/absence matters.

2.Window functions

Window(expression, partition_by=[...], order_by=..., frame=...) computes a value across a set of related rows (a "partition") WITHOUT collapsing them into one row — unlike annotate()+values() grouping, every original row survives, each annotated with its own window-computed value (e.g. "this movie's rating, plus the average rating of every movie from the same studio and genre," on the SAME row).

Key features

Window(expression=Avg("rating"), partition_by=[F("studio"), F("genre")], order_by="released__year") computes a per-row value without collapsing rows
partition_by groups rows for the computation's context — like a GROUP BY, but rows stay ungrouped in the output
order_by controls the order the window function is applied in — required for order-sensitive computations (running totals, ranks), optional for simple aggregates like Avg
frame (RowRange or ValueRange) narrows the partition further — RowRange(start=-2, end=2) means "2 rows before through 2 rows after," used for moving averages/windows
Without an explicit frame, the default is the entire partition — every row in that partition contributes to the computation

Window() arguments

Window() arguments
Argument	Purpose
expression	the aggregate/function to compute (Avg, Sum, Rank, RowNumber, ...)
partition_by	which rows form each computation's context — like GROUP BY, but rows stay ungrouped
order_by	ordering within the partition — required for order-sensitive functions
frame	narrows the partition further (e.g. N rows before/after) — defaults to the whole partition

Command

python

Model.objects.annotate(
    x=Window(expression=Avg("field"), partition_by=[F("group_field")], order_by="order_field"),
)

Window() computes a value per row within a partition WITHOUT collapsing rows — unlike values()+annotate() grouping, every original row survives. partition_by sets the grouping context; order_by is required for order-sensitive functions (Rank, running totals); frame narrows further to a sub-range of the partition.

3.Raw SQL: .raw(), RawSQL, and legacy extra()

.raw("SELECT ...") runs a full raw SQL statement and maps each result row to a model instance — the ORM equivalent of a raw query for whole objects. RawSQL(sql, params) embeds a raw SQL fragment inside a normal Django query (a filter/annotation), for one piece the ORM can't express. extra() is Django's older, now-deprecated way to inject raw SQL fragments into select/where/order_by — legacy code awareness only; new code should use annotate()/F()/RawSQL instead.

Key features

.raw(sql, params) returns a RawQuerySet mapping each row to real model instances — the raw SQL must select every field the model NEEDS (deferred fields are allowed to be missing, extra columns become model attributes)
RawSQL(sql, params, output_field=...) is an EXPRESSION — used inside annotate()/filter(), not as a replacement for the whole query
params is REQUIRED (as a tuple) for both .raw() and RawSQL — it forces parameterized substitution (%s placeholders), avoiding SQL injection; never string-interpolate untrusted input directly into the SQL text
extra() takes select=/where=/params=/tables=/order_by= — considered legacy: Django's own docs recommend annotate() with F()/Value()/Func() or RawSQL for anything extra() used to handle
Never quote a %s placeholder in raw SQL ('%s') — the DB-API driver adds quoting itself when needed; a hand-added quote breaks parameterization and can reopen an injection risk

The three raw SQL tools, by scope

The three raw SQL tools, by scope
Tool	Scope	Returns
.raw(sql, params)	an entire query	a RawQuerySet of real model instances
RawSQL(sql, params)	one fragment inside a normal query	used as an annotate()/filter() expression
extra() — legacy	select/where/order_by fragments	a modified QuerySet — avoid in new code

Command

python

Model.objects.raw("SELECT ... FROM ... WHERE col = %s", [value])
queryset.annotate(x=RawSQL("some_sql_expression(%s)", (param,), output_field=SomeField()))

.raw() replaces a whole query, returning real model instances; RawSQL embeds one fragment inside an otherwise normal query. Both require params as a parameterized tuple/list — never string-interpolate a value into the SQL text. extra() is legacy — recognize it in old code, but reach for annotate()/F()/RawSQL in new code instead.

Spotted a mistake on this page? Tell us.

Read Sheet