Django QuerySet API reference: alternative SQL queries for each method

In this article, we are going to consider as much methods of QuerySet API of Django as possible, in the context of lower-level, raw SQL queries. I believe that, this will hugely benefit to understand the concepts more deeply. Of course, if you have some knowledge on SQL :)

QuerySet API reference cover

Prior to start, let me share the official documentation of QuerySet API:

🔗 https://docs.djangoproject.com/en/5.0/ref/models/querysets

I assume that you already know what is a QuerySet and basic ORM features of Django. Since we are to work with SQL queries directly, it is essential to derive the corresponding query of each method somehow. First of all, let’s create a new Django project with some models:

$ py -m venv .env
$ .env\Scripts\activate
$ pip install django

$ django-admin startproject core .
$ python manage.py startapp inventory

# inventory/models.py
from django.db import models

class Car(models.Model):
    name = models.CharField(max_length=255)
    factory = models.CharField(max_length=255)
    year = models.SmallIntegerField()

    def __str__(self):
        return self.name

$ python manage.py makemigrations
$ python manage.py migrate

️️⚠️ I will use Django Shell to test the functionality. Make sure that you have run your project before accessing the Shell:

$ python manage.py runserver

Then, on another Terminal:

$ python manage.py shell

Keep in mind that Django Shell runs from project root folder. So, in order to access the models we have previously created:

>>> from inventory.models import Car

Now, we can use Shell to test the QuerySet methods effectively. Let’s first consider .all() method as it is the easiest one so far:

>>> print(Car.objects.all().query)
SELECT "inventory_car"."id", "inventory_car"."name", "inventory_car"."factory", "inventory_car"."year" FROM "inventory_car"

✅ Using this approach, we can determine the underlying SQL queries for each method.

Basically, QuerySet API methods are classified into 5 categories:

• Methods that return new QuerySets;
• Operators that return new QuerySets;
• Methods that do not return new QuerySets;
• Field lookups;
• Aggregate functions.

It may be difficult to show every method, but I try as much as I can. From now on, I only show the method name, its short description and usage followed by corresponding alternative SQL query, since all other information is available in the official documentation.

👉 all() — Returns a copy of the current QuerySet.

>>> Car.objects.all()

SELECT * FROM inventory_car;

👉 filter() — Returns a new QuerySet containing objects that match the given lookup parameters.

>>> Car.objects.filter(factory="BYD")

SELECT * FROM inventory_car WHERE inventory_car.factory = BYD;

👉 exclude() — Returns a new QuerySet containing objects that do not match the given lookup parameters.

>>> Car.objects.exclude(year=2013)

# Other usages
>>> Car.objects.exclude(year=2020, factory="BYD")
>>> Car.objects.exclude(year=2020).exclude(factory="BYD")

SELECT * FROM inventory_car WHERE NOT (inventory_car.year = 2013)

👉 annotate() — allows to add calculated fields to each object in the queryset based on some aggregation or annotation performed on related fields (similar to GROUP BY)

>>> Car.objects.annotate(total_cars=Count('factory'))

SELECT "inventory_car"."id", "inventory_car"."name", "inventory_car"."factory", "inventory_car"."year", COUNT("inventory_car"."factory") AS "total_cars" 
FROM "inventory_car" GROUP BY "inventory_car"."id", "inventory_car"."name", "inventory_car"."factory", "inventory_car"."year"

👉 order_by() — allows to define the order in which database rows should be retrieved based on one or more fields.

>>> Car.objects.all().order_by('name')

# Plus, we can give the direction:
>>> Car.objects.all().order_by('name').asc()
>>> Car.objects.all().order_by('name').desc()

SELECT "inventory_car"."id", "inventory_car"."name", "inventory_car"."factory", "inventory_car"."year" 
FROM "inventory_car" 
ORDER BY "inventory_car"."name" ASC

👉 reverse() — allows to reverse the order in which a queryset’s elements are returned.

>>> q = Car.objects.all().order_by('name')
>>> q.reverse()

