# How do I express a NOT IN using ROM?

**URL:** <https://discourse.rom-rb.org/t/how-do-i-express-a-not-in-using-rom/582>\
**Category:** Issues\
**Created:** [January 18, 2023, 10:31pm UTC](https://discourse.rom-rb.org/t/how-do-i-express-a-not-in-using-rom/582 "2023-01-18T22:31:01Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![abrokenjester](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/abrokenjester/32/282_2.png) [@abrokenjester](https://discourse.rom-rb.org/u/abrokenjester)\
**Post date:** [January 18, 2023, 10:31pm UTC](https://discourse.rom-rb.org/t/how-do-i-express-a-not-in-using-rom/582/1 "2023-01-18T22:31:01Z")

</div>

Hi folks,

new ROM user here. I’m trying to express the following SQL query using ROM:

```auto
SELECT id, name 
FROM rel WHERE ((rel.account = '...') AND rel.id NOT IN (SELECT id FROM ....))

```

The subselect returns a set of ids that I want to filter _out_ of the result of the outer query, hence the NOT IN. I can’t seem to find a way to express this though.

A regular `IN` works fine, like this:

```auto
subselect = rel.select(....).query

full_query = rel.where(rel.account: ...).where { id.in(subselect) }

```

the above results in the correct SQL. However, when I try to negate somehow, it fails.

I’ve tried the `not` method:

```auto
full_query = rel.where(rel.account: ...).where { id.not(subselect) }

```

but that gets converted to `!=`, and gives this (invalid) SQL:

```auto
SELECT id, name 
FROM rel WHERE ((rel.account = '...') AND rel.id != (SELECT id FROM ....))

```

I’ve also tried prefixing with a `!`:

```auto
full_query = rel.where(rel.account: ...).where { !id.in(subselect) }

```

but that results in this SQL:

```auto
SELECT id, name 
FROM rel WHERE ((rel.account = '...') AND false)

```

Any tips? Am I overlooking something fundamental or will I have to revert back to using raw SQL to express this?

---

<div class="post-metadata">

**Author:** ![abrokenjester](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/abrokenjester/32/282_2.png) [@abrokenjester](https://discourse.rom-rb.org/u/abrokenjester)\
**Post date:** [January 18, 2023, 10:41pm UTC](https://discourse.rom-rb.org/t/how-do-i-express-a-not-in-using-rom/582/2 "2023-01-18T22:41:30Z")

</div>

Of course, after banging my head against this for several days, I figure it out mere minutes after posting here 🙄

The trick is to use `.exclude` and stick the subselect arg in an array:

```auto
full_query = rel.where(rel.account: ...).exclude(id: [subselect])

```

I believe I actually tried this method at some point as well but didn’t use the array brackets around the subselect, which resulted in invalid SQL again. I’m not sure I follow the logic of sticking the subquery in an array but I won’t knock it too much: it works 🙂

EDIT I also discovered that my original attempt at using `not` also works if I put the subselect in an array:

```auto
full_query = rel.where(rel.account: ...).where { id.not([subselect]) }

```