Django in / not in query

sql

subqueries

django-orm

database-performance

byNikita Barsukov·Oct 31, 2024

Django uses __in lookup to filter model instances based on whether a field's value is IN or NOT IN a list. To get matches, Django uses filter. To omit these values, Django uses exclude.

Here’s a common usage:

# Match me those:
YourModel.objects.filter(field__in=[values])

# I don't like these:
YourModel.objects.exclude(field__in=[values])

The __in syntax is compatible with any iterable (lists, tuples, querysets).
Please use YourModel in the place of your model.
Kindly insert field and [values] with your field name and the values to match or exclude.

Using subqueries to exclude

If curtailing certain records from your condition based on another table, here's a solid move with Django for a 'not in' query:

leave_out_ids = Table2.objects.filter(your_condition).values_list('id', flat=True)
Table1.objects.exclude(id__in=leave_out_ids)

Extra gifts:

The your_condition filters data in Table2.
exclude denies the records in Table1 where id features in leave_out_ids.
The flat=True makes sure the list of IDs is one-level and not a list of tuples.

In a scenario with ForeignKey relations:

YourModel.objects.exclude(related_table__field=value)

Here, we're snubbing items where the related model has a field on par with the specified value.

Complex queries with Q objects

Rummaging through complex questions? Q objects they're your rescue rope:

from django.db.models import Q

think_complex = ~Q(related_table__field=value)
YourModel.objects.filter(think_complex)

The ~ acts as a magic wand & negates the Q object.
Did I say you can store these Q objects in variables to dust off any redundancy?

Custom SQL and performance pointers

Write your custom SQL into the queries with objects.extra(). The control is all yours:

YourModel.objects.extra(where=["field NOT IN (SELECT field FROM Table2 ...)"])

Worthy to keep in mind:

It's robust but don't forget you're in the ring with SQL injection risks.
Keep your SQL efficient to uphold good performance.

Different situations and their solutions

Large list of values? Not a problem!

For a large list of values, using __in with a list input can get hefty:

big_values = range(int(1e6))
YourModel.objects.filter(field__in=big_values)
# Is it just me or is it getting crowded in here?

A subquery might serve here:

# Avoiding the crowd by chucking an elevator
YourModel.objects.exclude(field__in=AnotherModel.objects.all().values_list('field', flat=True))

Not IN with NULL values? Here's the catch!

NULL values can toss a wrench in NOT IN logic of SQL because a NULL understands nothing equal to it:

# Risky one! As NULL is a peculiar guest, it may not behave as planned.
YourModel.objects.exclude(field__in=nullable_values)

Instead, use two conditions—one to exclude and another to check for NULL:

# Reserve a separate corner for NULL at the party by using ISNULL
YourModel.objects.exclude(field__in=nullable_values).exclude(field__isnull=True)

Baking 'not in' logic

For custom 'not in' logic, you can turn a head with custom lookups:

from django.db.models import Lookup

class NotIn(Lookup):
    lookup_name = 'not_in'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return '%s NOT IN (%s)' % (lhs, rhs), params

#This is how you roll out custom lookups
YourModel.objects.get_queryset().filter(field__not_in=[values])