Django queryset filter - Q() | VS __in

Question

What is the difference between

queryset.filter(Q(foo='bar') | Q(foo='baz'))

and

queryset.filter(foo__in=['bar', 'baz'])

I'm finding that sometimes they produce different results and I can't figure out why.

I'm getting different results with these queries:

In [8]: Profile.objects.filter(image="").count()
Out[8]: 7173

In [9]: Profile.objects.filter(image=None).count()
Out[9]: 25946

In [10]: Profile.objects.filter(image__in=["", None]).count()
Out[10]: 7173

In [11]: Profile.objects.filter(Q(image="") | Q(image=None)).count()
Out[11]: 33119

I'm using PostgreSQL as my database engine.

Aribold

Answer 1

First will generate query:

SELECT .... FROM ... WHERE (FOO = 'bar' OR FOO = 'baz');

second will generate query:

SELECT .... FROM ... WHERE (FOO IN ('bar', 'baz'));

Both queries should compute same results, but there may be some performance differences, depending on database backend. Generally, using in should be faster.

Karoline

Can you think of a reason they might give different results? – Aribold

Maybe with an issue with database engine - SQLite is performing some comparsions always case sensitive and some always case insensitive, MySQL also have some differences here (you can set setting on MySQL table, so all comparsions will be done case insensitive). Other than that, there are no reasons for different output here. – Karoline

According to your edit, in SQL IN can't compare to NULL value, because nothing is equal to null, it is null or it isn't and in many backends it won't work. You should use OR here to achieve your result. – Karoline

Yeah, that was my conclusion but I just wanted it confirmed. – Aribold