WITH

Pipes results between query parts, enabling aggregation and filtering.

Category: query-language

Syntax

WITH [DISTINCT] <expr> [AS <alias>], ...
  [ORDER BY ...] [SKIP <n>] [LIMIT <n>]
  [WHERE <predicate>]

Description

## Overview WITH acts as a pipeline boundary between query parts in a Cypher query. It projects a defined set of expressions (with optional aliases) from the preceding clauses into the following clauses, effectively resetting variable scope. Any variable not included in the WITH projection is no longer accessible downstream. This makes WITH essential for multi-stage queries that alternate between MATCH, aggregation, and filtering. In DeltaForge, WITH operates as an intermediate materialization point in the query execution plan. The engine evaluates the preceding clauses, applies aggregation and filtering at the WITH boundary, and then feeds the resulting bindings into subsequent MATCH or RETURN clauses. This design enables efficient execution of complex graph analytics that combine traversal with aggregation. ## Behavior - WITH supports inline ORDER BY, SKIP, and LIMIT sub-clauses that are applied to the intermediate result before passing it downstream. This is parsed and executed as part of the WITH clause itself, not deferred to a trailing RETURN. - Aggregation functions (count, sum, avg, min, max, collect) in the WITH item list implicitly group by the non-aggregated expressions, following the same semantics as RETURN. - A WHERE clause after WITH filters the intermediate result, enabling post-aggregation filtering (equivalent to SQL HAVING). - WITH DISTINCT deduplicates the intermediate result set before downstream processing. ## Limitations - Variables not explicitly projected in WITH are permanently dropped from scope. There is no way to re-introduce them in later clauses without a new MATCH. - Deeply nested WITH chains (many sequential WITH boundaries) increase materialization overhead. Where possible, combine logic into fewer stages.

Parameters

Name	Type	Description
`items`		Specifies the expressions to carry forward into the next query part. Only variables explicitly listed (or aliased) are visible downstream; all others are dropped from scope. Supports aggregation functions, property access, and computed expressions.

Examples

-- Aggregate then filter: cities with more than 10 residents
USE my_zone.my_schema.my_graph
MATCH (n:Employee)
WITH n.city AS city, count(*) AS cnt
WHERE cnt > 10
RETURN city, cnt
ORDER BY cnt DESC;

-- Scope narrowing: carry forward only needed variables
USE my_zone.my_schema.my_graph
MATCH (a)-[r]->(b)
WITH a, count(DISTINCT b) AS out_degree
ORDER BY out_degree DESC
LIMIT 10
RETURN a.name AS hub, out_degree;

-- Chain MATCH clauses through WITH
USE my_zone.my_schema.my_graph
MATCH (a)-[:mentor]->(b)
WITH a, collect(b.name) AS mentees
MATCH (a)-[:colleague]->(c)
RETURN a.name AS mentor, mentees, count(c) AS colleague_count;

-- WITH DISTINCT for deduplication before next stage
USE my_zone.my_schema.my_graph
MATCH (a)-[*1..3]->(b)
WITH DISTINCT b
RETURN b.name AS reachable, b.department AS dept
ORDER BY b.name;

-- Inline ORDER BY, SKIP, and LIMIT inside WITH
USE my_zone.my_schema.my_graph
MATCH (n)
WITH n ORDER BY n.h_index DESC SKIP 5 LIMIT 10
RETURN n.name AS name, n.h_index AS h_index;

Pitfalls

Forgetting to include a variable in the WITH projection silently drops it from scope. Subsequent clauses that reference the missing variable will fail with an unresolved variable error.
Post-aggregation filtering via WITH ... WHERE applies after grouping. Writing the filter in a preceding WHERE (before aggregation) may produce different results and is usually more efficient.
WITH without aggregation or DISTINCT is still a scope boundary that materializes intermediate results. Unnecessary WITH clauses between MATCH stages add overhead without benefit.
Inline ORDER BY inside WITH sorts the intermediate result, not the final output. If the subsequent MATCH produces a different row order, the WITH-level sort has no effect on the final result unless followed directly by RETURN.