--- title: Composing clauses description: "relate, the embedded Cypher graph database (relatedb.ai): UNION, CALL subqueries with scope clauses and IN TRANSACTIONS, EXISTS, COUNT and COLLECT subqueries, USE, CALL procedure YIELD, NEXT and WHEN in relate." type: reference surfaces: - cypher status: stable verified: 0.1.2 verified_by: - cypher canonical_url: https://relatedb.ai/docs/reference/cypher/composing-clauses/ prerequisites: [] related: - reference/cypher/reading-clauses - reference/cypher/writing-clauses - reference/cypher/procedures - concepts/namespaces - concepts/transactions-and-concurrency - concepts/problems-relate-solves#dependency-analysis last_updated: 2026-11-08 --- # Composing clauses UNION, CALL subqueries with scope clauses and IN TRANSACTIONS, EXISTS, COUNT and COLLECT subqueries, USE, CALL procedure YIELD, NEXT and WHEN in relate. > Verified against relate 0.1.2. If your `relate --version` is newer, read https://relatedb.ai/docs/reference/changelog/#since-0.1.2 Composing clauses combine queries. `UNION` stacks the rows of two queries, a `CALL { … }` subquery runs a query once per input row, `EXISTS`, `COUNT` and `COLLECT` turn a subquery into a value, `USE` picks the [namespace](https://relatedb.ai/docs/reference/glossary/#namespace) a statement runs against, `CALL ` runs a built-in procedure, `NEXT` feeds one query's rows into the next, and `WHEN … THEN … ELSE` picks one query by condition. Every form on this page runs without a preview opt-in. ## Synopsis ```text UNION [DISTINCT | ALL] [UNION [DISTINCT | ALL] …] [OPTIONAL] CALL [(, … | * )] { } [IN [ CONCURRENT] TRANSACTIONS [OF ROWS] [ON ERROR CONTINUE | BREAK | FAIL | RETRY [FOR ] [THEN CONTINUE | BREAK | FAIL]] [REPORT STATUS AS ]] EXISTS { [WHERE ] | } COUNT { [WHERE ] | } COLLECT { } USE CALL ([, …]) [YIELD * | YIELD [AS ], … [WHERE ]] NEXT [NEXT …] WHEN THEN [WHEN THEN …] [ELSE ] ``` ## UNION | Form | Result | | ------------------------- | -------------------------------------------- | | `UNION`, `UNION DISTINCT` | The rows of every branch, duplicates removed | | `UNION ALL` | The rows of every branch, duplicates kept | | Rule | Behavior | | ------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------- | | Column names | Every branch returns the same column names; the order of the columns may differ, and rows are matched by name | | Mixing `UNION` and `UNION ALL` in one statement | `syntax error: InvalidClauseComposition: mixing UNION and UNION ALL` | | Different column names | `syntax error: DifferentColumnsInUnion: all UNION branches must have the same column names` | | `ORDER BY`, `SKIP`, `LIMIT` after the last branch | Apply to that branch only; an `ORDER BY` after a `CALL () { … }` that wraps the union sorts every row ([example](#examples)) | | `USE` | Each branch may start with `USE`, naming the same namespace | ## CALL subqueries A `CALL { … }` subquery runs once for each incoming row. The scope clause in parentheses names the outer variables the body can read. | Scope | Body can read | Notes | | ------------------- | ---------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `CALL (a, b) { … }` | `a` and `b` | | | `CALL (*) { … }` | Every variable in scope | | | `CALL () { … }` | No outer variable | Reading one fails with `syntax error: UndefinedVariable: variable '' not defined` | | `CALL { WITH a … }` | The variables the leading `WITH` imports | Deprecated. `EXPLAIN` reports notification `01N00`, ``The `CALL { … }` subquery has no variable scope clause.``, with the remedy ``Add the scope clause the body imports: `CALL (n) { … }`, or `CALL () { … }` when it imports nothing.`` | | Body | Rows after the subquery | | ----------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------- | | Ends in `RETURN` | One row per row the body returns, per input row; an input row for which the body returns nothing is dropped | | Ends in `RETURN`, under `OPTIONAL CALL` | One row per row the body returns, per input row; an input row for which the body returns nothing is kept, with the returned variables `null` | | No `RETURN` (a unit subquery, such as one that only writes) | The input rows, unchanged | | Rule | Behavior | | -------------------------------------------------- | ----------------------------------------------------------------------------------------- | | A returned name that an outer variable already has | ``syntax error: VariableAlreadyBound: Variable `` already declared in outer scope`` | | Aggregation in the body | Per input row: `CALL (p) { MATCH (p)-->(x) RETURN count(x) AS n }` counts for each `p` | | Nesting | A body can hold another `CALL` subquery | ### CALL IN TRANSACTIONS `IN TRANSACTIONS` commits the subquery's work in batches of input rows, each batch its own [transaction](https://relatedb.ai/docs/reference/glossary/#transaction). `ON ERROR …` and `REPORT STATUS AS …` may come in either order. Outside an explicit transaction, each batch commits on its own: a batch that committed stays committed when a later one fails. Inside an explicit transaction, the batches commit or roll back with that transaction. | Option | Default | Behavior | | ------------------------------------------ | ---------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `OF ROWS` | 1,000 rows | Input rows per batch | | `IN [] CONCURRENT TRANSACTIONS` | Off | Accepted; the batches run one after another | | `ON ERROR FAIL` | The default | The statement fails at the first failing batch; earlier batches stay committed | | `ON ERROR CONTINUE` | — | Skips the failing batch and runs the rest | | `ON ERROR BREAK` | — | Stops at the failing batch; the statement succeeds with what committed | | `ON ERROR RETRY [FOR ] [THEN …]` | Without `THEN`: `FAIL` | Accepted, but no batch is retried: the duration is ignored and the `THEN` fallback applies at once. Without `THEN`, the statement fails as under `ON ERROR FAIL` | | `REPORT STATUS AS ` | Off | Binds a map per input row: `started`, `committed`, `transactionId` and `errorMessage`. Needs `ON ERROR CONTINUE`, `BREAK`, or `RETRY … THEN CONTINUE` or `THEN BREAK`; otherwise `syntax error: InvalidReportStatusUsage: REPORT STATUS requires ON ERROR CONTINUE, BREAK, or RETRY with THEN CONTINUE or THEN BREAK` | A write before `CALL { … } IN TRANSACTIONS` in the same statement fails with `syntax error: WriteBeforeCallInTransactions: a write cannot precede CALL IN TRANSACTIONS`, and `LOAD CSV` counts as a write ([writing clauses](https://relatedb.ai/docs/reference/cypher/writing-clauses/#examples)). Every input row runs the subquery once and counts against the fixed budget of 100,000 nested evaluation passes per statement ([limitations](https://relatedb.ai/docs/reference/limitations/)). ## Subquery expressions | Form | Value | Body | | --------------- | ----------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------ | | `EXISTS { … }` | `true` when the body has at least one row | A pattern with an optional `WHERE`, or a query with `MATCH` and `RETURN` | | `COUNT { … }` | The number of rows the body returns | The same as `EXISTS` | | `COLLECT { … }` | A list of the body's single column | A query ending in `RETURN` of exactly one column; otherwise `syntax error: parse error: A COLLECT subquery must end with a single return column` | | Rule | Behavior | | ------------------- | ---------------------------------------------------------------------------------------- | | Outer variables | The body reads them without a scope clause | | Writes in the body | `syntax error: InvalidClauseComposition: writes are not allowed in subquery expressions` | | `UNION` in the body | Allowed, with the same column-name rule as a top-level `UNION` | ## USE A store has three fixed namespaces: `neo4j` holds the graph, `system` answers administration commands, and `system_ontologies` is listed but not queryable. A statement without `USE` runs against the session namespace, `neo4j` unless `--namespace` says otherwise ([namespaces](https://relatedb.ai/docs/concepts/namespaces/)). | Form | Result | | --------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `USE neo4j ` | Runs against the graph | | `USE system ` | Runs a procedure call or `SHOW DATABASES`; `SHOW INDEXES` and `SHOW CONSTRAINTS` fail with `syntax error: InvalidClauseComposition: NotAllowedOnSystemDatabase: SHOW INDEXES and SHOW CONSTRAINTS are per-database catalogs and are not available on the system database`, and a `MATCH` fails with `semantic error: SemanticError: The following unsupported clauses were used: MATCH.` followed by the clauses `system` supports | | `USE system_ontologies ` | `semantic error: SemanticError: This database is not queryable. Use the db.ontology.* procedures on the user database 'neo4j' instead.` | | `USE ` | `semantic error: DatabaseNotFound: Graph not found: ` | | `USE` in two `UNION` branches naming different namespaces | `semantic error: SemanticError: Multiple graphs in the same query not allowed here. This feature is only available on composite databases. Attempted to access graph ` | | `USE graph.byName(…)` | `semantic error: SemanticError: Dynamic graph lookup not allowed here. This feature is only available on composite databases. Attempted to access graph graph.byName()` | | `USE` after another clause | `syntax error: InvalidPlacementOfUseClause: USE clause must be either the first clause in a query, or preceded by an importing WITH clause` | | `CALL { USE neo4j … }` | Runs; `USE` may open a subquery without a scope clause | | `CALL () { USE neo4j … }` | `syntax error: UndefinedVariable: variable 'neo4j' not defined` | ## CALL procedure | Form | Result | | ----------------------------------------- | --------------------------------------------------------------------------------- | | `CALL db.labels()` as the whole statement | Every column the procedure yields | | `CALL db.labels() YIELD label` | The named columns | | `YIELD *` | Every column | | `YIELD label AS l` | The column renamed | | `YIELD … WHERE ` | The rows that pass the predicate | | Inside a larger query | Runs once per input row | | A column the procedure does not yield | ``syntax error: UnknownProcedureOutput: does not return `` `` | | A name that is not a procedure | `syntax error: ProcedureNotFound: ` | `SHOW PROCEDURES` lists the procedures with their signatures; the [procedures](https://relatedb.ai/docs/reference/cypher/procedures/) page describes them. ## NEXT and WHEN | Form | Result | Rule | | ------------------------------------------------ | ----------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | ` NEXT ` | The second query runs on the first one's returned rows, with its column names as variables | `NEXT` at the start fails: `syntax error: parse error: InvalidNextStatement: NEXT must follow a statement, not begin one` | | `WHEN THEN … [ELSE ]` | The query of the first branch whose predicate is `true`; no `true` branch and no `ELSE` gives no rows | Starts a statement, a subquery body or a query after `NEXT`. Every branch returns the same columns: `syntax error: ColumnNameConflict: conditional branches must return the same columns` | ## Examples The cells run in page order on one in-memory store, starting from the shared example graph: ```cypher run name=seed CREATE (ada:Person {name: 'Ada', role: 'engineer'}), (grace:Person {name: 'Grace', role: 'engineer'}), (noor:Person {name: 'Noor', role: 'maintainer'}), (relate:Project {name: 'relate', language: 'Rust'}), (tck:Project {name: 'opencypher-tck', language: 'Gherkin'}), (ada)-[:WORKS_ON {since: 2024}]->(relate), (grace)-[:WORKS_ON {since: 2023}]->(relate), (grace)-[:WORKS_ON {since: 2022}]->(tck), (noor)-[:MAINTAINS]->(relate), (ada)-[:KNOWS]->(grace) ``` ```table (no rows) · nodes created: 5, relationships created: 5, properties set: 13, labels added: 5 ``` `ORDER BY` after a `UNION` sorts the last branch only: ```cypher run name=union-last-branch MATCH (r:Project) RETURN r.name AS name UNION MATCH (p:Person) RETURN p.name AS name ORDER BY name DESC ``` ```table | name | |------------------| | "relate" | | "opencypher-tck" | | "Noor" | | "Grace" | | "Ada" | 5 rows ``` Wrapped in `CALL () { … }`, the sort covers every row: ```cypher run name=union-sorted CALL () { MATCH (r:Project) RETURN r.name AS name UNION MATCH (p:Person) RETURN p.name AS name } RETURN name ORDER BY name ``` ```table | name | |------------------| | "Ada" | | "Grace" | | "Noor" | | "opencypher-tck" | | "relate" | 5 rows ``` A scoped subquery counts per person; Noor, with no project, gets `0` because an aggregate always returns a row: ```cypher run name=call-scope MATCH (p:Person) CALL (p) { MATCH (p)-[:WORKS_ON]->(r:Project) RETURN count(r) AS projects } RETURN p.name AS name, projects ORDER BY name ``` ```table | name | projects | |---------|----------| | "Ada" | 1 | | "Grace" | 2 | | "Noor" | 0 | 3 rows ``` Without an aggregate, Noor's row is dropped, unless the call is `OPTIONAL`: ```cypher run name=optional-call MATCH (p:Person) OPTIONAL CALL (p) { MATCH (p)-[:WORKS_ON]->(r:Project) RETURN r.name AS project } RETURN p.name AS name, project ORDER BY name, project ``` ```table | name | project | |---------|------------------| | "Ada" | "relate" | | "Grace" | "opencypher-tck" | | "Grace" | "relate" | | "Noor" | null | 4 rows ``` Batches that report their status. This writes a `Row` node for each value except the one that divides by zero: ```cypher run name=in-transactions UNWIND [1, 0, 2] AS i CALL (i) { CREATE (:Row {inverse: 1 / i}) } IN TRANSACTIONS OF 1 ROWS ON ERROR CONTINUE REPORT STATUS AS s RETURN i, s.committed AS committed, s.errorMessage AS error ``` ```table | i | committed | error | |---|-----------|-------------| | 1 | true | null | | 0 | false | "/ by zero" | | 2 | true | null | 3 rows · nodes created: 2, properties set: 2, labels added: 2 ``` The three subquery expressions on one row per person: ```cypher run name=subquery-expressions MATCH (p:Person) RETURN p.name AS name, EXISTS { (p)-[:KNOWS]->() } AS knowsSomeone, COUNT { (p)-[:WORKS_ON]->() } AS projects, COLLECT { MATCH (p)-[:WORKS_ON]->(r) RETURN r.name ORDER BY r.name } AS projectNames ORDER BY name ``` ```table | name | knowsSomeone | projects | projectNames | |---------|--------------|----------|------------------------------| | "Ada" | true | 1 | ["relate"] | | "Grace" | false | 2 | ["opencypher-tck", "relate"] | | "Noor" | false | 0 | [] | 3 rows ``` ```cypher run name=collect-two-columns expect=error RETURN COLLECT { MATCH (p:Person) RETURN p.name, p.role } AS pairs ``` ```table error: syntax error: parse error: A COLLECT subquery must end with a single return column RETURN COLLECT { MATCH (p:Person) RETURN p.name, p.role } AS pairs ^ ``` `USE` names the namespace; `system` refuses a `MATCH`: ```cypher run name=use-neo4j USE neo4j MATCH (p:Person) RETURN count(p) AS people ``` ```table | people | |--------| | 3 | 1 row ``` ```cypher run name=use-system expect=error USE system MATCH (n) RETURN count(n) AS n ``` ```table error: semantic error: SemanticError: The following unsupported clauses were used: MATCH. The system database supports a restricted set of Cypher clauses. The supported clause structure for procedure calls is: CALL, YIELD, RETURN. YIELD and RETURN clauses are optional. The order of the clauses is fixed and each can only occur once. ``` A procedure call, with its column renamed and filtered: ```cypher run name=procedure CALL db.labels() YIELD label AS l WHERE l STARTS WITH 'Pro' RETURN l ``` ```table | l | |-----------| | "Project" | 1 row ``` `NEXT` passes the returned columns on, and `WHEN` picks a branch per row: ```cypher run name=next-when MATCH (p:Person) RETURN p.name AS name, p.role AS role NEXT WHEN role = 'maintainer' THEN RETURN name, 'reviews' AS duty ELSE RETURN name, 'builds' AS duty ``` ```table | name | duty | |---------|-----------| | "Noor" | "reviews" | | "Ada" | "builds" | | "Grace" | "builds" | 3 rows ``` ## See also - [Reading clauses](https://relatedb.ai/docs/reference/cypher/reading-clauses/): `MATCH`, `WITH`, `RETURN` and the rest of a single query. - [Writing clauses](https://relatedb.ai/docs/reference/cypher/writing-clauses/): what runs inside a writing subquery. - [Procedures](https://relatedb.ai/docs/reference/cypher/procedures/): the built-in procedures `CALL` runs. - [Namespaces](https://relatedb.ai/docs/concepts/namespaces/): the three namespaces `USE` can name. - [Transactions and concurrency](https://relatedb.ai/docs/concepts/transactions-and-concurrency/): how statements and explicit transactions commit. ## Terms used on this page - **namespace**: One of the three fixed graphs in every store: `neo4j` for user data, `system` for administration, and `system_ontologies`, listed but not queryable. - **transaction**: A unit of work that commits as a whole or not at all; each statement is its own transaction unless an explicit `begin` … `commit` groups several.