OPTIONAL MATCH

Like MATCH, but returns NULLs for missing parts instead of filtering rows.

Category: query-language

Syntax

OPTIONAL MATCH <pattern> [WHERE <predicate>]

Description

## Overview OPTIONAL MATCH performs pattern matching with left-outer-join semantics. If the pattern is found, it binds the matched nodes and relationships just like a regular MATCH. If the pattern is not found for a given set of input bindings, the variables introduced by the OPTIONAL MATCH are set to NULL rather than eliminating the row. This preserves all rows from preceding clauses. In DeltaForge, OPTIONAL MATCH is executed by first evaluating the pattern against the CSR graph and then performing an outer join with the existing binding table. The graph data originates from Delta tables registered as graph sources, and property access on NULL bindings safely returns NULL. ## Behavior - OPTIONAL MATCH preserves every row from the preceding clause. Rows that find no match in the optional pattern receive NULL for all variables introduced by that pattern. - A WHERE clause attached to OPTIONAL MATCH filters only the optional matches. Rows that fail the WHERE predicate revert to NULL bindings (they are not removed from the result). - Multiple OPTIONAL MATCH clauses can be chained to probe different relationship types independently. Each operates on the full set of input bindings. - Aggregation functions (count, collect) treat NULL bindings correctly: count(r) returns 0 when no match exists, and collect() produces an empty list. ## Limitations - OPTIONAL MATCH on variable-length paths with wide ranges can produce large intermediate results even when most paths are absent, because the engine must attempt traversal for every input binding. - Nested OPTIONAL MATCH patterns (OPTIONAL MATCH inside a CALL subquery that is itself optional) may produce complex NULL propagation. Test with small datasets first.

Parameters

Name	Type	Description
`pattern`		Specifies the node and relationship pattern to optionally match. Uses the same pattern syntax as MATCH, including node labels, relationship types, and variable-length paths.

Examples

-- Left-outer-join style: include employees even if they have no mentor
USE my_zone.my_schema.my_graph
MATCH (e:Employee)
OPTIONAL MATCH (e)-[:mentor]->(m)
RETURN e.name AS employee, m.name AS mentor;

-- Count optional relationships per node
USE my_zone.my_schema.my_graph
MATCH (n)
OPTIONAL MATCH (n)-[r]->()
RETURN n.name AS name, count(r) AS out_degree
ORDER BY out_degree DESC;

-- Chain OPTIONAL MATCH to probe multiple relationship types
USE my_zone.my_schema.my_graph
MATCH (p:Employee)
OPTIONAL MATCH (p)-[:mentor]->(mentee)
OPTIONAL MATCH (p)-[:colleague]->(peer)
RETURN p.name AS person,
       count(DISTINCT mentee) AS mentees,
       count(DISTINCT peer) AS peers;

-- OPTIONAL MATCH with WHERE filter on the optional part
USE my_zone.my_schema.my_graph
MATCH (a:Employee)
OPTIONAL MATCH (a)-[r]->(b)
WHERE r.weight > 0.8
RETURN a.name AS source, b.name AS strong_contact, r.weight AS strength
ORDER BY a.name;

Pitfalls

Placing a WHERE clause after OPTIONAL MATCH but intending it to filter the outer rows converts the optional match into an inner match. Move the filter to a subsequent WITH ... WHERE to preserve outer-join semantics.
Aggregating over OPTIONAL MATCH results without accounting for NULLs can produce misleading counts. Use count(r) (not count(*)) to count only actual matches, since count(*) counts all rows including those with NULL bindings.
OPTIONAL MATCH (a)-[r]->(b) followed by RETURN b.name returns NULL for the name when no match exists, not an empty string. Downstream consumers must handle NULL values explicitly.