Composing clauses

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 namespacenamespace One of the three fixed graphs in every store: neo4j for user data, system for administration, and system_ontologies, listed but not queryable. Read more in the glossary a statement runs against, CALL <procedure> 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

<query> UNION [DISTINCT | ALL] <query> [UNION [DISTINCT | ALL] <query> …]

[OPTIONAL] CALL [(<variable>, … | * )] { <query> }
  [IN [<n> CONCURRENT] TRANSACTIONS [OF <n> ROWS]
    [ON ERROR CONTINUE | BREAK | FAIL | RETRY [FOR <duration>] [THEN CONTINUE | BREAK | FAIL]]
    [REPORT STATUS AS <variable>]]

EXISTS { <pattern> [WHERE <predicate>] | <query> }
COUNT { <pattern> [WHERE <predicate>] | <query> }
COLLECT { <query that returns one column> }

USE <namespace> <query>

CALL <procedure>([<argument>, …]) [YIELD * | YIELD <column> [AS <alias>], … [WHERE <predicate>]]

<query> NEXT <query> [NEXT <query> …]

WHEN <predicate> THEN <query> [WHEN <predicate> THEN <query> …] [ELSE <query>]

UNION

CALL subqueries

A CALL { … } subquery runs once for each incoming row. The scope clause in parentheses names the outer variables the body can read.

CALL IN TRANSACTIONS

IN TRANSACTIONS commits the subquery's work in batches of input rows, each batch its own transactiontransaction 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. Read more in the glossary. 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.

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). Every input row runs the subquery once and counts against the fixed budget of 100,000 nested evaluation passes per statement (limitations).

Subquery expressions

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).

CALL procedure

SHOW PROCEDURES lists the procedures with their signatures; the procedures page describes them.

NEXT and WHEN

Examples

The cells run in page order on one in-memory store, starting from the shared example graph:

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)

(no rows) · nodes created: 5, relationships created: 5, properties set: 13, labels added: 5

ORDER BY after a UNION sorts the last branch only:

MATCH (r:Project) RETURN r.name AS name
UNION
MATCH (p:Person) RETURN p.name AS name ORDER BY name DESC

| name             |
|------------------|
| "relate"         |
| "opencypher-tck" |
| "Noor"           |
| "Grace"          |
| "Ada"            |
5 rows

Wrapped in CALL () { … }, the sort covers every row:

CALL () {
  MATCH (r:Project) RETURN r.name AS name
  UNION
  MATCH (p:Person) RETURN p.name AS name
}
RETURN name ORDER BY name

| 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:

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

| name    | projects |
|---------|----------|
| "Ada"   | 1        |
| "Grace" | 2        |
| "Noor"  | 0        |
3 rows

Without an aggregate, Noor's row is dropped, unless the call is OPTIONAL:

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

| 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:

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

| 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:

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

| name    | knowsSomeone | projects | projectNames                 |
|---------|--------------|----------|------------------------------|
| "Ada"   | true         | 1        | ["relate"]                   |
| "Grace" | false        | 2        | ["opencypher-tck", "relate"] |
| "Noor"  | false        | 0        | []                           |
3 rows

RETURN COLLECT { MATCH (p:Person) RETURN p.name, p.role } AS pairs

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:

USE neo4j MATCH (p:Person) RETURN count(p) AS people

| people |
|--------|
| 3      |
1 row

USE system MATCH (n) RETURN count(n) AS n

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:

CALL db.labels() YIELD label AS l WHERE l STARTS WITH 'Pro'
RETURN l

| l         |
|-----------|
| "Project" |
1 row

NEXT passes the returned columns on, and WHEN picks a branch per row:

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

| name    | duty      |
|---------|-----------|
| "Noor"  | "reviews" |
| "Ada"   | "builds"  |
| "Grace" | "builds"  |
3 rows

See also

• Reading clauses: MATCH, WITH, RETURN and the rest of a single query.
• Writing clauses: what runs inside a writing subquery.
• Procedures: the built-in procedures CALL runs.
• Namespaces: the three namespaces USE can name.
• Transactions and concurrency: how statements and explicit transactions commit.