CALL Subqueries, EXISTS Subqueries, Scope Import/Export, and Avoiding Accidental Cartesian Products

Learning outcomes

AtlasMart needs one customer row enriched with independent statistics and a boolean “has purchased this product” flag. A monolithic pattern can duplicate rows across orders and products. Subqueries provide composition boundaries, but only if import/export scope and returned cardinality are explicit.

Re-establish the deterministic AtlasMart read fixture

Every lesson can stand alone. The fixture is idempotent, so rerunning it is the safest reset: it restores four customers, five orders, four products, two categories, five PLACED, seven CONTAINS, and four IN_CATEGORY relationships without deleting the Chapter 04 operations graph. If your course sandbox already contains the fixture, this simply normalizes the properties used here.

CYPHER 25CREATE CONSTRAINT customer_id IF NOT EXISTS FOR (c:Customer) REQUIRE c.customerId IS UNIQUE;CREATE CONSTRAINT product_id IF NOT EXISTS FOR (p:Product) REQUIRE p.productId IS UNIQUE;CREATE CONSTRAINT order_id IF NOT EXISTS FOR (o:Order) REQUIRE o.orderId IS UNIQUE;CREATE CONSTRAINT category_id IF NOT EXISTS FOR (c:Category) REQUIRE c.categoryId IS UNIQUE;MERGE (c1:Customer {customerId:'C-1001'}) SET c1.name='Ava Chen', c1.tier='gold', c1.region='eu'MERGE (c2:Customer {customerId:'C-1002'}) SET c2.name='Noah Smith', c2.tier='silver', c2.region='us'MERGE (c3:Customer {customerId:'C-1003'}) SET c3.name='Mina Rahimi', c3.tier='gold', c3.region='me'MERGE (c4:Customer {customerId:'C-1004'}) SET c4.name='Leo Martin', c4.region='eu'MERGE (cat1:Category {categoryId:'CAT-CAMERAS'}) SET cat1.name='Cameras'MERGE (cat2:Category {categoryId:'CAT-AUDIO'}) SET cat2.name='Audio'MERGE (p1:Product {productId:'P-1001'}) SET p1.name='Trail Camera', p1.price=129.90, p1.rating=4.7MERGE (p2:Product {productId:'P-1002'}) SET p2.name='Studio Headphones', p2.price=89.00, p2.rating=4.7MERGE (p3:Product {productId:'P-1003'}) SET p3.name='Action Camera', p3.price=219.00, p3.rating=4.5MERGE (p4:Product {productId:'P-1004'}) SET p4.name='USB Microphone', p4.price=75.00MERGE (p1)-[:IN_CATEGORY]->(cat1)MERGE (p3)-[:IN_CATEGORY]->(cat1)MERGE (p2)-[:IN_CATEGORY]->(cat2)MERGE (p4)-[:IN_CATEGORY]->(cat2)MERGE (o1:Order {orderId:'O-2001'}) SET o1.placedAt=datetime('2026-08-01T09:00:00Z'), o1.status='paid', o1.total=218.90MERGE (o2:Order {orderId:'O-2002'}) SET o2.placedAt=datetime('2026-08-02T10:30:00Z'), o2.status='paid', o2.total=219.00MERGE (o3:Order {orderId:'O-2003'}) SET o3.placedAt=datetime('2026-08-03T12:00:00Z'), o3.status='shipped', o3.total=129.90MERGE (o4:Order {orderId:'O-2004'}) SET o4.placedAt=datetime('2026-08-04T14:15:00Z'), o4.status='paid', o4.total=164.00MERGE (o5:Order {orderId:'O-2005'}) SET o5.placedAt=datetime('2026-08-04T14:15:00Z'), o5.status='paid', o5.total=75.00MERGE (c1)-[:PLACED]->(o1)MERGE (c2)-[:PLACED]->(o2)MERGE (c1)-[:PLACED]->(o3)MERGE (c3)-[:PLACED]->(o4)MERGE (c4)-[:PLACED]->(o5)MERGE (o1)-[:CONTAINS {quantity:1}]->(p1)MERGE (o1)-[:CONTAINS {quantity:1}]->(p2)MERGE (o2)-[:CONTAINS {quantity:1}]->(p3)MERGE (o3)-[:CONTAINS {quantity:1}]->(p1)MERGE (o4)-[:CONTAINS {quantity:1}]->(p2)MERGE (o4)-[:CONTAINS {quantity:1}]->(p4)MERGE (o5)-[:CONTAINS {quantity:1}]->(p4);

Before analytical work, verify the relevant slice rather than assuming the whole database contains only these entities:

CYPHER 25MATCH (c:Customer) WITH count(c) AS customersMATCH (o:Order) WITH customers, count(o) AS ordersMATCH (p:Product) WITH customers, orders, count(p) AS productsMATCH (:Customer)-[placed:PLACED]->(:Order)WITH customers, orders, products, count(placed) AS placedMATCH (:Order)-[line:CONTAINS]->(:Product)RETURN customers, orders, products, placed, count(line) AS contains;

