78

this is a simple question. I'd like to know if it is the same to write:

queryset = Model.objects.filter(field=None)

than:

queryset = Model.objects.filter(field__isnull=True)

I'm using django 1.8

Alejandro Veintimilla
  • 10,743
  • 23
  • 91
  • 180

3 Answers3

64

They are equal:

>>> str(Person.objects.filter(age__isnull=True).query) == str(Person.objects.filter(age=None).query)
True
>>> print(Person.objects.filter(age=None).query)
SELECT "person_person"."id", "person_person"."name", "person_person"."yes", "person_person"."age" FROM "person_person" WHERE "person_person"."age" IS NULL
>>> print(Person.objects.filter(age__isnull=True).query)
SELECT "person_person"."id", "person_person"."name", "person_person"."yes", "person_person"."age" FROM "person_person" WHERE "person_person"."age" IS NULL

Exclusion: the Postgres JSON field (see the answer of @cameron-lee)

Nishi
  • 10,634
  • 3
  • 27
  • 36
knbk
  • 52,111
  • 9
  • 124
  • 122
  • Just wondering if using `field__isnull=True' is not faster than `field=None` I imagine, isnull would be faster as it seems to match the database – unlockme May 10 '18 at 09:30
  • 1
    @unlockme: well they result in the *same* query: Django ORM is smart enough to use `IS NULL`, hence except for some small difference due to the handling of this `=None`, vs `__isnull=True` when *translating* the call in a query, the two run in the same time. – Willem Van Onsem Jun 06 '18 at 14:31
  • 3
    I've had some issues with using __in and None. Such as: __in=(None, "", " ") will result to False while Q(__isnull=True)|Q(__in=("", " ")) would result to True. Could never truly figure out why. – Dougyfresh Aug 23 '19 at 18:32
  • @Dougyfresh seems like a Django bug - quoted here too: https://stackoverflow.com/questions/20225340/django-queryset-filter-in-list-with-null – Anupam Oct 27 '21 at 14:05
  • 1
    @Anupam, this is not a bug. Null does not compare as true to any value including null. For example, "select null = null;" will return null, not true. "select null in [1, 2, null]" will return false. The only way to test if something is null is to use "is null". – Vinay Pai Jan 20 '22 at 21:26
47

It depends on the type of field. As mentioned in other answers, they are usually equivalent but in general, this isn't guaranteed.

For example, the Postgres JSON field uses =None to specify that the json has the value null while __isnull=True means there is no json:

https://docs.djangoproject.com/en/3.0/ref/contrib/postgres/fields/#jsonfield

null json vs no json

Cameron Lee
  • 973
  • 9
  • 11
  • 1
    Really great answer! Love it – Manan Mehta Jun 23 '20 at 19:01
  • I am currently having a hard time matching "None" Postgres JSON fields. Neither filter(field__is_null=True) or filter(field=None) are working. How would I match for these null postgres JSON fields? – thrillhouse Mar 22 '21 at 23:33
  • @thrillhouse: it sounds like maybe you have stored the string "None" which is different than both SQL null or json null. – Cameron Lee Mar 24 '21 at 15:43
36

Just to keep in mind that you cannot reverse the condition with your first solution:

# YOU CANNOT DO THIS
queryset = Model.objects.filter(field!=None)

However you can do this:

queryset = Model.objects.filter(field__isnull=False)
Manan Mehta
  • 5,501
  • 1
  • 18
  • 18
  • 12
    You can do `.exclude(field=None)` if you need to reverse the condition - personally I prefer `.exclude` because it is more explicit. – Paulo Scardine Nov 14 '18 at 12:07
  • 1
    However every time you do that you wrap the query, and multiple query wrappings, if unnecessary, can be cumbersome SQL-wise and even slow down performance @PauloScardine – wonderwhy Jun 21 '19 at 15:23
  • 1
    Interesting, @wonderwhy do you have a source for the "performance slowdown" due to wrappings? I never had any performance glitch with PostgreSQL due to multiple wrappings - in my experience the planner does a very good job with this kind of query. About the query being cumbersome SQL-wise the whole point of using an ORM is not having to deal with SQL - I could not care less about the automatically generated SQL as long as it performs well... :-) – Paulo Scardine Jun 21 '19 at 18:34
  • rather a comment – Ali Asgari Aug 26 '19 at 16:32