optional-match.adoc

OPTIONAL MATCH

Introduction

OPTIONAL MATCH matches patterns against a graph database, just as MATCH does. The difference is that if no matches are found, OPTIONAL MATCH will use a null for missing parts of the pattern. OPTIONAL MATCH could therefore be considered the Cypher equivalent of the outer join in SQL.

When using OPTIONAL MATCH, either the whole pattern is matched, or nothing is matched. The WHERE clause is part of the pattern description, and its 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 OPTIONAL MATCH clause, read Patterns.

Example graph

The following graph is used for the examples below:

To recreate the graph, run the following query in an empty Neo4j database:

CREATE
  (charlie:Person {name: 'Charlie Sheen'}),
  (martin:Person {name: 'Martin Sheen'}),
  (michael:Person {name: 'Michael Douglas'}),
  (oliver:Person {name: 'Oliver Stone'}),
  (rob:Person {name: 'Rob Reiner'}),
  (wallStreet:Movie {title: 'Wall Street'}),
  (charlie)-[:ACTED_IN]->(wallStreet),
  (martin)-[:ACTED_IN]->(wallStreet),
  (michael)-[:ACTED_IN]->(wallStreet),
  (oliver)-[:DIRECTED]->(wallStreet),
  (thePresident:Movie {title: 'The American President'}),
  (martin)-[:ACTED_IN]->(thePresident),
  (michael)-[:ACTED_IN]->(thePresident),
  (rob)-[:DIRECTED]->(thePresident),
  (martin)-[:FATHER_OF]->(charlie)

OPTIONAL MATCH in more detail

Like SQL, Cypher queries are constructed using various clauses which are chained together to feed intermediate results between each other. For example, the matching variables from one MATCH clause will provide the context in which the next clause exists. However, there are two important differences between Neo4j and SQL which helps to explain OPTIONAL MATCH further.

1. While it is both possible and advised to enforce partial schemas using indexes and constraints, Neo4j offers a greater degree of schema flexibility than a relational database. Nodes and relationships in a Neo4j database do not have to have a specific property set to them because other nodes or relationships in the same graph have that property (unless there is a property existence constraint created on the specific property).

2. Queries in Cypher are run as pipelines. If a clause returns no results, it will effectively end the query as subsequent clauses will have no data to execute upon.

For example, the following query returns no results:

MATCH (a:Person {name: 'Martin Sheen'})
MATCH (a)-[r:DIRECTED]->()
RETURN a.name, r

(no changes, no records)

This is because the second MATCH clause returns no data (there are no DIRECTED relationships connected to Martin Sheen in the graph) to pass on to the RETURN clause.

However, replacing the second MATCH clause with OPTIONAL MATCH does return results. This is because, unlike MATCH, OPTIONAL MATCH enables the value null to be passed between clauses.

MATCH (p:Person {name: 'Martin Sheen'})
OPTIONAL MATCH (p)-[r:DIRECTED]->()
RETURN p.name, r

Table 1. Result

p.name	r
"Martin Sheen"	<null>
Rows: 1

OPTIONAL MATCH can therefore be used to check graphs for missing as well as existing values, and to pass on rows without any data to subsequent clauses in a query.

Optional relationships

If the existence of a relationship is optional, use the OPTIONAL MATCH clause. If the relationship exists, it is returned. If it does not, null is returned in its place.

MATCH (a:Movie {title: 'Wall Street'})
OPTIONAL MATCH (a)-->(x)
RETURN x

Returns null, since the Movie node Wall Street has no outgoing relationships.

Table 2. Result

x
<null>
Rows: 1

On the other hand, the following query does not return null since the Person node Charlie Sheen has one outgoing relationship.

MATCH (a:Person {name: 'Charlie Sheen'})
OPTIONAL MATCH (a)-->(x)
RETURN x

Table 3. Result

x
{"title":"Wall Street"}
Rows: 2

Properties on optional elements

If the existence of a property is optional, use the OPTIONAL MATCH clause. null will be returned if the specified property does not exist.

MATCH (a:Movie {title: 'Wall Street'})
OPTIONAL MATCH (a)-->(x)
RETURN x, x.name

Returns the element x (null in this query), and null for its name property, because the Movie node Wall Street has no outgoing relationships.

Table 4. Result

x	x.name
<null>	<null>
Rows: 1

The following query only returns null for the nodes which lack a name property.

MATCH (a:Person {name: 'Martin Sheen'})
OPTIONAL MATCH (a)-->(x)
RETURN x, x.name

Table 5. Result

x	x.name
{"title":"Wall Street"}	<null>
{"name":"Charlie Sheen"}	"Charlie Sheen"
{"title":"The American President"}	<null>
Rows: 3

Optional typed and named relationship

It is also possible to look for specific relationship types when using OPTIONAL MATCH:

MATCH (a:Movie {title: 'Wall Street'})
OPTIONAL MATCH (a)-[r:ACTED_IN]->()
RETURN a.title, r

This returns the title of the Movie node Wall Street, and since this node has no outgoing ACTED_IN relationships, null is returned for the relationship denoted by the variable r.

Table 6. Result

a.title	r
"Wall Street"	<null>
Rows: 1

However, the following query does not return null since it is looking for incoming relationships of the type ACTED_IN to the Movie node Wall Street.

MATCH (a:Movie {title: 'Wall Street'})
OPTIONAL MATCH (x)-[r:ACTED_IN]->(a)
RETURN a.title, x.name, type(r)

Table 7. Result

a.title	x.name	type(r)
"Wall Street"	"Michael Douglas"	"ACTED_IN"
"Wall Street"	"Martin Sheen"	"ACTED_IN"
"Wall Street"	"Charlie Sheen"	"ACTED_IN"
Rows: 3