My cypher WHERE clause is not filtering

Question

I have a cypher query that is something like:

MATCH(e:ZOOT {id:100})
OPTIONAL MATCH(e)-[a1]->(X:QAZ)
OPTIONAL MATCH(e)-[a2]->(Y:WSX)
WHERE a1 is not null or a2 is not null
RETURN e, a1, a2

What I want is for rows producing neither a1 or a2 to be filtered away.

Yet my statement is returning rows in all cases, even of a1 and a2 are both null.

How does WHERE really work?

edit - clarification added to query

Answer 1

The reason you're seeing confusing results is because you're assuming the WHERE applies to the entire result before being pumped out by the RETURN. However, this isn't the case.

From the documentation on Cypher Structure:

WHERE: Not a clause in its own right, but rather part of MATCH, OPTIONAL MATCH and WITH. Adds constraints to a pattern, or filters the intermediate result passing through WITH.

So if I place parentheses to show how the clauses group together, it would look like this:

MATCH (e:ZOOT {id:100})
OPTIONAL MATCH(e)-[a1]->(X:QAZ)

(OPTIONAL MATCH(e)-[a2]->(Y:WSX)
WHERE a1 is not null or a2 is not null)

RETURN e, a1, a2

Your WHERE is only applying to that OPTIONAL MATCH (that particular OPTIONAL MATCH will only be included if a1 is not null or a2 is not null), which wasn't your intention. You want to apply it to the whole thing, so the easiest way to do that is to separate the queries with a WITH like so:

MATCH (e:ZOOT {id:100})
OPTIONAL MATCH(e)-[a1]->(:QAZ)
OPTIONAL MATCH(e)-[a2]->(:WSX)
WITH e, a1, a2
WHERE a1 is not null or a2 is not null
RETURN e, a1, a2

There's a way to optimize this query a bit if you're not really interested in the relationships themselves, and just want to know if your :ZOOT node has a match to either a QAZ node or a WSX node. You can use EXISTS() like so:

MATCH (e:ZOOT {id:100})
WHERE EXISTS((e)-->(:QAZ)) OR EXISTS((e)-->(:WSX))
RETURN e

Note that since you didn't provide the relationship type or use the X and Y variables bound to your end nodes, I'm assuming you aren't interested in them; I've removed them to avoid confusion from anyone reading your query.