Django ORM: equivalent of SQL `NOT IN` query

Using Django
1

Howdy friends,

I’ve been racking my brain trying to get the ORM to do an equivalent of a SQL NOT IN clause. Unfortunately, I haven’t been able to with either exclude() or a Q object. These both result in a clause such as:

NOT (id IN ())

instead of:

id NOT IN ()

There are more details here: https://stackoverflow.com/questions/60671987/django-orm-equivalent-of-sql-not-in-exclude-and-q-objects-do-not-work

But am I missing something obvious? Or should I just drop to raw SQL here? Thanks in advance for having a look if this is in your wheelhouse.

2

You should be able to make a custom lookup that inherits from In and replaces it with NOT IN.

1 Like
3

Thank you, thank you, THANK YOU.

TIL: Custom Lookups!

4

Why do you specifically need ‘NOT IN’? The results differ because of NULLs? Is it something we should consider adding to Django?

5

I’m working on the P.R. right now, as a matter of fact! The result of NOT (id IN (a, b, c)) is not always the same as id NOT IN (a, b, c). That is the disparity that was hitting me. I’m not the first to notice the subtle difference:

https://code.djangoproject.com/ticket/25113#comment:7

The details at the Stack Overflow post show the specific use case - a logging table from Univa Grid Engine (nee Oracle Grid Engine, nee Sun Grid Engine). To find jobs that are currently running, we need to find rows that have a “delivered” event, that do not have a “finished” event. To find jobs that are currently queued, we need to find rows that have a “pending” event, that do not have a “delivered” event. The NOT IN with a Subquery is working wonderfully for that now.

Thanks again for your help. I’d love it if you could review the code before I P.R. it!

6

Could you try to come up with a simplified test case that demonstrates why this is needed beyond possible optimizations? I’m pretty sure @adamchainz’s intuition is right wrt to NULL handling and if that’s the case you just need to adjust your subquery or outer query lookup to deal which such value to achieve the same results.

To me this is a similar problem to the often proposed __ne lookup mapping to != that we’ve decided not to include in Django core to avoid confusion wrt to NULL handling. This comment from Anssi clearly explains why this not a good idea; it breaks filter and exclude complementary when NULL values are involved.

This is explained in more details in https://stackoverflow.com/questions/129077/null-values-inside-not-in-clause

1 Like