# To get last 5 elements
>>> l5 = q.reverse()[:5]

SELECT "inventory_car"."id", "inventory_car"."name", "inventory_car"."factory", "inventory_car"."year" 
FROM "inventory_car" 
ORDER BY "inventory_car"."name" DESC

-- Just changes ASC to DESC

👉 distinct() — used to eliminate duplicate objects.

>>> Car.objects.distinct()
>>> Car.objects.order_by('name').distinct('name')

SELECT DISTINCT "inventory_car"."id", "inventory_car"."name", "inventory_car"."factory", "inventory_car"."year" 
FROM "inventory_car" 
ORDER BY "inventory_car"."name" ASC

👉 values() — is used to specify which fields of the model you want to retrieve.

>>> Car.objects.values('name', 'factory')

SELECT "inventory_car"."id", "inventory_car"."name", "inventory_car"."factory"
FROM "inventory_car"

👉 dates() — returns a list of unique dates within a given queryset, based on the specified field.

>>> queryset = MyModel.objects.dates('date_field', 'month')

SELECT DISTINCT DATE_TRUNC('month', date_field) AS date_field
FROM myapp_mymodel;

👉 datetimes() — similar to .dates(), but operates on datetime fields instead of date fields.

>>> queryset = MyModel.objects.datetimes('datetime_field', 'month', tzinfo=timezone.utc)

SELECT DISTINCT DATE_TRUNC('month', datetime_field AT TIME ZONE 'UTC') AS datetime_field
FROM myapp_mymodel;

👉 union() — combines 2 or more querysets and returns their union.

>>> q1 = Car.objects.filter(year=2020)
>>> q2 = Car.objects.filter(year=2010)
>>> union = q1.union(q2)

(SELECT "inventory_car"."year" FROM "inventory_car"."year" WHERE "inventory_car"."year" = 2020)
UNION
(SELECT "inventory_car"."year" FROM "inventory_car"."year" WHERE "inventory_car"."year" = 2010)

👉 intersection() — returns a queryset containing the intersection of the results of two querysets.

>>> q1 = Car.objects.filter(year=2020)
>>> q2 = Car.objects.filter(year=2010)
>>> intersect = q1.intersection(q2)

(SELECT "inventory_car"."year" FROM "inventory_car"."year" WHERE "inventory_car"."year" = 2020)
INTERSECT
(SELECT "inventory_car"."year" FROM "inventory_car"."year" WHERE "inventory_car"."year" = 2010)

👉 difference() — returns a queryset containing the elements that are in the first queryset but not in the second queryset.

>>> q1 = Car.objects.filter(year=2020, factory="BYD")
>>> q2 = Car.objects.filter(factory="BYD")
>>> diff = q1.difference(q2)

(SELECT "inventory_car"."year" FROM "inventory_car"."year" WHERE "inventory_car"."year" = 2020)
EXCEPT
(SELECT "inventory_car"."year" FROM "inventory_car"."year" WHERE "inventory_car"."year" = 2010)

👉 select_related() —

>>> Car.objects.select_related()

-- Alternative SQL query: Soon

👉 prefetch_related() —

>>> Car.objects.prefetch_related()

-- Alternative SQL query: Soon

👉 extra() — sometimes, the Django query syntax by itself can’t easily express a complex WHERE clause. In such cases, we can use this method for injecting specific clauses into SQL generated by a QuerySet.

>>> Car.objects.extra(where = ["name = %s"], params = ['Kia'])

SELECT *
FROM inventory_car
WHERE factory = 'Kia';

👉 defer() — in some complex data-modeling situations, your models might contain a lot of fields, some of which could contain a lot of data (for example, text fields), or require expensive processing to convert them to Python objects. If you are using the results of a queryset in some situation where you don’t know if you need those particular fields when you initially fetch the data, you can tell Django not to retrieve them from the database.

>>> Car.objects.defer("description")
# Car description is relatively long text ans it may not be necessary at initial fetching of the data

SELECT "id", "field3", "field4"  -- Excluding 'field1' and 'field2'
FROM myapp_mymodel;

