Django IN ~ SQL IN (original) (raw)

Summary: in this tutorial, you’ll learn how to use Django in to check if a value is in a set of values.

Introduction to the Django In #

We’ll use the Employee model in the HR application for the demonstration. The Employee model maps to the hr_employee table in the database:

The SQL IN operator returns true if a value is in a set of values:

field_name IN (v1, v2, ...)Code language: Python (python)

For example, you can use the IN operator to query the rows from the hr_employee table whose department_id is in a list like this:

SELECT * FROM hr_employee WHERE department_id IN (1,2,3)Code language: SQL (Structured Query Language) (sql)

In Django, you use the in operator:

>>> Employee.objects.filter(department_id__in=(1,2,3)) SELECT "hr_employee"."id", "hr_employee"."first_name", "hr_employee"."last_name", "hr_employee"."contact_id", "hr_employee"."department_id" FROM "hr_employee" WHERE "hr_employee"."department_id" IN (1, 2, 3)Code language: SQL (Structured Query Language) (sql)

Typically, you use a subquery with the in operator rather than a list of literal values. For example, you find all employees in the Sales and Marketing departments as follows:

`>>> departments = Department.objects.filter(Q(name='Sales') | Q(name='Marketing'))

Employee.objects.filter(department__in=departments) SELECT "hr_employee"."id", "hr_employee"."first_name", "hr_employee"."last_name", "hr_employee"."contact_id", "hr_employee"."department_id" FROM "hr_employee" WHERE "hr_employee"."department_id" IN ( SELECT U0."id" FROM "hr_department" U0 WHERE (U0."name" = 'Sales' OR U0."name" = 'Marketing') )`Code language: SQL (Structured Query Language) (sql)

How it works.

First, get the departments with the names Sales or Marketing:

departments = Department.objects.filter(Q(name='Sales') | Q(name='Marketing'))Code language: Python (python)

Second, pass the department QuerySet to the in operator:

Employee.objects.filter(department__in=departments)Code language: Python (python)

Behind the scenes, Django executes a query with the IN operator that matches the department id with a list of department id from a list:

SELECT "hr_employee"."id", "hr_employee"."first_name", "hr_employee"."last_name", "hr_employee"."contact_id", "hr_employee"."department_id" FROM "hr_employee" WHERE "hr_employee"."department_id" IN ( SELECT U0."id" FROM "hr_department" U0 WHERE (U0."name" = 'Sales' OR U0."name" = 'Marketing') )Code language: SQL (Structured Query Language) (sql)

NOT IN #

The NOT operator negates the IN operator. The NOT IN operator returns true if a value is not in a list of values:

field_name NOT IN (v1, v2, ...)

To perform NOT IN in Django, you can use the Q object and ~ operator:

~Q(field_name__in=(v1,v2,..))Code language: Protocol Buffers (protobuf)

For example, the following finds employees whose department id is not 1, 2, or 3:

>>> Employee.objects.filter(~Q(department_id__in=(1,2,3))) SELECT "hr_employee"."id", "hr_employee"."first_name", "hr_employee"."last_name", "hr_employee"."contact_id", "hr_employee"."department_id" FROM "hr_employee" WHERE NOT ("hr_employee"."department_id" IN (1, 2, 3))Code language: SQL (Structured Query Language) (sql)

Alternatively, you can use the exclude() method instead of the filter() method:

>>> Employee.objects.exclude(department_id__in=(1,2,3)) SELECT "hr_employee"."id", "hr_employee"."first_name", "hr_employee"."last_name", "hr_employee"."contact_id", "hr_employee"."department_id" FROM "hr_employee" WHERE NOT ("hr_employee"."department_id" IN (1, 2, 3))Code language: JavaScript (javascript)

Summary #

• Use the Django in to check if a value is in a list of values.

Was this tutorial helpful ?