cypher equivalent to sql nested query

Question

In Cypher, unless I'm missing something, why can't I do:

match(m:Movie)
where not m.title in (match (bm:BadMovie) return bm.title)
return m

but have to go through an intermediate WITH:

with match (bm:BadMovie) return collect(bm.title) as badTitles
match(m:Movie)
where not m.title in badTitles
return m

In SQL, I'd just do:

select * from Movie where title not in (select title from BadMovie)

Also, I don't particularly like having to go through a 'collect' to do this, because I'm afraid of the memory costs for high volumes. I'd rather let the engine deduce the best way to do the lookups.

I do not have a relationship between those nodes, and do not want one. I'm just curious why an expression returning a series of nodes wouldn't be allowed within another one.

Thank you

Answer 1

In the case of your particular query, you don't have to use WITH at all, there's an easier way to do this:

MATCH (m:Movie) WHERE NOT m:BadMovie RETURN m;

That's substantially easier than the subquery you're doing in SQL.

On the question of "why does cypher not allow subqueries" -- it simply hasn't been built into the language yet. There are a number of related cypher improvement proposals on the opencypher site that you can see -- others are thinking about and proposing topics related to this.