Chapter 04 · Advanced Pattern Matching: Variable Length, OPTIONAL MATCH, Paths, Quantified Patterns, and Shortest Paths

OPTIONAL MATCH and Null Introduction: Preserving Rows Without Accidentally Filtering Them Away

Preserve “no related entity” as an explicit result state by understanding where optional pattern qualification ends and row filtering begins.

Intermediate110–130 minutesOPTIONAL MATCH/null-scope labNeo4j 2026.07.1 Community · Cypher 25Last reviewed: September 2026

Learning outcomes

AtlasMart needs a list of suppliers even when no qualifying east-region store is reachable directly. That is a classic outer-join-shaped requirement: preserve the supplier row and use null when the optional graph pattern is absent. In Cypher, OPTIONAL MATCH provides that behavior—but predicate scope decides whether the row survives.

Explain OPTIONAL MATCH as row preservation plus null introduction for missing pattern bindings.

Place WHERE with the OPTIONAL MATCH whose pattern it constrains.

Distinguish pattern qualification from later row filtering through WITH/FILTER-like stages.

Prove optional behavior with known matched and unmatched AtlasMart rows.

Recognize when a predicate accidentally converts an optional requirement into a mandatory one.

Chapter 04 continuity contract

Continue Chapters 01–03 with Neo4j Community 2026.07.1, database neo4j, explicit CYPHER 25 in version-sensitive examples, local container atlasmart-neo4j, Bolt 127.0.0.1:7687, HTTP 127.0.0.1:7474, and constraint-backed AtlasMart domain identifiers. Chapter 04 adds a small synthetic operational handoff subgraph on existing Supplier/Category/Store concepts; it is intentionally isolated for path-mechanics exercises and does not replace the transactional relationships modeled earlier.

Version and execution note

Neo4j 2026.07.1 is the current 2026 release used by this course snapshot; 5.26.30 remains the current 5.26 LTS comparison line. The current manual covers Cypher 25; Cypher 5 is frozen. Quantified path patterns/relationships date from Neo4j 5.9, while explicit Cypher 25 path modes such as ACYCLIC arrived later and have version-sensitive combination rules. Commands here were checked against current documentation but could not be executed in this generation environment, so expected output is described by deterministic invariants rather than fabricated captures.

1. OPTIONAL MATCH preserves an incoming row when the pattern is absent

Cypher · idempotent cyclic AtlasMart handoff fixture

CYPHER 25CREATE CONSTRAINT supplier_id IF NOT EXISTS FOR (s:Supplier) REQUIRE s.supplierId IS UNIQUE;CREATE CONSTRAINT category_id IF NOT EXISTS FOR (c:Category) REQUIRE c.categoryId IS UNIQUE;CREATE CONSTRAINT store_id IF NOT EXISTS FOR (s:Store) REQUIRE s.storeId IS UNIQUE;CREATE CONSTRAINT ops_point_id IF NOT EXISTS FOR (n:OpsPoint) REQUIRE n.pointId IS UNIQUE;MERGE (s1:Supplier {supplierId:'SUP-3001'}) SET s1:OpsPoint, s1.pointId='OP-SUP-1', s1.name='Northwind Optics';MERGE (s2:Supplier {supplierId:'SUP-3002'}) SET s2:OpsPoint, s2.pointId='OP-SUP-2', s2.name='Audio Forge';MERGE (c1:Category {categoryId:'CAT-CAMERAS'}) SET c1:OpsPoint, c1.pointId='OP-CAT-CAM', c1.name='Cameras';MERGE (c2:Category {categoryId:'CAT-AUDIO'}) SET c2:OpsPoint, c2.pointId='OP-CAT-AUD', c2.name='Audio';MERGE (st1:Store {storeId:'ST-001'}) SET st1:OpsPoint, st1.pointId='OP-ST-1', st1.name='Central', st1.region='west';MERGE (st2:Store {storeId:'ST-002'}) SET st2:OpsPoint, st2.pointId='OP-ST-2', st2.name='Harbor', st2.region='east';MERGE (st3:Store {storeId:'ST-003'}) SET st3:OpsPoint, st3.pointId='OP-ST-3', st3.name='Airport', st3.region='north';MATCH (s1:OpsPoint {pointId:'OP-SUP-1'}), (s2:OpsPoint {pointId:'OP-SUP-2'}),      (c1:OpsPoint {pointId:'OP-CAT-CAM'}), (c2:OpsPoint {pointId:'OP-CAT-AUD'}),      (st1:OpsPoint {pointId:'OP-ST-1'}), (st2:OpsPoint {pointId:'OP-ST-2'}), (st3:OpsPoint {pointId:'OP-ST-3'})MERGE (s1)-[:HANDOFF_TO {routeId:'R01', minutes:30, active:true}]->(st1)MERGE (s1)-[:HANDOFF_TO {routeId:'R02', minutes:10, active:true}]->(c1)MERGE (c1)-[:HANDOFF_TO {routeId:'R03', minutes:8, active:true}]->(st1)MERGE (c1)-[:HANDOFF_TO {routeId:'R04', minutes:5, active:true}]->(c2)MERGE (st1)-[:HANDOFF_TO {routeId:'R05', minutes:12, active:true}]->(s2)MERGE (st1)-[:HANDOFF_TO {routeId:'R06', minutes:11, active:false}]->(c2)MERGE (s2)-[:HANDOFF_TO {routeId:'R07', minutes:9, active:true}]->(c2)MERGE (s2)-[:HANDOFF_TO {routeId:'R08', minutes:6, active:true}]->(st2)MERGE (c2)-[:HANDOFF_TO {routeId:'R09', minutes:7, active:true}]->(st2)MERGE (st2)-[:HANDOFF_TO {routeId:'R10', minutes:14, active:true}]->(s1)MERGE (st2)-[:HANDOFF_TO {routeId:'R11', minutes:4, active:true}]->(st3)MERGE (st3)-[:HANDOFF_TO {routeId:'R12', minutes:13, active:true}]->(c1);

