10.2. Optional Match

Introduction

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 NULLs 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]Tip

To understand the patterns used in the OPTIONAL MATCH clause, read Section 8.8, “Patterns”.

The following graph is used for the examples below:

Figure 10.2. Graph

Relationship

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.

Result

x
1 row

<null>

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`]->_3 create _2-[:`ACTED_IN`]->_4 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_3 create _6-[:`ACTED_IN`]->_4 match (a:Movie {title: 'Wall Street'}) optional match (a)-->(x) return x

Properties on optional elements

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.

Result

xx.name
1 row

<null>

<null>

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`]->_3 create _2-[:`ACTED_IN`]->_4 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_3 create _6-[:`ACTED_IN`]->_4 match (a:Movie {title: 'Wall Street'}) optional match (a)-->(x) return x, x.name

Optional typed and named relationship

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.

Result

r
1 row

<null>

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`]->_3 create _2-[:`ACTED_IN`]->_4 create _5-[:`DIRECTED`]->_3 create _6-[:`ACTED_IN`]->_3 create _6-[:`ACTED_IN`]->_4 match (a:Movie {title: 'Wall Street'}) optional match (a)-[r:ACTS_IN]->() return r