How to filter for empty or null values in a Django QuerySet

Before we get into the specific code examples to properly filter QuerySets, it’s important to understand how Django handles keyword argument syntax and how it parses that information in a useful way.

While the official documentation provides far more detail, the crux of the syntax is the double-underscore, which is used to separate the field name from the lookuptype or ‘function’, followed by the value to compare to. Per the documentation, the syntax is written like so: field__lookuptype=value

Therefore, if are using field lookup/double-underscore syntax to find values where the name field of your model contains the word Smith, you’d use this syntax:

name__contains='Smith'

The critical part here is the syntax of the field name first, followed by a double-underscore and then the lookuptype. Things can be far more complex than this, but this knowledge will guide us through the rest of the tutorial.

Easily the most important method when working with Django models and the underlying QuerySets is the filter() method, which allows you to generate a QuerySet of objects that match a particular set of filtered parameters.

For example, our application has a Book model with a few basic fields: title, author, and date_published. We can quickly see that our database contains 20 books in total by using the count() method:

Now, by using filter(), we can retrieve a QuerySet of just those books that were published within the last 90 days period, like so:

With filter(), we can determine that merely 3 of our 20 total books were published within the last 90 day period.

Similar to the filter() method but acting as the mirror opposite is the exclude() method. As the name implies, exclude() functions by excluding all the matches from the supplied parameters, and returning the QuerySet that remains.

Thus if we use the exact same date_published example above but swap out filter() for exclude(), we’d expect the resulting book count to be the inverse: from 3 of 20 to now 17 of 20:

Sure enough that’s exactly what we get: Every book in the system that wasn’t excluded by our filtered date range – in other words, everything published more than 90 days ago.

Now that we understand the basics of using filter() and exclude() to retrieve a modified QuerySet in Django, we can use these methods to look for field values that are either empty or NULL.

As discussed above, there are a number of potential field lookups that can be used with filter() and exclude(), and when working with field values that are NULL, the field lookup of choice should be isnull.

Here we can use isnull to find all Books where the author field is NULL:

We’ve quickly determined that 2 of our books contain a NULL value for the author field.

While there isn’t a specific field lookup to locate empty fields (such as string fields that contain nothing but are not NULL in the database), we can approximate that functionality with the exact field lookup instead:

In the above example we see that there is currently 1 Book that has an empty string ('') in the title field.

One final tip is that it is possible to combine multiple field lookups by chaining together filter() or exclude() calls.

Here, we’ll use exclude() to remove all Books with either a NULL author or an empty title field. Since we know there are 2 and 1 books in each category, respectively, we’d expect to get a result of 17 out of the 20 total books:

There we have it! Some simple uses of filter() and exclude() to retrieve (or ignore) both NULL and empty values in Django QuerySets.