3.16. CALL {} (subquery)

The CALL {} clause evaluates a subquery that returns some values.

3.16.1. Introduction

CALL allows to execute subqueries, i.e. queries inside of other queries. Subqueries allow you to compose queries, which is especially useful when working with UNION or aggregations.

The CALL clause is also used for calling procedures. For descriptions of the CALL clause in this context, refer to Section 3.17, “CALL procedure”.

A subquery is evaluated for each incoming input row and may produce an arbitrary number of output rows. Every output row is then combined with the input row to build the result of the subquery. That means that a subquery will influence the number of rows. If the subquery does not return any rows, there will be no rows available after the subquery.

There are restrictions on what queries are allowed as subqueries and how they interact with the enclosing query:

  • A subquery must end with a RETURN clause.
  • A subquery cannot refer to variables from the enclosing query.
  • A subquery cannot return variables with the same names as variables in the enclosing query.
  • All variables that are returned from a subquery are afterwards available in the enclosing query.

The following graph is used for the examples below:

Figure 3.16. Graph
alt

3.16.2. Post-union processing

Subqueries can be used to process the results of a UNION query further. This example query finds the youngest and the oldest person in the database and orders them by name.

Query. 

CALL {
  MATCH (p:Person) RETURN p ORDER BY p.age ASC LIMIT 1
  UNION
  MATCH (p:Person) RETURN p ORDER BY p.age DESC LIMIT 1
}
RETURN p.name, p.age ORDER BY p.name

Table 3.126. Result
p.name p.age

2 rows

"Alice"

20

"Charlie"

65

Try this query live.  CREATE (a:Person:Child {age: 20, name: 'Alice'}), (b:Person {age: 27, name: 'Bob'}), (c:Person:Parent {age: 65, name: 'Charlie'}), (d:Person {age: 30, name: 'Dora'}) CREATE (a)-[:FRIEND_OF]->(b) CREATE (a)-[:CHILD_OF]->(c) CALL { MATCH (p:Person) RETURN p ORDER BY p.age ASC LIMIT 1 UNION MATCH (p:Person) RETURN p ORDER BY p.age DESC LIMIT 1 } RETURN p.name, p.age ORDER BY p.name

If different parts of a result should be matched differently, with some aggregation over the whole results, subqueries need to be used. This example query finds all persons with friends in one part of the union and all children with parents in the other part. Subsequently the number of friends and parents is counted together.

Query. 

CALL {
  MATCH (p:Person)-[:FRIEND_OF]->(other:Person) RETURN p, other
  UNION
  MATCH (p:Child)-[:CHILD_OF]->(other:Parent) RETURN p, other
}
RETURN DISTINCT p.name, count(other)

Table 3.127. Result
p.name count(other)

1 row

"Alice"

2

Try this query live.  CREATE (a:Person:Child {age: 20, name: 'Alice'}), (b:Person {age: 27, name: 'Bob'}), (c:Person:Parent {age: 65, name: 'Charlie'}), (d:Person {age: 30, name: 'Dora'}) CREATE (a)-[:FRIEND_OF]->(b) CREATE (a)-[:CHILD_OF]->(c) CALL { MATCH (p:Person)-[:FRIEND_OF]->(other:Person) RETURN p, other UNION MATCH (p:Child)-[:CHILD_OF]->(other:Parent) RETURN p, other } RETURN DISTINCT p.name, count(other)

3.16.3. Aggregation and side-effects

Subqueries can be useful to do aggregations for each row and to isolate side-effects. This example query creates five Clone nodes for each existing person. The aggregation ensures that cardinality is not changed by the subquery. Without this, the result would be five times as many rows.

Query. 

MATCH (p:Person)
CALL {
  UNWIND range(1, 5) AS i
  CREATE (c:Clone)
  RETURN count(c) AS numberOfClones
}
RETURN p.name, numberOfClones

Table 3.128. Result
p.name numberOfClones

4 rows

"Alice"

5

"Bob"

5

"Charlie"

5

"Dora"

5

Try this query live.  CREATE (a:Person:Child {age: 20, name: 'Alice'}), (b:Person {age: 27, name: 'Bob'}), (c:Person:Parent {age: 65, name: 'Charlie'}), (d:Person {age: 30, name: 'Dora'}) CREATE (a)-[:FRIEND_OF]->(b) CREATE (a)-[:CHILD_OF]->(c) MATCH (p:Person) CALL { UNWIND range(1, 5) AS i CREATE (c:Clone) RETURN count(c) AS numberOfClones } RETURN p.name, numberOfClones

3.16.4. Correlated subqueries

This functionality is currently only available in Neo4j Fabric. Find out more about this feature in Operations Manual → Fabric.

A correlated subquery is a subquery that uses variables defined outside of the CALL clause. To be able to use a variable in this way, the variable must be explicitly imported into the subquery.

3.16.4.1. Importing variables into subqueries

Variables are imported into a subquery using an importing WITH clause. As the subquery is evaluated for each incoming input row, the imported variables get bound to the corresponding values from the input row in each evaluation.

Query. 

UNWIND [0, 1, 2] AS x
CALL {
  WITH x
  RETURN x*10 AS y
}
RETURN x, y

Table 3.129. Result
x y

3 rows

0

0

1

10

2

20

An importing WITH clause must:

  • Consist only of simple references to outside variables - e.g. WITH x, y, z. Aliasing or expressions are not supported in importing WITH clauses - e.g. WITH a AS b or WITH a+1 AS b.
  • Be the first clause of a subquery (or the second clause, if directly following a USE clause).

3.16.4.2. Aggregation on imported variables

Aggregations in subqueries are scoped to the subquery evaluation, also for imported variables, as shown in the following example:

Query. 

UNWIND [0, 1, 2] AS x
CALL {
  WITH x
  RETURN max(x) AS xMax
}
RETURN x, xMax

Table 3.130. Result
x xMax

3 rows

0

0

1

1

2

2

The aggregation max(x) observes only a single value of x in each evaluation of the subquery, and thus simply evaluates to that same value.