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.

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.