# Django ORM: equivalent of SQL \`NOT IN\` query **URL:** \ **Category:** Using Django\ **Created:** [March 13, 2020, 2:52pm UTC](https://forum.djangoproject.com/t/django-orm-equivalent-of-sql-not-in-query/1480 "2020-03-13T14:52:23Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![FlipperPA](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/flipperpa/32/181_2.png) [@FlipperPA](https://forum.djangoproject.com/u/FlipperPA)\ **Post date:** [March 13, 2020, 2:52pm UTC](https://forum.djangoproject.com/t/django-orm-equivalent-of-sql-not-in-query/1480/1 "2020-03-13T14:52:23Z")
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](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. ---
**Author:** ![adamchainz](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/adamchainz/32/26_2.png) [@adamchainz](https://forum.djangoproject.com/u/adamchainz)\ **Post date:** [March 13, 2020, 3:11pm UTC](https://forum.djangoproject.com/t/django-orm-equivalent-of-sql-not-in-query/1480/2 "2020-03-13T15:11:56Z")
You should be able to make a [custom lookup](https://docs.djangoproject.com/en/3.0/howto/custom-lookups/) that inherits [from `In`](https://github.com/django/django/blob/40a64dd1e24d45f8e00a55b22a5174b8f1359b5c/django/db/models/lookups.py#L345) and replaces it with `NOT IN`. ---
**Author:** ![FlipperPA](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/flipperpa/32/181_2.png) [@FlipperPA](https://forum.djangoproject.com/u/FlipperPA)\ **Post date:** [March 13, 2020, 8:55pm UTC](https://forum.djangoproject.com/t/django-orm-equivalent-of-sql-not-in-query/1480/3 "2020-03-13T20:55:51Z")
Thank you, thank you, THANK YOU. TIL: Custom Lookups! ---
**Author:** ![adamchainz](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/adamchainz/32/26_2.png) [@adamchainz](https://forum.djangoproject.com/u/adamchainz)\ **Post date:** [March 13, 2020, 11:10pm UTC](https://forum.djangoproject.com/t/django-orm-equivalent-of-sql-not-in-query/1480/4 "2020-03-13T23:10:17Z")
Why do you specifically need ‘NOT IN’? The results differ because of `NULL`s? Is it something we should consider adding to Django? ---
**Author:** ![FlipperPA](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/flipperpa/32/181_2.png) [@FlipperPA](https://forum.djangoproject.com/u/FlipperPA)\ **Post date:** [March 14, 2020, 3:41pm UTC](https://forum.djangoproject.com/t/django-orm-equivalent-of-sql-not-in-query/1480/5 "2020-03-14T15:41:18Z")
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](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! ---
**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\ **Post date:** [March 14, 2020, 10:05pm UTC](https://forum.djangoproject.com/t/django-orm-equivalent-of-sql-not-in-query/1480/6 "2020-03-14T22:05:01Z")
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](https://code.djangoproject.com/ticket/5763#comment:19) 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](https://stackoverflow.com/questions/129077/null-values-inside-not-in-clause)