Understanding Django queryset field lookup in
<p>Short version: Why does Model.objects.exclude(..__in=[None]) exclude every object?</p>

<p>I have encountered an interesting behaviour of django field lookup that I do not understand. Let's say I have 21 objects of a given model:</p>

<pre><code>&gt;&gt;&gt; Model.objects.count()
21
</code></pre>

<p>If I exclude a given private key directly or with <code>in</code> field lookup I get the expected behaviour:</p>

<pre><code>&gt;&gt;&gt; Model.objects.exclude(pk=1).count()
20
&gt;&gt;&gt; Model.objects.exclude(pk__in=[1]).count()
20
</code></pre>

<p>If I exclude the private key of value <code>None</code> I get the expected result:</p>

<pre><code>&gt;&gt;&gt; Model.objects.exclude(pk=None).count()
21
</code></pre>

<p>However if I do the same with the <code>in</code> field lookup I get nothing back:</p>

<pre><code>&gt;&gt;&gt; Model.objects.exclude(pk__in=[None]).count()
0
</code></pre>

<p>Why is this so?</p>

<p>I suspect this is because of the way SQL treats NULL. The query compiles to <code>SELECT COUNT(*) FROM mymodel WHERE NOT (id IN (NULL));</code>.</p>

<p>See for example <a href="http://stackoverflow.com/questions/129077/not-in-clause-and-null-values">this question</a> for a discussion of why NOT IN with NULL always returns empty.</p>

python django-queryset