Chapter 10 · From QuerySet to SQL
10.14 Subqueries: Subquery, Exists, OuterRef and a query inside another
A Query is an expression, and Query is how one comes to stand inside another: a clone whose aliases bump_prefix has renamed, which notes the outer query’s tables as external_aliases, and in whose conditions the ResolvedOuterRef an OuterRef left has become a column of the outer table. Subquery and Exists wrap such a query to stand as a selected column or a condition; a queryset given as the value of a filter goes the same way with no wrapper at all.
A query inside another has four things to do. It has to keep its tables apart from the outer query’s, since both may read the same table and SQL tells them apart by alias alone. It has to be able to refer to the outer query’s columns, which is what makes a subquery correlated: the first sailor of each ship, not of all ships. It has to be written wherever an expression is wanted, in parentheses, as a column of the select list, as the right side of a comparison, or as a condition under "EXISTS". And it has to know when it can match nothing, so that a condition on it can be decided without the database. The opening page, From QuerySet to SQL, says that Query is built on BaseExpression for this reason.
A query as an expression: Query.resolve_expression
Query binds a query to another: the outer query is what the inner one is bound to, and the resolved object is a copy, which the method makes with Query.clone and then does four things to.
It renames the clone’s aliases. Query is given the outer query and, where the two have the same alias_prefix, gives the clone the next prefix that is not in its own subq_aliases, "U" after the "T" every query starts with, and adds it to the subq_aliases of both queries, so that the outer query, resolved in its turn against a third, takes a prefix after it; a query whose prefix differs from the outer’s already, as one resolved a second time at a deeper level, is left as it is. The clone’s change_aliases then renames every alias in its map to the prefix and a number, relabelling each reference in the where tree, the select, the group by and the annotations, which replaces each leaf of the where tree by a relabelled copy. The docstring gives the reason, that the two queries’ aliases must not conflict, and Tables and joins: the alias map and join promotion tells the method in full.
It marks the clone with subquery true. The flag is read in several places: Query.as_sql puts the statement in parentheses, and clears the ordering on a backend that does not ignore an unnecessary one; the compiler wraps a subquery whose select list has extra columns in another select, and sets the parentheses of a compound statement by it; a tuple comparison against a subquery is refused on some backends by it; and BaseExpression is true of an expression one of whose sources carries it.
It resolves the clone’s where tree against the outer query, leaf by leaf: WhereNode has each leaf’s left side and right side resolved where they can be, and that is where a ResolvedOuterRef in a condition becomes a column. The clone of the tree that WhereNode returns is thrown away, and the resolution reaches the clone’s tree because WhereNode.clone keeps the leaf objects and _resolve_node sets the sides on them: relabelled copies where change_aliases had an alias to rename, and the queryset’s own leaves where its alias map was empty. The parts of a combined query are resolved the same way, each against the outer query, and so is each annotation; an annotation that is itself a subquery has the clone’s external aliases added to its own.
It notes the outer query’s tables. external_aliases maps each alias of the outer query’s alias map to whether that table stands under an alias other than its own name, and a comment in the constructor says it exists because a query may refer to aliases of an outer query, as one made by split_exclude does. change_aliases keeps the map up to date when the clone is renamed again, and Query reads its keys.
What follows was recorded from a running Django, on SQLite, over the shipping line of the opening page: a line at the left is Python as it was run, with its value after -> where it is a question and nothing after it where it only bound a name, and the lines set in under it are the calls it set going, nested as they were made, with only the calls the passage is about written down, -> what a call returned, and a method sometimes written as the object’s class and, in parentheses, the class whose method ran. A question that asks for a thing said answers with it put into the recording’s words, a where tree as (AND: ...).
first_hand = Sailor.objects.filter(ship=OuterRef('pk')).order_by('name').values('name')[:1]
...
sub = Subquery(first_hand)
sub.query is first_hand.query, sub.query.subquery, first_hand.query.subquery, said(sub.get_source_expressions()) -> (False, True, False, [a Query of Sailor])
outer = Ship.objects.all().query
resolved = sub.resolve_expression(outer)
type(resolved).__name__, resolved.subquery, resolved.alias_prefix, list(resolved.alias_map), resolved.external_aliases, sorted(outer.subq_aliases) -> ('Query', True, 'U', ['U0', 'U1'], {'fleet_ship': False}, ['T', 'U'])
said(resolved.where) -> (AND: RelatedExact(Col(U0, crew.Sailor.ship), Col(fleet_ship, fleet.Ship.id)))
Subquery had already cloned the queryset’s query and marked the clone, and the queryset’s own query is untouched. The resolved object is a Query, not a Subquery: why Subquery removes the wrapper is told under Subquery. Its prefix is "U" and its alias map has two entries, "U0" for the sailors’ table and "U1" for the ships’ table that the filter on "ship" joined and trim_joins took back, left in the map with a count of zero as the opening page describes. The outer query’s one table is external and not aliased, and the outer query now knows "U" as a prefix in use. The where tree compares the sailors’ "ship_id" under the new alias with a column of the outer table, Col(fleet_ship, fleet, which is what the OuterRef became.
A subquery resolved and compiled: OuterRef becomes a ResolvedOuterRef as the inner filter is built, and a Col of the outer table when the inner query is resolved against the outer
first_hand = Sailor.objects.filter(ship=OuterRef('pk')).order_by('name').values('name')[:1]
OuterRef.resolve_expression (OuterRef(pk)) -> ResolvedOuterRef(pk)
with_first = Ship.objects.annotate(first=Subquery(first_hand))
Subquery.resolve_expression (Subquery.resolve_expression)
Query.resolve_expression(query=a Query of Ship) (of a Query of Sailor)
Query.bump_prefix(a Query of Ship)
Query.change_aliases({'crew_sailor': 'U0', 'fleet_ship': 'U1'})
WhereNode.resolve_expression
ResolvedOuterRef.resolve_expression (ResolvedOuterRef(pk))
F.resolve_expression (ResolvedOuterRef(pk))
Query.resolve_ref(name='pk') -> Col(fleet_ship, fleet.Ship.id)
-> Col(fleet_ship, fleet.Ship.id)
-> Col(fleet_ship, fleet.Ship.id)
-> a Query of Sailor
-> a Query of Sailor
with_first.query.get_compiler('default').as_sql()
Query.as_sql (of a Query of Sailor) -> ('(SELECT "U0"."name" AS "name" FROM "crew_sailor" "U0" WHERE "U0"."ship_id" = ("fleet_ship"."id") ORDER BY 1 ASC LIMIT 1)', ())
The OuterRef became a ResolvedOuterRef as first_hand’s filter was built, with no outer query in sight. Subquery then resolved its one source, the query, which bumped its prefix and renamed its two aliases before the where tree was resolved; inside the where tree the ResolvedOuterRef resolved as an F does, by asking the outer query’s resolve_ref for the column named "pk" (From a name to a column: names_to_path, setup_joins and build_lookup). When the outer statement was written, Query.as_sql wrote the inner one in parentheses, with the outer column inside it. The parentheses round "fleet_ship"."id" are Lookup wrapping a compiled expression (Lookups: Lookup, BuiltinLookup and the standard comparisons), and ORDER BY 1 orders by position because the ordered column is the selected one (ORDER BY and DISTINCT: get_order_by, find_ordering_name and get_distinct).
list(Ship.objects.order_by('name')[:1].query.alias_map), list(Ship.objects.values('tonnage').query.alias_map) -> ([], ['fleet_ship'])
A resolved query is read from outside through get_external_cols, get_group_by_cols and output_field. Query walks the annotations and the where tree’s leaves, yielding each Col and, for a nested subquery, its own external columns, and keeps those whose alias is in external_aliases. Query returns those columns, so that a grouped outer query groups by the outer columns a subquery refers to rather than by the subquery; but where any of them is marked possibly_multivalued, which ResolvedOuterRef sets on a column whose name holds a "__", it returns the wrapper it was given, or the query itself, so that the whole subquery is grouped by (GROUP BY and HAVING: set_group_by, get_group_by and split_having_qualify). Query is the type a query has as an expression: the target of its one selected column, or the output field of its one selected annotation, and None where neither is exactly one.
resolved.get_external_cols(), resolved.get_group_by_cols(), resolved.contains_subquery, Ship.objects.annotate(first=Subquery(first_hand)).query.annotations['first'].contains_subquery -> ([Col(fleet_ship, fleet.Ship.id)], [Col(fleet_ship, fleet.Ship.id)], True, True)
said(Subquery(Sailor.objects.filter(name=OuterRef('crew__name')).values('id')[:1]).resolve_expression(Ship.objects.all().query).get_group_by_cols()) -> [a Query of Sailor]
The one external column is the ship’s key; the group-by columns are the same, since "pk" holds no "__"; contains_subquery is true both of the resolved query and of the annotation the outer query kept, which is such a resolved query with no wrapper round it; and a subquery whose outer reference is "crew__name" is grouped by as a whole.
OuterRef and ResolvedOuterRef
OuterRef is an F that defers its resolution by one query. An F resolves against the query it is given; an OuterRef resolves against the query outside that one. Its resolve_expression does not look at the query it is handed at all: it returns a ResolvedOuterRef of its name, or, where its name is itself an OuterRef, that inner OuterRef with one level taken off. So an OuterRef in the filter of a queryset becomes a ResolvedOuterRef as soon as the filter is built, when Query resolves the value against the inner query (The where clause: build_filter, add_q and WhereNode).
ResolvedOuterRef is an F too, and resolving it is an ordinary F’s resolution: the query it is given, which is the outer query when the inner one is resolved, finds the name with resolve_ref, a column or an annotation of the outer query. It refuses one thing, an outer expression with a window in it, with NotSupportedError (Window expressions: Window, WindowFrame and the QUALIFY rewrite), and marks the column possibly_multivalued where the name holds a "__", a comment noting that the mark should one day be reserved for the relations that are many. Its relabeled_clone returns itself, since it refers to no alias of the query being renamed, and its get_group_by_cols is empty. Its as_sql raises ValueError, saying that the queryset contains a reference to an outer query and may be used in a subquery alone: a queryset filtered with an OuterRef cannot be run on its own, and the refusal comes when its statement is written.
first=Subquery(first_hand) beside the query of first_hand once it has been resolved against it. The inner query has aliases of its own under the prefix "U", notes the outer query’s table as external, and holds in its where tree a column of the outer table, which the OuterRef became in two steps: a ResolvedOuterRef when the inner queryset was filtered, and a Col when the inner query was resolved against the outer one.Each level of nesting takes one OuterRef off. A subquery inside a subquery that needs the outermost query’s column writes OuterRef(OuterRef('pk')): the first resolution, against the innermost query, leaves OuterRef('pk'); the second, against the middle query, leaves ResolvedOuterRef('pk'); the third, against the outermost, finds the column. A line that begins with sql_of answers with the statement and its parameters.
OuterRef inside OuterRef: each level of nesting takes one off
sql_of(Port.objects.annotate(x=Subquery(Ship.objects.filter(home=OuterRef('pk')).annotate(y=Subquery(Sailor.objects.filter(ship=OuterRef(OuterRef('pk'))).values('name')[:1])).values('y')[:1])).values('name', 'x')) -> ('SELECT "fleet_port"."name" AS "name", (SELECT (SELECT "U0"."name" AS "name" FROM "crew_sailor" "U0" WHERE "U0"."ship_id" = ("fleet_port"."id") LIMIT 1) AS "y" FROM "fleet_ship" "V0" WHERE "V0"."home_id" = ("fleet_port"."id") ORDER BY "V0"."name" ASC LIMIT 1) AS "x" FROM "fleet_port" ORDER BY 1 ASC', ())
OuterRef('pk').resolve_expression(), OuterRef(OuterRef('pk')).resolve_expression(), OuterRef(OuterRef('pk')).resolve_expression().resolve_expression() -> (ResolvedOuterRef(pk), OuterRef(pk), ResolvedOuterRef(pk))
Sailor.objects.filter(ship=OuterRef('pk')) -> raises ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.
list(Sailor.objects.filter(ship=OuterRef('pk'))) -> raises ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.
In the statement the innermost query, under "U0", compares its "ship_id" with "fleet_port"."id", the outermost table’s column, across the middle query under "V0"; the middle query got "V" because "U" was already in its subq_aliases from the resolution of the query inside it. The two refusals are ResolvedOuterRef’s, one when the queryset’s repr evaluated it and one when a list was made of it.
Subquery
Subquery wraps a query so that it can be written as an expression with a template, "(%(subquery)s)" by default. It is built on BaseExpression and Combinable, so arithmetic on one makes a CombinedExpression (Expressions: resolve_expression and as_sql). Its constructor takes a queryset or a query, clones the query, marks the clone subquery, takes a template out of the keyword arguments where one is given, and keeps the rest as extra for the template to fill; an output_field is passed to BaseExpression as for any expression. The query is its one source expression, Subquery.copy clones it afresh, and Subquery and Subquery are the query’s. Subquery.as_sql has the query write itself through Query.as_sql, strips the parentheses that method added, and puts the result into the template under the name "subquery", with extra and whatever the caller passed as the rest of the template’s values. Subquery asks the query with itself as the wrapper. contains_aggregate is false on the class, so an aggregate inside the subquery does not count as one of the outer query’s, and empty_result_set_value is None: a selected subquery whose query can match nothing is written as "NULL" by the compiler (The SELECT clause: get_select, the select mask and klass_info).
Its output field is the query’s own, through _resolve_output_field, unless one was given. And its resolve_expression removes the wrapper: after the ordinary resolution of its sources, which is Query on the query, a plain Subquery with the default template marks the resolved query contains_subquery and returns that query in its own place, a comment calling the wrapper an unnecessary shim that complicates the inspection of a lookup’s right side. Where an output field was given whose type is not that of the query’s own, the query is returned inside an ExpressionWrapper of that field instead. A subclass, or a Subquery given another template, stays as it is.
type(Subquery(first_hand, output_field=models.CharField()).resolve_expression(outer)).__name__, type(Subquery(first_hand, output_field=models.IntegerField()).resolve_expression(outer)).__name__ -> ('Query', 'ExpressionWrapper')
type(Subquery(first_hand).resolve_expression(outer).output_field).__name__ -> 'CharField'
Subquery(Sailor.objects.filter(ship=OuterRef('pk')).values('name', 'id')[:1]).resolve_expression(outer).output_field -> raises OutputFieldIsNoneError: Cannot resolve expression type, unknown output_field
With a CharField given, the type the query has already, the resolved object is the query; with an IntegerField it is the wrapper. Without one, the query’s output field is the type of its one selected column, "name". The last line selects two columns: the query’s output_field is then None, and reading the wrapper’s output field raises OutputFieldIsNoneError, which is where the resolution stops, since it reads the output field to decide what to return.
The annotation recorded under A query as an expression is the common case: the resolved query stands in Query under "first", and the compiler selects it in parentheses under that alias (Annotations, ordering and the other methods that change the query).
Exists
Exists is a Subquery with the template "EXISTS(%(subquery)s)", a BooleanField as its output field, so that it is conditional and can stand in a filter and in a When, and False as its empty_result_set_value. Its constructor replaces the query it was given with that query’s Query.exists, the query an exists() call runs: it selects a constant under the alias "a", asks for one row, and clears the ordering and, unless the query is both distinct and sliced, the select list, so that values on the inner queryset changes nothing (Aggregates: Aggregate, Count and get_aggregation).
Exists.as_sql catches the EmptyResultSet an inner query raises when it can match nothing and compiles Value(False) in its place, or writes "1=0" on a backend without boolean expressions in a select list. Exists is for such a backend too: where supports_boolean_expr_in_select_clause is off, a selected Exists is wrapped as "CASE WHEN" the subquery "THEN 1 ELSE 0 END". A line that begins with tail answers with the statement from its "FROM" on, without the select list’s parameters.
tail(Ship.objects.filter(Exists(Sailor.objects.filter(ship=OuterRef('pk'), name='Ada')))) -> ('FROM "fleet_ship" WHERE EXISTS(SELECT %s AS "a" FROM "crew_sailor" "U0" WHERE ("U0"."name" = %s AND "U0"."ship_id" = ("fleet_ship"."id")) LIMIT 1) ORDER BY "fleet_ship"."name" ASC', (1, 'Ada'))
As a filter an Exists is a conditional expression, and Query resolves it and wraps it as Exact(Exists(...), True), which Exact.as_sql writes as the expression alone, WHERE EXISTS(...), and with "NOT" in front where the right side is False; ~Exists(...) is a NegatedExpression, which writes its own "NOT" (The where clause: build_filter, add_q and WhereNode, Expressions: resolve_expression and as_sql); a line that begins with where_said answers with the where tree:
where_said(Ship.objects.filter(Exists(Sailor.objects.filter(ship=OuterRef('pk'))))) -> (AND: Exact(Exists(a Query of Sailor), True))
tail(Ship.objects.filter(Exists(Sailor.objects.none()))) -> ('FROM "fleet_ship" WHERE %s ORDER BY "fleet_ship"."name" ASC', (False,))
sql_of(Ship.objects.annotate(x=Exists(Sailor.objects.none())).values('x')) -> ('SELECT %s AS "x" FROM "fleet_ship" ORDER BY "fleet_ship"."name" ASC', (False,))
Exists(Sailor.objects.all()).query.select, Exists(Sailor.objects.all()).query.high_mark, Exists(Sailor.objects.all()).output_field, Exists(Sailor.objects.all()).empty_result_set_value, Subquery(Sailor.objects.all()).empty_result_set_value -> ((), 1, <django.db.models.fields.BooleanField>, False, None)
...
tail(Ship.objects.filter(Exists(Sailor.objects.filter(ship=OuterRef('pk')).values('name')))) -> ('FROM "fleet_ship" WHERE EXISTS(SELECT %s AS "a" FROM "crew_sailor" "U0" WHERE "U0"."ship_id" = ("fleet_ship"."id") LIMIT 1) ORDER BY "fleet_ship"."name" ASC', (1,))
The inner query made by QuerySet.none raised EmptyResultSet when compiled, and the condition became the parameter False; selected as an annotation, the same False stands under the alias. The attributes are the constructor’s doing: no select, a limit of one, the boolean output field, and the two classes’ values for an empty result. The inner queryset with values('name') got the same SELECT %s AS "a" as the one without.
A queryset as the value of a filter
A queryset on the right of "in", "exact" or a comparison is a subquery without a wrapper. Query resolves any value that has resolve_expression, and a queryset has one: QuerySet returns its query resolved against the outer one, with the queryset’s database alias, QuerySet._db, copied onto it. What the lookup then holds on its right is a Query marked subquery. Before the lookup is built, Query refuses a queryset of another model for a relation unless it has chosen its columns (The where clause: build_filter, add_q and WhereNode). In and Exact then make the subquery fit the left side: each compares the subquery’s width with the left side’s and refuses a mismatch with ValueError, and where the queryset has has_select_fields false, has chosen no columns, each clears its select clause and adds "pk"; Exact first refuses a query not limited to one row, which has_limit_one tells (Lookups: Lookup, BuiltinLookup and the standard comparisons, In, Exact). The width is Query: the model’s key columns where the queryset has chosen no columns or its select list is empty, and otherwise the select list’s, a composite key counted by its columns. In.process_rhs adds one refusal at compile time, a subquery whose queryset was bound to another database than the connection’s, with a message that says to evaluate it as a list instead. A relation on the left adds the preparation of Lookups on relations and composite keys: RelatedIn and the tuple lookups.
tail(Ship.objects.filter(tonnage__gt=Subquery(Ship.objects.filter(name='Petrel').values('tonnage')[:1]))) -> ('FROM "fleet_ship" WHERE "fleet_ship"."tonnage" > (SELECT "U0"."tonnage" AS "tonnage" FROM "fleet_ship" "U0" WHERE "U0"."name" = %s ORDER BY "U0"."name" ASC LIMIT 1) ORDER BY "fleet_ship"."name" ASC', ('Petrel',))
...
tail(Ship.objects.filter(tonnage=Subquery(Ship.objects.order_by('-tonnage').values('tonnage')[:1]) - 720)) -> ('FROM "fleet_ship" WHERE "fleet_ship"."tonnage" = ((SELECT "U0"."tonnage" AS "tonnage" FROM "fleet_ship" "U0" ORDER BY 1 DESC LIMIT 1) - %s) ORDER BY "fleet_ship"."name" ASC', (720,))
tail(Ship.objects.filter(pk__in=Sailor.objects.filter(name='Ada').values('ship'))) -> ('FROM "fleet_ship" WHERE "fleet_ship"."id" IN (SELECT "U0"."ship_id" AS "ship" FROM "crew_sailor" "U0" WHERE "U0"."name" = %s) ORDER BY "fleet_ship"."name" ASC', ('Ada',))
tail(Ship.objects.filter(pk__in=Sailor.objects.filter(name='Ada').values_list('ship', flat=True))) -> ('FROM "fleet_ship" WHERE "fleet_ship"."id" IN (SELECT "U0"."ship_id" AS "ship" FROM "crew_sailor" "U0" WHERE "U0"."name" = %s) ORDER BY "fleet_ship"."name" ASC', ('Ada',))
tail(Ship.objects.filter(pk__in=Subquery(Sailor.objects.filter(name='Ada').values('ship')))) -> ('FROM "fleet_ship" WHERE "fleet_ship"."id" IN (SELECT "U0"."ship_id" AS "ship" FROM "crew_sailor" "U0" WHERE "U0"."name" = %s) ORDER BY "fleet_ship"."name" ASC', ('Ada',))
The Subquery on the right of "tonnage__gt" was reduced by the resolution to its query, and the one with - 720 after it is in arithmetic, a CombinedExpression whose left side is the subquery. The three "pk__in" lines are one statement written three ways: a values queryset, a flat values_list queryset and a Subquery of the first all reach In as the same resolved query, and the column "ship" each had chosen is kept under its alias.
The ordering of a subquery
A subquery’s ordering is the inner query’s own, and what becomes of it depends on who holds the subquery. In clears it with clear_default=True, so that the model’s default ordering does not come back either (ORDER BY and DISTINCT: get_order_by, find_ordering_name and get_distinct). Exact leaves it, and a query limited to one row is written with it. A Subquery leaves it too. Then Query.as_sql asks the backend: where ignores_unnecessary_order_by_in_subqueries is off, as it is for Oracle, it clears the ordering without force, which clear_ordering declines to do for a query that is sliced, has distinct fields or asks for a lock, so a sliced subquery keeps its ordering there too.
Where distinct() without fields and an ordering by a column the subquery does not select have added that column to its select list, SQLCompiler wraps the statement in another select that reads the wanted columns alone, under the alias "subquery" (The compiler: as_sql, execute_sql and results_iter).
The ordering of a query inside another: cleared by In, kept under a slice, and the extra columns of distinct with order_by wrapped away
tail(Sailor.objects.filter(ship__in=Ship.objects.filter(tonnage__gt=1000).order_by('name'))) -> ('FROM "crew_sailor" WHERE "crew_sailor"."ship_id" IN (SELECT "U0"."id" FROM "fleet_ship" "U0" WHERE "U0"."tonnage" > %s)', (1000,))
tail(Sailor.objects.filter(ship=Ship.objects.order_by('name')[:1])) -> ('FROM "crew_sailor" WHERE "crew_sailor"."ship_id" = (SELECT "fleet_ship"."id" FROM "fleet_ship" ORDER BY "fleet_ship"."name" ASC LIMIT 1)', ())
sql_of(Ship.objects.annotate(x=Subquery(Sailor.objects.filter(ship=OuterRef('pk')).order_by('-name').values('name')[:1])).values('x')) -> ('SELECT (SELECT "U0"."name" AS "name" FROM "crew_sailor" "U0" WHERE "U0"."ship_id" = ("fleet_ship"."id") ORDER BY 1 DESC LIMIT 1) AS "x" FROM "fleet_ship" ORDER BY "fleet_ship"."name" ASC', ())
connection.features.ignores_unnecessary_order_by_in_subqueries -> True
sql_of(Ship.objects.annotate(x=Subquery(Sailor.objects.filter(ship=OuterRef('pk')).distinct().order_by('signed_on').values('name')[:1])).values('x')) -> ('SELECT (SELECT "subquery"."name" FROM (SELECT DISTINCT "U0"."name" AS "name", "U0"."signed_on" FROM "crew_sailor" "U0" WHERE "U0"."ship_id" = ("fleet_ship"."id") ORDER BY "U0"."signed_on" ASC LIMIT 1) subquery) AS "x" FROM "fleet_ship" ORDER BY "fleet_ship"."name" ASC', ())
The ordered queryset under "in" lost its ordering; the sliced one under "exact" kept it, and stands under its table’s own name, having had no alias to rename; the Subquery kept its descending order, written by position. The distinct() line is the wrap: the inner statement selects "name" and "signed_on", and the statement round it selects "subquery"."name" alone.
Django’s own subqueries
Django makes subqueries of its own by the same means. Query builds one for an exclude across a relation that can reach several rows, filtering the inner query with a ResolvedOuterRef or by an external alias, and makes an Exists of it for the outer where tree, where the negation of the exclude puts "NOT" in front (The where clause: build_filter, add_q and WhereNode). Query marks a clone of the query subquery and makes it the inner query of an AggregateQuery where the query is sliced, distinct, grouped or combined, among other conditions, and where an aggregate refers to an annotation whose contains_subquery is true (Aggregates: Aggregate, Count and get_aggregation). The compiler’s get_qualify_sql does the same to write a filter on a window (Window expressions: Window, WindowFrame and the QUALIFY rewrite); a compound statement stands inside another with parentheses chosen by the flag (Two queries into one: combine and get_combinator_sql); and an update or a delete whose filter joins another table moves the joins into a subquery on the primary key (INSERT, UPDATE and DELETE: the insert, update and delete compilers).