19

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.

gdvalderrama
  • 713
  • 1
  • 17
  • 26
Ross Lote
  • 814
  • 1
  • 8
  • 19

1 Answers1

20

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.

GwynBleidD
  • 20,081
  • 5
  • 46
  • 77
  • Can you think of a reason they might give different results? – Ross Lote Oct 02 '15 at 10:03
  • 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. – GwynBleidD Oct 02 '15 at 10:56
  • 5
    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. – GwynBleidD Oct 02 '15 at 11:08
  • Yeah, that was my conclusion but I just wanted it confirmed. – Ross Lote Oct 02 '15 at 11:10