Cypher · keep every supplier whether or not it has a direct east-store handoff

CYPHER 25MATCH (s:Supplier:OpsPoint)OPTIONAL MATCH (s)-[:HANDOFF_TO]->(st:Store:OpsPoint)WHERE st.region='east'RETURN s.supplierId,       st.storeId AS eastStoreORDER BY s.supplierId, eastStore;

The WHERE belongs to the preceding OPTIONAL MATCH. It constrains which optional store qualifies. A supplier with no qualifying direct east-store relationship remains as an output row with eastStore = null. This is not the same as matching every store and later deleting rows whose store is not east.

2. Moving the predicate changes the row contract

Cypher · deliberately wrong post-filter that removes null rows

CYPHER 25MATCH (s:Supplier:OpsPoint)OPTIONAL MATCH (s)-[:HANDOFF_TO]->(st:Store:OpsPoint)WITH s, stWHERE st.region='east'RETURN s.supplierId, st.storeId AS eastStoreORDER BY s.supplierId;

Once the optional pattern has produced a row with st = null, the expression st.region='east' evaluates to null. A later WHERE retains only true, so the null row disappears. The repair is to attach the predicate to the OPTIONAL MATCH when “no east store” is a valid result state.

Requirement	Correct placement	Reason
Preserve supplier; bind east store if present	`OPTIONAL MATCH ... WHERE st.region=...`	Predicate qualifies the optional pattern.
Return only suppliers that ended with an east-store binding	`WITH s,st WHERE st.region=...`	Post-filter intentionally removes null bindings.
Preserve supplier, then test presence explicitly	`RETURN st IS NULL AS missing`	Null becomes observable business state.

3. Optional expansion can still multiply rows

OPTIONAL MATCH does not mean “at most one.” If a supplier has three qualifying store relationships, one incoming supplier row becomes three output rows. If no store matches, it becomes one null-extended row. Cardinality tests must therefore cover zero, one and many matches.

Cypher · count zero/one/many optional matches

CYPHER 25MATCH (s:Supplier:OpsPoint)OPTIONAL MATCH (s)-[:HANDOFF_TO]->(st:Store:OpsPoint)RETURN s.supplierId,       count(st) AS directStoreCount,       collect(st.storeId) AS storesORDER BY s.supplierId;

count(st) ignores null, so a supplier with no direct store binding reports zero. The outer supplier row still existed before aggregation.

4. Optional multi-hop patterns need the same bounds as mandatory ones

Cypher · bounded optional reachability

