Introduction
This article is the third in my series where I, raised on the Django ORM, relearn raw SQL: Queries. The series has four parts.
- Model Definition: what tables a models.py definition becomes
- Model Changes: what ALTER TABLE runs behind makemigrations and migrate
- Queries (this article): what queries run behind filter and update
- Performance Tuning: the N+1 problem, select_related / prefetch_related, and the internals of aggregation
The two previous articles dealt with DDL like CREATE TABLE and ALTER TABLE — creating and changing the container that is the table. Now that the container is ready, from here we finally get into the SQL that manipulates the data inside it. When you write User.objects.get(id=2), what SELECT is issued underneath? What INSERT or UPDATE do create and update become? This is the installment where I relentlessly check the ORM code I write most often against its corresponding SQL.
Such data-manipulating SQL is called DML (Data Manipulation Language), distinct from DDL. At the start of the Model Definition article I wrote that “I know words like SELECT, WHERE, and JOIN but can’t accurately explain them”; now I finally step into that. Only UPDATE was slightly foreshadowed at the end of the Model Changes article; this time I’ll cover SELECT as the star, along with INSERT, UPDATE, and DELETE.
I use the same sample models as before (User, Profile, Post, Tag, Comment) as-is. For the full definitions and the ER diagram, see the Model Definition article. One difference is that this time I need data inside the tables. DDL could be read against empty tables, but queries aren’t interesting unless results come back. Locally, I’ve verified this with three users and six articles, with tags and comments linked.
Since this is based on my own research, it may contain errors. If you notice anything, I’d greatly appreciate a heads-up.
How to check the SQL the ORM issues
The work is done inside the Django shell started with python manage.py shell. Normally, just looking at a QuerySet’s query attribute was enough.
>>> qs = Post.objects.filter(status="draft")
>>> print(qs.query)
SELECT "blog_post"."id", "blog_post"."author_id", "blog_post"."title", "blog_post"."subtitle", "blog_post"."slug", "blog_post"."content", "blog_post"."status", "blog_post"."view_count", "blog_post"."published_at", "blog_post"."created_at", "blog_post"."updated_at" FROM "blog_post" WHERE "blog_post"."status" = draftYou can see that the condition I wrote as filter(status="draft") becomes the WHERE clause as-is. It’s handy and convenient enough, so I’ll use this method as the basis in this article.
When you want to see “the exact SQL that was executed” more strictly, you can also check with connection.queries1.
Also, since all column names are listed after SELECT and it gets long, from here I’ll abbreviate parts unrelated to the topic as SELECT ... FROM. This “listing all columns” itself has a proper meaning, but I’ll cover that in the values chapter.
Nailing down the basic form of SELECT first
Before moving on to individual methods, let me look at just one skeleton of the SELECT statement. Every SELECT in this article is, in the end, a variation of this form.
SELECT columns, ... -- which columns
FROM table_name -- from which table
WHERE condition -- narrowed to which rows
ORDER BY column -- ordered how
LIMIT count; -- how many to returnOnly SELECT and FROM are required; the rest are parts you add when needed. And this skeleton maps neatly to the QuerySet methods I usually write.
| QuerySet operation | SQL clause |
|---|---|
| filter / exclude | WHERE |
| order_by | ORDER BY |
| slice ([:10]) | LIMIT / OFFSET |
| values / values_list | SELECT column selection |
| double-underscore following a relation, like author__name | JOIN (extension of FROM) |
In other words, a QuerySet method chain is the act of filling in the parts of this skeleton one by one. This article checks this correspondence table row by row with actual SQL.
First, create data: INSERT
Read queries aren’t interesting without data, so I’ll start from the writing side. I’ll start with create, the most commonly used.
>>> User.objects.create(name="dave", email="dave@example.com")INSERT INTO "blog_user" ("name", "email") VALUES ('dave', 'dave@example.com') RETURNING "blog_user"."id"Inserting a row is the job of the INSERT statement. The basic form pairs a list of columns with a list of values2.
INSERT INTO table_name (columns, ...) VALUES (values, ...);Comparing the output with the basic form, I notice two things. First, there’s no id in the column list. As I saw in the Model Definition article, id is a GENERATED BY DEFAULT AS IDENTITY column, so if you don’t specify it, the DB assigns it.
The other is RETURNING at the end. It’s a part not in the basic form; this is PostgreSQL’s extension syntax to have specified columns of the inserted row returned2. It’s there to receive the DB-assigned id in the same single round-trip as the INSERT. The reason user.id is immediately usable in create’s return value is thanks to this RETURNING.
save() tries UPDATE first
Since the real form of create is “create an instance and call save(),” let me look at save() too. What I’m curious about is how save() decides whether to issue an INSERT or an UPDATE. Trying it, the dividing line was pk. For an instance with an empty pk, it’s exactly the same INSERT as above. What’s interesting is when pk has a value: if you specify a nonexistent id=999 and save(), two SQL statements come out.
UPDATE "blog_user" SET "name" = 'eve', "email" = 'eve@example.com' WHERE "blog_user"."id" = 999
INSERT INTO "blog_user" ("id", "name", "email") VALUES (999, 'eve', 'eve@example.com') RETURNING "blog_user"."id"Doing the same with an existing id=1 finishes with just the first UPDATE. In other words, save() doesn’t confirm whether the row exists with a SELECT. It’s a two-stage setup: try an UPDATE first, and if the number of updated rows was 0, switch to INSERT. It’s a design that saves the one query that would exist only to check existence.
bulk_create is a single INSERT
When creating multiple records at once, looping create issues three INSERTs for three records. bulk_create bundles them into one.
>>> Tag.objects.bulk_create([Tag(name="web"), Tag(name="db"), Tag(name="dev")])INSERT INTO "blog_tag" ("name") SELECT * FROM UNNEST(('{web,db,dev}')::varchar[]) RETURNING "blog_tag"."id"It did become one, but the form differed from my prediction. Since VALUES can list rows like VALUES ('web'), ('db'), ('dev'), I expected that. What actually appeared was the first-seen function UNNEST.
Looking it up, first, INSERT has syntax INSERT INTO ... SELECT ... that flows a SELECT result in place of VALUES2. And UNNEST is a PostgreSQL function that expands an array into rows. So this SQL expands the varchar array '{web,db,dev}' into three rows and flows the result into the INSERT. This is a PostgreSQL-oriented optimization introduced in Django 5.2; previously it was the VALUES-listing form as predicted3. Since you can pass the whole array as a single value, the shape of the SQL statement doesn’t change as the count grows. That seems to be the benefit.
get_or_create isn’t a single SQL statement
What about get_or_create, which does “get if it exists, create if not”? Calling it with a name not already present produced four statements.
>>> Tag.objects.get_or_create(name="new-tag")SELECT "blog_tag"."id", "blog_tag"."name" FROM "blog_tag" WHERE "blog_tag"."name" = 'new-tag' LIMIT 21
BEGIN
INSERT INTO "blog_tag" ("name") VALUES ('new-tag') RETURNING "blog_tag"."id"
COMMITJust as the name says, “SELECT, and INSERT if not found,” and such composite methods aren’t a single special SQL but a combination of multiple queries. BEGIN and COMMIT are the transaction syntax that wrapped the entire migration at the start of the Model Definition article. Django wraps this INSERT in a transaction so that even if another process concurrently creates the same name and causes a unique-constraint violation, it can handle it without leaving a half-finished state. By the way, when called inside an already-running transaction, this part changes to SAVEPOINT/RELEASE SAVEPOINT (an intermediate save point within a transaction). Trying its sibling update_or_create, this one had FOR UPDATE, a row-lock specification, added at the end of the SELECT. I won’t chase transaction mastery or row locks in this series; I’ll just note that such guards are in place and move on.
What’s more interesting is the first SELECT. Even though I didn’t ask for it, LIMIT 21, an odd number, is attached. This is actually because get_or_create uses get internally. On to the star of the next chapter.
Getting one and getting all
get and LIMIT 21
>>> User.objects.get(id=2)SELECT "blog_user"."id", "blog_user"."name", "blog_user"."email" FROM "blog_user" WHERE "blog_user"."id" = 2 LIMIT 21get(id=2) became the WHERE clause "id" = 2. As a SELECT statement it’s the same form as filter’s result; get doesn’t produce special SQL. But again, LIMIT 21 is attached. For a single-get method, it’s not no LIMIT (all rows), nor LIMIT 1, but 21.
Looking it up, Django’s source code had exactly the constant MAX_GET_RESULTS = 21. It’s a number back-calculated from get’s spec. get raises DoesNotExist for 0 rows and MultipleObjectsReturned for 2 or more. Just detecting “2 or more” only needs fetching 2 rows, but Django reports the number of hits in the error message. On the other hand, reading all rows to count exactly would be a disaster if you accidentally get a huge table. The compromise was cutting off at 21 rows. Let me actually try varying the count.
>>> Post.objects.get(author_id=1) # a condition that hits 3 rows
MultipleObjectsReturned: get() returned more than one Post -- it returned 3!
>>> Tag.objects.get(name__startswith="t") # a condition that hits 25 rows
MultipleObjectsReturned: get() returned more than one Tag -- it returned more than 20!Up to 20 rows it counts and reports exactly, and once the 21st is seen it just reports “more than 20.” LIMIT 21 is the number that balances the kindness of the error message with safety.
By the way, even if you write get(pk=2), the issued SQL is character-for-character identical to get(id=2). pk is an alias for the primary key (id in this model), resolved to id before the SQL is assembled.
first supplements the order
What about first, another single-get?
>>> User.objects.first()SELECT "blog_user"."id", "blog_user"."name", "blog_user"."email" FROM "blog_user" ORDER BY "blog_user"."id" ASC LIMIT 1LIMIT 1 makes sense, but there’s an ORDER BY id ASC I didn’t ask for. This is the flip side of “a table has no concept of row order” that I learned in the Meta.ordering section of the Model Definition article. You can’t define “the first row” for something with no order. So when order is unspecified, Django supplements pk-ascending order before taking the first. If you specify the order yourself, that one is used, of course.
>>> Post.objects.order_by("-published_at").first()SELECT ... FROM "blog_post" ORDER BY "blog_post"."published_at" DESC LIMIT 1Lining them up, the difference in character between get and first shows up directly in the SQL. get is an assertion “there should be one row matching the condition,” with LIMIT 21 to error if not. first is “just give me the first one after ordering (None is fine if none),” with ORDER BY + LIMIT 1 for that. From SQL, you can see that which one to use is chosen by whether you want multiple hits to be an error.
all
Let me look at all(), which gets all rows.
>>> list(User.objects.all())SELECT "blog_user"."id", "blog_user"."name", "blog_user"."email" FROM "blog_user"The shortest SELECT in this article, with neither WHERE nor LIMIT. Since I added no narrowing parts, only the required part of the skeleton remains. By the way, SQL has SELECT *, which means all columns, but Django doesn’t use *; it lists the fields defined on the model every time. However many columns the table has, it receives only the fields the model knows about, in the defined order.
Assembling the WHERE clause: filter and exclude
From here it’s the WHERE clause. Since I confirmed in the how-to-check chapter that filter becomes a simple WHERE, I’ll start by passing two conditions. As declared, from here I’ll abbreviate the SELECT column listing as ....
>>> list(Post.objects.filter(status="published", view_count__gte=100))
[<Post: Intro to Django's ORM>, <Post: Relearning SQL>, <Post: PostgreSQL Indexes>]SELECT ... FROM "blog_post" WHERE ("blog_post"."status" = 'published' AND "blog_post"."view_count" >= 100)The conditions listed in filter were joined with AND into a single WHERE clause. WHERE conditions can use not just = but comparison operators like >= too. The conversion rule that turns this view_count__gte=100 into >= 100 will be covered together in the next section.
Next, exclude. Let me get “anything other than draft.”
>>> list(Post.objects.exclude(status="draft"))SELECT ... FROM "blog_post" WHERE NOT ("blog_post"."status" = 'draft')exclude wraps the contents of the WHERE clause in NOT. SQL also has the <> operator, which means “not equal.” The reason it wraps in NOT instead of using that seems to be that what’s passed to exclude isn’t necessarily a single condition. Passing two conditions gives this.
SELECT ... FROM "blog_post" WHERE NOT ("blog_post"."status" = 'draft' AND "blog_post"."view_count" <= 10)NOT (A AND B), meaning “exclude rows that are both A and B,” a collective negation. If you grasp it as “wrap the whole cluster of conditions passed to exclude in a single NOT, not negating each condition individually,” you won’t misread it. Combined with filter it’s the same structure. The WHERE clause of filter(author_id=1).exclude(status="draft") becomes ("author_id" = 1 AND NOT ("status" = 'draft')); the filter side stays as-is, and only the exclude side is wrapped in NOT.
Last, let me see what SQL you get if you write filter split into a chain of two.
>>> list(Post.objects.filter(status="published").filter(view_count__gte=100))
[<Post: Intro to Django's ORM>, <Post: Relearning SQL>, <Post: PostgreSQL Indexes>]SELECT ... FROM "blog_post" WHERE ("blog_post"."status" = 'published' AND "blog_post"."view_count" >= 100)The issued SQL is a single statement, with a WHERE clause character-for-character identical to passing two conditions together at the start. It’s not a two-stage process of applying the second filter to the first filter’s result; you can see it’s ultimately assembled as a single WHERE clause.
Field lookups and their SQL correspondence
The way of connecting a double underscore (two underscores) after a field name, like the previous section’s view_count__gte=100, is called a field lookup. Let me check what these become in SQL, going through the representative ones.
First, contains for partial match.
>>> list(Post.objects.filter(title__contains="SQL"))
[<Post: Relearning SQL>, <Post: PostgreSQL Indexes>]SELECT ... FROM "blog_post" WHERE "blog_post"."title"::text LIKE '%SQL%'What appeared is the LIKE operator. LIKE matches strings by pattern, and % represents “a string of zero or more characters.” '%SQL%' means “contains SQL somewhere, with anything before or after,” and this was the real form of partial match. The ::text in the middle is PostgreSQL’s type-cast syntax, aligning the varchar column to the text type before comparing. It doesn’t affect the result, so it’s fine to skim past as boilerplate.
The next was this article’s discovery: icontains, which ignores case. Since PostgreSQL has the ILIKE operator that ignores case, I fully expected that to come out, but it was different.
>>> list(Post.objects.filter(title__icontains="sql"))
[<Post: Relearning SQL>, <Post: PostgreSQL Indexes>]SELECT ... FROM "blog_post" WHERE UPPER("blog_post"."title"::text) LIKE UPPER('%sql%')It aligns both sides with UPPER (a function that uppercases) and then compares with plain LIKE. Uppercase everything and case differences vanish — a naive, reliable approach. Whereas ILIKE is a PostgreSQL dialect, the combination of UPPER and LIKE works on any database. This seems to be a way of writing that keeps Django’s behavior consistent across multiple DB backends. Here too, the Django design philosophy I’ve seen repeatedly since the Model Definition article showed its face: “prefer a way common to all backends over mapping to SQL dialects.”
Let me look at IN too.
>>> list(Post.objects.filter(status__in=["draft", "archived"]))
[<Post: A draft memo>, <Post: An old archived article>]SELECT ... FROM "blog_post" WHERE "blog_post"."status" IN ('draft', 'archived')__in was the IN operator as-is. It means “matches any one of the listed values,” equivalent to lining up = with OR.
Since I’ve got the gist by now, I’ll summarize the rest in a table. All were run and verified locally.
| Lookup | Form in the WHERE clause |
|---|---|
__gt / __gte / __lt / __lte | > / >= / < / <= |
__in=[...] | IN (...) |
__contains | LIKE '%x%' |
__icontains | UPPER(...) LIKE UPPER('%x%') |
__startswith | LIKE 'x%' |
__isnull=True | IS NULL |
__year=2026 | BETWEEN '2026-01-01...' AND '2026-12-31...' |
Two supplements. __isnull becomes the dedicated IS NULL because = can’t be used for NULL tests — exactly the story from the Model Changes article. But since the current sample models made published_at NOT NULL in the Model Changes article, there are no longer any nullable columns, so I’ll defer the demo to the next chapter (it has its turn when combined with relations). I thought __year would extract the year with a date function like EXTRACT, but it expanded into a BETWEEN (an inclusive range operator) “from the start of the year to the end of the year.”
And here’s this section’s summary. The name “field lookup” is grandiose, but once lined up with SQL, what’s after the double underscore is just selecting the WHERE-clause operator. =, or >=, or IN, or LIKE. As long as the correspondence between lookup names and operators is in your head, you can read the WHERE clause from a filter line. But the double underscore has one more face. I’ll cover it in the next chapter.
Sorting and pagination: order_by and slicing
Next is sorting. Let me get “the top 3 by view count.”
>>> list(Post.objects.order_by("-view_count")[:3])
[<Post: Relearning SQL>, <Post: PostgreSQL Indexes>, <Post: Intro to Django's ORM>]SELECT ... FROM "blog_post" ORDER BY "blog_post"."view_count" DESC LIMIT 3order_by("-view_count") became an ORDER BY clause. The minus at the head of the field name corresponds to DESC (descending), and without a minus it’s ASC (ascending). It’s the same part as the ORDER BY "id" ASC that came up in the first section. The slice [:3] is also the LIMIT I saw in first. What I want to nail down here is the order of application: what LIMIT cuts out is the first 3 rows after sorting is finished. It takes effect in the order: narrow with WHERE, sort with ORDER BY, cut with LIMIT.
So what about a slice with a start point?
>>> list(Post.objects.order_by("id")[3:6])
[<Post: New Features in Python 3.14>, <Post: PostgreSQL Indexes>, <Post: An old archived article>]SELECT ... FROM "blog_post" ORDER BY "blog_post"."id" ASC LIMIT 3 OFFSET 3OFFSET was added. OFFSET is a specification to “skip n rows from the head,” and [3:6] is “skip 3, take 3,” meaning LIMIT 3 OFFSET 3. Python’s slice sense was translated directly into SQL.
And this is also the trick behind pagination. Page 3 with 10 items per page is [20:30], or in SQL LIMIT 10 OFFSET 20. Django’s built-in Paginator just converts page numbers into this OFFSET. The backend of the page-flipping UI you see in every service actually runs on just these two parts. One caveat: OFFSET, despite saying “skip,” has the database read the skipped rows once and then discard them. OFFSET 10000 reads and discards 10000 rows, so it has the property that deeper pages get slower. It’s a point to remember when designing something where pages can get deep.
Finally, let me collect the homework from the Model Definition article. Back then I learned that the Meta option ordering “appears nowhere in the DDL, because a table has no concept of row order, and order is something you specify each time with SELECT’s ORDER BY clause.” The meaning of the latter half finally becomes tangible in this chapter. If I had written ordering = ["-created_at"] in Post’s Meta, ORDER BY "created_at" DESC would have been implicitly inserted into every SELECT in this article that didn’t have order_by. Since it’s a setting that changes only the query without changing the table definition, the fact that adding or changing ordering doesn’t require a schema change via migration follows from the same logic. Order isn’t something the table holds; it’s something the query builds every time. This sense feels like it’ll keep paying off in reading and writing SQL.
A filter that crosses a relation becomes a JOIN
The WHERE clauses so far all completed within a single table. But in real apps you want to narrow by a column in another table, like “filter articles by the author’s name.” In Django you just follow the relation with a double underscore.
>>> list(Post.objects.filter(author__name="alice"))
[<Post: Intro to Django's ORM>, <Post: Relearning SQL>, <Post: A draft memo>]SELECT ... FROM "blog_post" INNER JOIN "blog_user" ON ("blog_post"."author_id" = "blog_user"."id") WHERE "blog_user"."name" = 'alice'A JOIN appeared. The syntax is FROM table_A INNER JOIN table_B ON join_condition, an operation that joins two tables into one large table. The key is the ON clause, which specifies “which rows correspond to which.” This time it’s "blog_post"."author_id" = "blog_user"."id", joining rows where the article’s author_id and the user’s id match. The foreign key column I learned in the Model Definition article “just holds the other side’s id number” is used here as the path crossing the two tables. Against the large table formed by joining, the WHERE clause narrows by "blog_user"."name", a blog_user-side column, and the SELECT returns only blog_post-side columns.
And here I need to update the understanding from the lookup section. The double underscore in author__name isn’t selecting an operator. The first double underscore represents the route following the relation (author, i.e., a JOIN to blog_user), and the name beyond it is the column used in WHERE. The double underscore represents not only the WHERE-clause operator but also the JOIN route. This was the double underscore’s other face.
Searching for “doesn’t have” with LEFT OUTER JOIN
The homework from the lookup section, the __isnull demo, can also be done here. Let me search for users who haven’t created a profile.
>>> list(User.objects.filter(profile__isnull=True))
[<User: carol>]SELECT ... FROM "blog_user" LEFT OUTER JOIN "blog_profile" ON ("blog_user"."id" = "blog_profile"."user_id") WHERE "blog_profile"."id" IS NULLThe JOIN type changed. The earlier INNER JOIN keeps only “rows where the other side was found.” With that behavior, carol, who has no profile, would vanish at the JOIN and there’d be no way to find her. LEFT OUTER JOIN keeps all rows on the left side (the FROM-side blog_user) and joins rows with no match filled with NULL. carol’s row survives with all the profile-side columns NULL, and this is picked up with WHERE "blog_profile"."id" IS NULL. It’s a classic SQL technique for “searching for something that doesn’t exist,” and Django chooses even the JOIN type according to the condition.
Reverse relations change the SQL by how you write them
Relations can be followed in reverse too. Since I defined related_name=“posts”, alice’s articles can be gotten with alice.posts.all(), but this SQL has no JOIN.
SELECT ... FROM "blog_post" WHERE "blog_post"."author_id" = 1Which makes sense: since we know the alice instance at hand is id=1, we can just narrow author_id directly in WHERE. Joining tables is needed only when “narrowing by a column value on the other side.” That form — following a reverse relation in filter — becomes a JOIN this time. Searching for “users who have a published article,” something interesting happened.
>>> list(User.objects.filter(posts__status="published"))
[<User: alice>, <User: alice>, <User: bob>, <User: bob>]SELECT ... FROM "blog_user" INNER JOIN "blog_post" ON ("blog_user"."id" = "blog_post"."author_id") WHERE "blog_post"."status" = 'published'alice and bob each came back twice. It looks like a bug, but it isn’t — it’s the JOIN mechanism itself. What a JOIN produces is a row correspondence, so alice, who has two published articles, becomes one row per article — two rows total. Since the SELECT returns only blog_user-side columns, you see two rows with identical contents. To remove duplicates, add DISTINCT (a specification to remove duplicate rows) to the SELECT. In a QuerySet it’s distinct().
>>> list(User.objects.filter(posts__status="published").distinct())
[<User: alice>, <User: bob>]SELECT DISTINCT "blog_user"."id", "blog_user"."name", "blog_user"."email" FROM "blog_user" INNER JOIN "blog_post" ON ("blog_user"."id" = "blog_post"."author_id") WHERE "blog_post"."status" = 'published'“The result of a filter crossing a relation somehow duplicates” is a trap you actually tend to step on when writing Django. From the SQL side, rows increasing due to a JOIN is normal behavior, and choosing whether to return them increased or collapsed is our job.
ManyToMany is two JOINs
Last is ManyToMany. Let me search for “articles tagged sql.”
>>> list(Post.objects.filter(tags__name="sql"))SELECT ... FROM "blog_post" INNER JOIN "blog_post_tags" ON ("blog_post"."id" = "blog_post_tags"."post_id") INNER JOIN "blog_tag" ON ("blog_post_tags"."tag_id" = "blog_tag"."id") WHERE "blog_tag"."name" = 'sql'Two INNER JOINs lined up. In the Model Definition article I learned that the real form of a ManyToManyField is the junction table blog_post_tags, and that structure appears in the query as-is. The ORM’s tags__name looks like a single one-hop double underscore, but in SQL it crosses article → junction table → tag with two JOINs. The junction table appeared in the Model Definition article as “something that gets created,” but in queries it works as this waypoint every time.
Digging into JOIN itself is the main battlefield of the next Performance Tuning article. How joins are processed, how much they cost, and how they relate to select_related, which fetches related objects together, I’ll leave to that article; here I’ll just nail down “the number of JOINs increases by the number of relations followed with double underscores” and move on.
Narrowing the columns fetched: values and values_list
In this chapter, I narrow the SELECT-clause listing I’ve been abbreviating as ... by my own will for the first time. The reason the SELECTs so far listed all fields is that building a Post instance needs the values of all fields. But there are plenty of situations where you use only id and title to render an article list. That’s what values is for.
>>> list(Post.objects.values("id", "title")[:3])
[{'id': 1, 'title': "Intro to Django's ORM"}, {'id': 2, 'title': 'Relearning SQL'}, {'id': 3, 'title': 'A draft memo'}]SELECT "blog_post"."id" AS "id", "blog_post"."title" AS "title" FROM "blog_post" LIMIT 3The SELECT clause became only the two specified columns, and the return value changed from model instances to a list of dicts. The AS in the middle is an SQL keyword that gives a column an alias, and here it just re-applies the same name, so it’s fine to skim past.
Let me look at its sibling values_list too.
>>> list(Post.objects.values_list("id", "title")[:3])
[(1, "Intro to Django's ORM"), (2, 'Relearning SQL'), (3, 'A draft memo')]SELECT "blog_post"."id" AS "id", "blog_post"."title" AS "title" FROM "blog_post" LIMIT 3The return value changed from dicts to tuples, but the SQL is character-for-character identical to values. In other words, the difference between values and values_list is only how the same result delivered from the database is wrapped on the Python side. Even values_list("title", flat=True), which takes only one column and peels off the tuple nesting, has straightforward single-column SELECT for its SQL.
The most obvious effect is transfer volume. A TextField like content tends to get large, and carrying every article’s body every time you render a list is very wasteful. Narrowing the SELECT clause eliminates that transfer. And this operation of “specifying the SELECT clause yourself” shows its true worth in the next Performance Tuning article, combined with aggregation (annotate) alongside GROUP BY.
Update and delete
Now that reads are covered, last is the writing side. I’ll check UPDATE and DELETE, the partners of save() I saw in the INSERT chapter.
update
A QuerySet has an update method that lets you rewrite the rows narrowed by filter all at once.
>>> Post.objects.filter(status="draft").update(status="published")
1UPDATE "blog_post" SET "status" = 'published' WHERE "blog_post"."status" = 'draft'The UPDATE statement is exactly the form foreshadowed at the end of the Model Changes article: specify the value with SET and the target rows with WHERE. Back then it appeared as a tool for filling NULLs within a migration, but the form issued daily from the ORM was exactly the same syntax. What I want to note is the WHERE clause: the parts filter assembles are the same ones as in a SELECT, plugged directly into the UPDATE. The return value 1 is the number of rows the UPDATE actually rewrote.
One more thing: looking closely at the SET clause, there’s no updated_at. In the Model Definition article I learned “auto_now timestamps are filled by Django every save(),” but update doesn’t go through save(), so auto_now doesn’t fire. The updated_at of rows rewritten with update stays old. It’s a trap you step on if you don’t know it, so seeing it in the SQL was a gain.
save() is SELECT + all-column UPDATE
So what about the usual way — get one record, rewrite a field, and save()?
>>> post = Post.objects.get(id=1)
>>> post.title = "Retitled"
>>> post.save()SELECT ... FROM "blog_post" WHERE "blog_post"."id" = 1 LIMIT 21
UPDATE "blog_post" SET "author_id" = 1, "title" = 'Retitled', "subtitle" = '', "slug" = 'django-orm-intro', "content" = 'This is the body of Intro to Django''s ORM.', "status" = 'published', "view_count" = 120, "published_at" = '2026-06-07 02:27:37.753464+00:00'::timestamptz, "created_at" = '2026-07-07 02:27:37.762992+00:00'::timestamptz, "updated_at" = '2026-07-07 02:28:21.252225+00:00'::timestamptz WHERE "blog_post"."id" = 1Two queries (the get SELECT and the UPDATE), and the UPDATE’s SET clause lists all columns. Even though I changed only title. save() doesn’t track which fields were changed, so it has no choice but to write back all the values the instance at hand holds. This time updated_at is also on the SET clause, and auto_now is at work (this is exactly the “try UPDATE first” save() I saw in the INSERT chapter, so it’s only natural).
You can narrow the columns to write back with update_fields.
>>> post.save(update_fields=["title"])UPDATE "blog_post" SET "title" = 'Retitled again' WHERE "blog_post"."id" = 1It really became an UPDATE of only title. But interestingly, in this form updated_at isn’t included either. auto_now only fires for “fields written by save.” If you narrow with update_fields, you need to include it yourself, like update_fields=["title", "updated_at"]. Actually trying it, updated_at appeared in the SET clause only when I included it.
delete goes after related rows too
Deletion. First let me delete just one record.
>>> Comment.objects.get(id=1).delete()DELETE FROM "blog_comment" WHERE "blog_comment"."id" IN (1)The DELETE statement’s first appearance. The syntax is DELETE FROM table_name WHERE condition, and since it deletes whole rows, there’s nothing like a SET clause4. The reason WHERE is IN (1) rather than = 1 seems to be that it’s unified into a form for deleting multiple records at once.
What’s interesting is when you delete the referenced side. Deleting a Post that has comments and tags linked ran three DELETEs.
>>> Post.objects.get(id=1).delete()DELETE FROM "blog_post_tags" WHERE "blog_post_tags"."post_id" IN (1)
DELETE FROM "blog_comment" WHERE "blog_comment"."post_id" IN (1)
DELETE FROM "blog_post" WHERE "blog_post"."id" IN (1)In the Model Definition article I learned that on_delete=CASCADE “doesn’t write ON DELETE CASCADE in the DDL; Django emulates it on the Python side.” This is the scene of its execution. It deletes the tag links and comments first, then the body last. Deleting children first is because deleting the parent first would trip the foreign key constraint (a referenced row can’t be deleted), so the order has meaning too. When deleting in bulk with QuerySet.delete(), one SELECT is inserted before this sequence of DELETEs. This is so Django can grasp what will be deleted (including the related objects swept in), a continuation of the “go delete it yourself to fire deletion signals” design I saw in the Model Definition article.
bulk_update and the CASE expression
Last, bulk_update, the counterpart to the INSERT chapter’s bulk_create. When you want to update several articles’ view_count to different values, looping save() runs one UPDATE per record — five for five records. bulk_update bundles them into one, but come to think of it, it’s puzzling. An UPDATE’s SET clause is a uniform specification of “set this column to this value,” with no mechanism to write different values per row. How does it make one statement?
>>> posts = list(Post.objects.filter(status="published"))
>>> for p in posts:
... p.view_count += 1
>>> Post.objects.bulk_update(posts, ["view_count"])UPDATE "blog_post" SET "view_count" = (CASE WHEN ("blog_post"."id" = 2) THEN 341 WHEN ("blog_post"."id" = 4) THEN 86 WHEN ("blog_post"."id" = 5) THEN 211 WHEN ("blog_post"."id" = 3) THEN 1 WHEN ("blog_post"."id" = 1) THEN 121 ELSE NULL END)::integer WHERE "blog_post"."id" IN (2, 4, 5, 3, 1)The answer was the CASE expression. CASE is SQL’s conditional branch that returns a value in the form CASE WHEN condition THEN value ... ELSE value END. By passing a branching expression like “if id is 2 then 341, if 4 then 86…” to the SET clause, it writes different values per row in a single UPDATE. Since the WHERE clause narrows targets with IN, the ELSE NULL branch is never actually used. Counting by the number of SQL statements: five for looped save() versus one for bulk_update. This difference connects directly to the next installment’s N+1 story.
Wrap-up
I’ve relentlessly checked the ORM code I write daily against its corresponding SQL. Let me collect this time’s correspondences into one table.
| ORM side | SQL side |
|---|---|
| create | INSERT INTO … VALUES … RETURNING |
| bulk_create | INSERT INTO … SELECT * FROM UNNEST(…) |
| get | SELECT … WHERE … LIMIT 21 |
| first | SELECT … ORDER BY primary key LIMIT 1 |
| filter / exclude | WHERE / WHERE NOT (…) |
| lookups (__gte, __in, __contains, etc.) | WHERE-clause operators (>=, IN, LIKE, etc.) |
| double underscore following a relation | JOIN |
| order_by | ORDER BY |
| slice ([20:30]) | LIMIT 10 OFFSET 20 |
| values / values_list | SELECT-clause column selection |
| update | UPDATE … SET … WHERE |
| delete | DELETE FROM … WHERE (related rows separately) |
| bulk_update | UPDATE … SET CASE WHEN … |
The conclusion was just as the map I drew in the “basic form of SELECT” chapter. A QuerySet method chain is the act of assembling SQL clauses. Even if you write filter split apart, the final WHERE clause is composed into one, and I learned it’s important to understand by the generated SQL rather than by differences in how you write it.
On top of that, I think there are two takeaways this time.
The first is realizing that the ORM isn’t a naive translator. get’s LIMIT 21, bulk_create’s UNNEST, get_or_create’s SAVEPOINT, save() that tries UPDATE first. All are efforts toward the safe side and the efficient side that you couldn’t imagine from models.py. Once you can read SQL, you start to see not just the syntax but even the design intent behind it.
The second is that SQL-side properties don’t disappear even when using the ORM. JOINing increases rows and duplicates results. OFFSET reads the skipped rows too. update doesn’t go through save(), so auto_now doesn’t fire. Phenomena and traps that seem mysterious when looking only at the ORM layer are all normal behavior once you peel back one layer to the SQL. With the “ORM method → SQL” correspondence in your head, you should be able to prevent this kind of problem in advance.
Next is finally the last installment, Performance Tuning. Now that I can create tables, change them, and read and write queries, I’ll finish with the speed story. The N+1 problem, famous as the price of the ORM’s convenience, its countermeasures select_related and prefetch_related, and the internals of aggregation — I’ll check them armed with the connection.queries (count and time) I acquired this time.
References
https://sukkiri.jp/books/sukkiri_sql4
