Neo4j OPTIONAL MATCH not returning null

Question

In Neo4j have 4 person nodes in the graph; 2 having relation ship with Movie nodes and other 2 no relationship.

Expecting below query to return only 2 nodes with out relationship , but it returns all.

Let me know if anything missed in the query. I am new to Neo4J

MATCH (p:Person) 
OPTIONAL MATCH (p) -[r]- (m:Movie)  
Where type(r) = null 
Return p,type(r),m

Thanks!

InverseFalcon InverseFalcon · Accepted Answer · 2017-02-04T19:09:42

The reason you're getting unexpected results is due to the placement and meaning of the WHERE clause, as well as its interaction with the behavior of OPTIONAL MATCH.

WHERE only applies to the preceding MATCH, OPTIONAL MATCH, or WITH. In this case, it's not applying to all results of the query, it's applying to just the OPTIONAL MATCH, and this has an entirely different behavior than what you're expecting.

It helps if you isolate it and look at the OPTIONAL MATCH and WHERE alone:

OPTIONAL MATCH (p) -[r]- (m:Movie)  
Where type(r) = null

This means: optionally match from p to a :Movie m through relationship r where that r is null. You can't have a relationship that exists yet its type is null, that's impossible, so this OPTIONAL MATCH will always fail.

But because it's an OPTIONAL MATCH, rows won't be removed, instead the non-matched elements will be null. The result is rows with :Person p (from your previous match), and null for type(r) and m.

What you're actually intending is to first perform an optional match, and then filter out rows where the type is null. You want a WHERE that effects all rows of the query, not the OPTIONAL MATCH.

This requires us to add a WITH clause, and move the WHERE after it:

MATCH (p:Person) 
OPTIONAL MATCH (p) -[r]- (m:Movie)  
WITH p, r, m
Where type(r) = null 
Return p, type(r), m

Moving the WHERE from the OPTIONAL MATCH to the WITH changes its meaning, it will now apply to all rows of your query and, because it's no longer associated with an OPTIONAL MATCH, it will actually remove rows that don't fit the predicate rather than keeping rows and setting elements to null.

Now, all that said, there is an easier query to return what you want without using OPTIONAL MATCH at all:

MATCH (p:Person) 
WHERE NOT (p)--(:Movie) 
RETURN p

Match :Person nodes where there's no relationship from those :Persons to a :Movie node.

We can omit returning the type of relationship and the movie node because those won't exist according to your match.

Neo4j OPTIONAL MATCH not returning null

1 Answers