CYPHER 25MATCH (s:Supplier:OpsPoint)OPTIONAL MATCH p=(s)-[:HANDOFF_TO]->{1,3}(st:Store:OpsPoint)WHERE st.region=$regionRETURN s.supplierId,       st.storeId,       length(p) AS hopsORDER BY s.supplierId, hops, st.storeId;

Optionality changes what happens when no path matches; it does not eliminate path expansion cost when many paths do match. A dense supplier can still generate many optional path rows.

Lab: prove row preservation before optimizing

Cypher · side-by-side acceptance query

CYPHER 25MATCH (s:Supplier:OpsPoint)OPTIONAL MATCH (s)-[:HANDOFF_TO]->(st:Store:OpsPoint)WHERE st.region=$regionRETURN 'pattern-qualified' AS variant, s.supplierId, st.storeId AS storeUNION ALLMATCH (s:Supplier:OpsPoint)OPTIONAL MATCH (s)-[:HANDOFF_TO]->(st:Store:OpsPoint)WITH s, stWHERE st.region=$regionRETURN 'post-filtered' AS variant, s.supplierId, st.storeId AS storeORDER BY variant, supplierId;

Run with $region='east' and compare which suppliers survive each variant. The difference is the lesson: the first query preserves outer supplier rows; the second intentionally does not.

Production judgment: define whether absence is a valid state in the API contract, then test that state explicitly. Do not “fix” nulls with coalesce() until you know whether null means absent relationship, missing property, or an application error. Next, we inspect path values themselves and make cycles/uniqueness visible.

Check your understanding

What does OPTIONAL MATCH output when the optional pattern has no match?
Why can WHERE immediately after OPTIONAL MATCH preserve the outer row?
Why can WITH ... WHERE remove that same row?
Can OPTIONAL MATCH still produce multiple rows per input row?
Does adding OPTIONAL make a variable-length traversal cheap?

Review the answers

1. It preserves the incoming row and binds variables introduced only by the optional pattern to null.

2. That WHERE is part of the optional pattern qualification; a failed optional match becomes null bindings rather than deleting the outer row.

3. The later WHERE filters rows, and predicates on null are not true.

4. Yes; zero matches gives one null-extended row, while many matches produce many rows.

5. No. Optionality changes missing-match semantics, not fan-out or traversal work.

Summary and next step

OPTIONAL MATCH and Null Introduction: Preserving Rows Without Accidentally Filtering Them Away is useful only when its assumptions and observed evidence stay attached to the decision. The examples above establish a reproducible mechanism and boundary; they do not turn one lab result into a universal production rule.

Next, continue to Path Values, Nodes/Relationships Functions, Path Predicates, Uniqueness, and Cycle Awareness. Carry forward the verified assumptions, fixture state, version/edition boundaries, and measurements from this lesson instead of treating the next topic as an isolated recipe.

Authoritative references

Current Neo4j versions — Official current-release and 5.26 LTS patch snapshot.
Cypher Manual introduction — Current Cypher 25 baseline and Cypher 5 compatibility framing.
Patterns — Current graph/path matching overview, including shortest paths and match/path modes.
Variable-length paths — Quantified path patterns, quantified relationships, group variables and inline predicates.
Variable-length path reference — Formal syntax and rules for quantified and legacy variable-length patterns.
Path-pattern reference — Path values, path-pattern composition and matching rules.
Unique relationship paths — Default relationship-uniqueness behavior and DIFFERENT RELATIONSHIPS semantics.
Match modes and path modes — Current Cypher 25 WALK/TRAIL/ACYCLIC and match-mode compatibility rules.
OPTIONAL MATCH — Outer-row preservation and null introduction when a pattern is absent.
WHERE — WHERE as a subclause of MATCH/OPTIONAL MATCH and its pattern-scoping consequences.
Path functions — nodes(), relationships(), length()/path_length() and path-related list/predicate functions.
Shortest paths — Current SHORTEST/ALL SHORTEST path selector semantics.
Query plans and operators — Execution-plan operators and row/db-hit/memory evidence.
GDS graph algorithms — Graph Data Science algorithm families including path finding.
GDS Dijkstra source-target — Weighted positive-edge shortest-path algorithm for projected GDS graphs.

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only

0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only Ethereum or ERC-20 compatible assets to this address.