How to use NOT IN clause in sqlalchemy ORM query

Question

how do i convert the following mysql query to sqlalchemy?

SELECT * FROM `table_a` ta, `table_b` tb where 1
AND ta.id = tb.id
AND ta.id not in (select id from `table_c`)

so far i have this for sqlalchemy:

query = session.query(table_a, table_b)
query = query.filter(table_a.id == table_b.id)

fedorqui · Answer 1 · 2021-06-29T07:45:03.290

116

The ORM internals describe the not_in() operator (previously notin_()), so you can say:

query = query.filter(table_a.id.not_in(subquery))
#                               ^^^^^^

From the docs:

inherited from the ColumnOperators.not_in() method of ColumnOperators

implement the NOT IN operator.

This is equivalent to using negation with ColumnOperators.in_(), i.e. ~x.in_(y).

Note that version 1.4 states:

The not_in() operator is renamed from notin_() in previous releases. The previous name remains available for backwards compatibility.

So you may find notin_() in some cases.

edited Jun 29 '21 at 07:45

answered Aug 25 '17 at 14:05

fedorqui

275,237
103
548
598

17

This also works with a simple list in place of subquery. I.e. `notin_([42, 43, 44, 45])` – tandy Apr 29 '18 at 20:34
1

Renamed to `not_in()` in 1.4. https://docs.sqlalchemy.org/en/14/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators.not_in – user1023102 Jun 28 '21 at 17:56
Version 1.3.6 ```notin_()``` works!! – Jonnathan Carrasco Oct 18 '21 at 01:43

score 81 · Answer 2 · answered Oct 03 '14 at 15:53

81

Try this:

subquery = session.query(table_c.id)
query = query.filter(~table_a.id.in_(subquery))

Note: table_a, table_b and table_c should be mapped classes, not Table instances.

answered Oct 03 '14 at 15:53

Slava Bacherikov

1,983
13
15

hello. how do i join table_a and table_b in this example? – chrizonline Oct 03 '14 at 15:57
I assume, that `query` is query object that you showed above. – Slava Bacherikov Oct 03 '14 at 16:07
ohh ok now i get it. initially i was lost because i thought the example was not for join table. – chrizonline Oct 03 '14 at 16:14
27

this also works `query = query.filter(table_a.id.notin_(subquery))` – Jonathan Vanasco Oct 29 '15 at 18:44
What if the element is `None` ? – rosefun Dec 01 '20 at 04:16

score 19 · Answer 3 · answered Oct 03 '14 at 16:16

here is the full code:

#join table_a and table_b
query = session.query(table_a, table_b)
query = query.filter(table_a.id == table_b.id)

# create subquery
subquery = session.query(table_c.id)
# select all from table_a not in subquery
query = query.filter(~table_a.id.in_(subquery))

How to use NOT IN clause in sqlalchemy ORM query

3 Answers3