DjangoのQuerySetでSQLの IS NULL を表現する方法

Djangoさわってて、「IS NULL」ってQuerysetでどうやるんだっけ、と思って調べたのでメモしておく

例えば以下のBookモデルがあるとして

class City(models.Model):
    name = models.CharField(max_length=30)


class Person(models.Model):
    hometown = models.ForeignKey(City)
    name = models.CharField(max_length=100)


class Book(models.Model):
    author = models.ForeignKey(Person, null=True)
    title = models.CharField(max_length=100, null=True)

titleがNullのBookを引っ張ってくる場合

Book.objects.filter(title__isnull=True)

発行されるSQL

SELECT `books_book`.`id`, `books_book`.`author_id`, `books_book`.`title` FROM `books_book` WHERE `books_book`.`title` IS NULL

ついでにAnd検索、Or検索についてメモ

titleもauthorもNullのBookを引っ張ってくる場合

Book.objects.filter(title__isnull=True, author__isnull=True)

発行されるSQL

SELECT `books_book`.`id`, `books_book`.`author_id`, `books_book`.`title`
FROM `books_book`
LEFT OUTER JOIN `books_person` ON (`books_book`.`author_id` = `books_person`.`id`)
WHERE (`books_book`.`title` IS NULL AND `books_person`.`id` IS NULL)

titleとauthor、どちらかがNullのBookを引っ張ってくる場合

from django.db.models import Q
Book.objects.filter(Q(title__isnull=True)|Q(author__isnull=True))

発行されるSQL

SELECT `books_book`.`id`, `books_book`.`author_id`, `books_book`.`title`
FROM `books_book`
LEFT OUTER JOIN `books_person` ON (`books_book`.`author_id` = `books_person`.`id`)
WHERE (`books_book`.`title` IS NULL OR `books_person`.`id` IS NULL)