👉 only() — is used to retrieve only specific fields from the database, excluding all other fields.

>>> Car.objects.only("factory")

SELECT name FROM Car

👉 Boolean operations: AND (&), OR (|), XOR (^) are also supported in QuerySet management.

>>> Car.objects.filter(year=2010) & Car.objects.filter(year=2020)
>>> Car.objects.filter(year=2010) | Car.objects.filter(year=2020)
>>> Car.objects.filter(year=2010) ^ Car.objects.filter(year=2020)

👉 get() — retrieves a single object from the database that matches the given query parameters.

>>> Car.objects.get(year=2018)

SELECT * FROM Car WHERE year = 2018;

👉 create() — creates a new object instance and saves it to the database in a single step.

>>> Car.objects.create(name="Sorento", factory="Kia", year=2023)

INSERT INTO Car(name, factory, year) VALUES ('Sorento', 'Kia', 2023);

👉 get_or_create() — retrieves an object if it exists based on the provided query parameters, otherwise creates a new object with those parameters.

>>> Car.objects.get_or_create(name="Sorento", factory="Kia", year=2023)

-- I think, there is no exact SQL equivalent

👉 bulk_create() — inserts multiple objects into the database in a single query, which can be more efficient than saving each object individually.

>>> Car.objects.bulk_create(
  [
    Car(name="K5", factory="Kia", year=2021),
    Car(name="K8", factory="Kia", year=2023),
  ]
)

INSERT INTO Car 
VALUES ("K5", "Kia", 2021), ("K8", "Kia", 2023);

👉 count() — returns the number of objects in the database.

>>> Car.objects.count()

SELECT COUNT(*) FROM Car

👉 in_bulk() — takes a list of field values (id_list) and the field_name for those values, and returns a dictionary mapping each value to an instance of the object with the given field value.

>>> Car.objects.in_bulk([1,2,3])
>>> Car.objects.in_bulk([1,2,3], field_name="name")

SELECT * FROM Car WHERE id IN (1, 2, 3);
-- OR
SELECT name FROM Car WHERE id IN (1, 2, 3);

👉 latest() — retrieves the latest object based on the specified field.

>>> Car.objects.latest('year')

SELECT * FROM Car ORDER BY year DESC LIMIT 1;

👉 earliest() — retrieves the earliest object based on the specified field.

>>> Car.objects.earliest('year')

SELECT * FROM Car ORDER BY year ASC LIMIT 1;

👉 first() — retrieves the first object in the queryset, according to the order of objects as they are stored in the database.

>>> Car.objects.first()

SELECT * FROM Car LIMIT 1;

👉 last() — retrieves the last object in the queryset, according to the order of objects as they are stored in the database.

>>> Car.objects.last()

SELECT * FROM Car ORDER BY id DESC LIMIT 1;

👉 exists() — checks if there are any objects that match the given query parameters.

>>> Car.objects.get(id=2).exists()

SELECT * FROM Car WHERE EXISTS id=2;

👉 contains() — is used to check object membership in a QuerySet.

>>> queryset.contains(object)

-- There is not exact representation in SQL

👉 update() — updates the fields of the objects that match the given query parameters with the provided values in a single database query.

>>> Car.objects.get(id=2).update(name="K5")

UPDATE Car SET name = 'K5';

👉 delete() — deletes the objects that match the given query parameters from the database in a single database query.

>>> Car.objects.get(id=5).delete()

DELETE * FROM Car WHERE id=5;

👉 explain() — generates an explanation of the query plan for the given queryset. This is useful for analyzing the performance of the query and identifying potential optimizations.

>>> Car.objects.all().explain()

EXPLAIN SELECT * FROM Car;

There are so many methods in Django QuerySet API that we haven’t seen all of them yet. But at this point, I gave enough information in terms of equivalent SQL queries. At least I hope.

🏁 Yes, some methods are missing and maybe they can also be represented in SQL somehow. But now I cannot express them, yet in the future I can fix them. Thanks for your attention!