What is Django ORM equivalent for sql "field <> 1"?

Question

Assuming that field is nullable, in raw SQL condition

WHERE field <> 1

will exclude all 1 rows and also exclude all NULL rows.

Why Django ~Q(field=1) makes query

WHERE (NOT (field = 1 AND field IS NOT NULL))

which results to include NULL rows. Is there equivalent for SQL field <> 1 in Django ORM?

Answer 1

Because Django's interpretation makes more sense from a Python developer's point of view. SQL has separate constructs for checking for null values (IS NULL and IS NOT NULL), and both field == NULL and field <> NULL are NULL (neither true nor false), which is not exactly intuitive.

Think of it this way: If I want to get all rows where field <> 1 I probably want to get rows with NULL values as well because NULL is not 1.

If you want to replicate the same behaviour you can use something like

MyModel.objects.exclude(field=1).exclude(field__isnull=True)