OPTIONAL
MATCH
matches patterns against your graph database, just like MATCH
does.
The difference is that if no matches are found, OPTIONAL
MATCH
will use NULL
s for missing parts of the pattern.
OPTIONAL
MATCH
could be considered the Cypher equivalent of the outer join in SQL.
Either the whole pattern is matched, or nothing is matched.
Remember that WHERE
is part of the pattern description, and the predicates will be considered while looking for matches, not after.
This matters especially in the case of multiple (OPTIONAL
) MATCH
clauses, where it is crucial to put WHERE
together with the MATCH
it belongs to.
Tip | |
---|---|
To understand the patterns used in the |
The following graph is used for the examples below:
If a relationship is optional, use the OPTIONAL
MATCH
clause. This is similar to how a SQL outer join works. If the relationship is there, it is returned. If it’s not, NULL
is returned in it’s place.
Query.
MATCH (a:Movie { title: 'Wall Street' }) OPTIONAL MATCH (a)-->(x) RETURN x
Returns NULL
, since the node has no outgoing relationships.
Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 match (a:Movie {title: 'Wall Street'}) optional match (a)-->(x) return x
Returning a property from an optional element that is NULL
will also return NULL
.
Query.
MATCH (a:Movie { title: 'Wall Street' }) OPTIONAL MATCH (a)-->(x) RETURN x, x.name
Returns the element x (NULL
in this query), and NULL
as its name.
Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 match (a:Movie {title: 'Wall Street'}) optional match (a)-->(x) return x, x.name
Just as with a normal relationship, you can decide which identifier it goes into, and what relationship type you need.
Query.
MATCH (a:Movie { title: 'Wall Street' }) OPTIONAL MATCH (a)-[r:ACTS_IN]->() RETURN r
This returns a node, and NULL
, since the node has no outgoing ACTS_IN
relationships.
Try this query live. create (_0:`Person` {`name`:"Oliver Stone"}) create (_1:`Person` {`name`:"Charlie Sheen"}) create (_2:`Person` {`name`:"Martin Sheen"}) create (_3:`Movie` {`name`:"TheAmericanPresident", `title`:"The American President"}) create (_4:`Movie` {`name`:"WallStreet", `title`:"Wall Street"}) create (_5:`Person` {`name`:"Rob Reiner"}) create (_6:`Person` {`name`:"Michael Douglas"}) create _0-[:`DIRECTED`]->_4 create _1-[:`ACTED_IN`]->_4 create _1-[:`FATHER`]->_2 create _2-[:`ACTED_IN`]->_4 create _2-[:`ACTED_IN`]->_3 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_4 create _6-[:`ACTED_IN`]->_3 match (a:Movie {title: 'Wall Street'}) optional match (a)-[r:ACTS_IN]->() return r
Copyright © 2013 Neo Technology