For the fixture above the invariant is 4 / 5 / 4 / 5 / 7. If Chapter 04 remains loaded, the total database node/relationship counts will be larger; label- and type-specific counts are the contract for this chapter.

1. CALL creates a nested query scope

A returning CALL subquery runs once for each incoming outer row. Variables from the outer query are not implicitly available in a modern CALL subquery; import them with the variable scope clause. The older importing-WITH form is deprecated, so this chapter uses the scope clause introduced in Neo4j 5.23.

CYPHER 25MATCH (c:Customer)CALL (c) {  MATCH (c)-[:PLACED]->(o:Order)  RETURN count(o) AS orderCount,         round(sum(o.total), 2) AS gross}RETURN c.customerId AS customerId, orderCount, grossORDER BY customerId;

The outer MATCH produces four customer rows. Because the subquery contains aggregation with no grouping key, it returns one summary row for each incoming customer—even if a customer had zero orders. The outer cardinality therefore remains four.

2. Import only the variables the subquery needs

CALL (c) imports one variable. CALL () imports none. CALL (*) imports all outer variables. Importing everything is convenient but enlarges the mental scope and can make accidental correlations harder to notice.

CYPHER 25MATCH (c:Customer)CALL () {  MATCH (p:Product)  RETURN count(p) AS catalogProducts}RETURN c.customerId, catalogProductsORDER BY c.customerId;

The product count is independent of c, so importing the customer would communicate a correlation that does not exist.

3. EXISTS asks a boolean question without exporting inner rows

An EXISTS subquery is an expression. Outer variables can be referenced directly without a CALL-style import clause, variables created inside do not escape, and evaluation only needs to establish whether at least one row exists.

CYPHER 25MATCH (c:Customer)WHERE EXISTS {  MATCH (c)-[:PLACED]->(:Order)-[:CONTAINS]->(:Product {productId:$productId})}RETURN c.customerId AS customerId, c.name AS nameORDER BY customerId;

This is often a better contract than matching every purchase path and then applying DISTINCT merely to recover one customer row.

4. Returning subqueries can multiply rows

If a CALL subquery returns several rows for one incoming row, each result row is joined back to that incoming row. That may be intended, or it may recreate the expansion the subquery was supposed to control.

CYPHER 25MATCH (c:Customer)CALL (c) {  MATCH (p:Product)  RETURN p}RETURN c.customerId, p.productId;

The subquery does not use c but returns four product rows, so each of four customers is paired with each product: sixteen rows in this fixture. The repair depends on intent. For a catalog count, import nothing and aggregate to one row. For “customer bought product,” use a connected pattern or EXISTS.

5. Scope errors are correctness errors, not style issues

CYPHER 25MATCH (c:Customer)CALL (c) {  MATCH (c)-[:PLACED]->(o:Order)  RETURN count(o) AS orderCount, sum(o.total) AS gross}CALL (c) {  MATCH (c)-[:PLACED]->(:Order)-[:CONTAINS]->(p:Product)  RETURN count(DISTINCT p) AS distinctProducts}RETURN c.customerId, orderCount, gross, distinctProductsORDER BY c.customerId;

Each subquery states a separate correlation and collapses its own expansion before returning. This is not automatically faster than one query; it is easier to reason about. Use EXPLAIN/PROFILE on representative data before making performance claims.

Lab: prove import/export and row survival

Start with four customer rows. Add a CALL (c) aggregate and verify four rows remain. Replace the aggregate with a subquery that returns one product row per purchase and observe multiplication. Then rewrite the boolean question with EXISTS and verify that the final row grain returns to customers.

CYPHER 25// A. one row per customerMATCH (c:Customer)CALL (c) { MATCH (c)-[:PLACED]->(o:Order) RETURN count(o) AS n }RETURN count(*) AS outerRows;// B. boolean existence without exporting purchase rowsMATCH (c:Customer)WHERE EXISTS { MATCH (c)-[:PLACED]->(:Order)-[:CONTAINS]->(:Product {productId:'P-1001'}) }RETURN count(*) AS matchingCustomers;

Do not compare plan memory or latency using only this tiny graph; it is a semantic fixture. For performance engineering, scale the fixture deliberately and disclose graph density, indexes, result cardinality and warmup state.

Summary and next step

Subqueries are scope and cardinality boundaries, not decorative nesting. Import intentionally, aggregate before exporting when the contract wants one row, and use EXISTS when the question is boolean. The final lesson composes these mechanisms into an explainable customer analytics query.

Authoritative references

• Current Neo4j versions — Release/LTS snapshot used to pin the course baseline.
• Cypher Manual — Introduction — Cypher 25 status and language-version policy.
• Select Cypher version — Database/default and per-query Cypher version behavior.
• CALL subqueries — Execution-per-input-row, scope clause, return cardinality and current deprecation guidance.
• EXISTS subqueries — Existence semantics and outer-variable visibility.
• Variables — Query-part and subquery variable scope.