# Subquery expressions

A subquery expression runs a query body once for every row of the enclosing query
and reduces the rows that body produces to a single value. Memgraph supports three
of them, which differ only in what they reduce to:

| Expression      | Returns   | Body matched nothing |
| --------------- | --------- | -------------------- |
| `EXISTS { … }`  | Boolean   | `false`              |
| `COUNT { … }`   | Integer   | `0`                  |
| `COLLECT { … }` | List      | `[]`                 |

None of them ever returns `null`, so the result is always safe to compare, sort or
aggregate on.

A subquery body can reference variables of the enclosing query, which makes it
*correlated* — it is re-evaluated per row against the values that row holds.
Variables the body introduces stay inside the body.

## Dataset

The examples on this page run against a small graph of `Person` nodes joined by `KNOWS`, and `Movie`
nodes they are joined to by `ACTED_IN`. Only some of the people have a `nickname`. You can create it locally by executing the queries at the end of
the page: [Dataset queries](#dataset-queries).

## The three side by side

The same body, reduced three ways. Carol and Dave match nothing, so each
expression falls back to its empty value rather than to `null`:

```cypher
MATCH (p:Person)
RETURN p.name AS name,
       EXISTS  { MATCH (p)-[:ACTED_IN]->(m) }                AS acted,
       COUNT   { MATCH (p)-[:ACTED_IN]->(m) }                AS credits,
       COLLECT { MATCH (p)-[:ACTED_IN]->(m) RETURN m.title } AS titles
ORDER BY name;
```

Output:

```nocopy
+---------+-------+---------+----------------------------------------------+
| name    | acted | credits | titles                                       |
+---------+-------+---------+----------------------------------------------+
| "Alice" | true  | 3       | ["The Matrix", "Johnny Mnemonic", "Jumanji"] |
| "Bob"   | true  | 1       | ["Jumanji"]                                  |
| "Carol" | false | 0       | []                                           |
| "Dave"  | false | 0       | []                                           |
+---------+-------+---------+----------------------------------------------+
```

## EXISTS

`EXISTS { … }` is `true` when its body produces at least one row. Over a single
pattern it can be written as a [bare pattern](#the-bare-pattern-shorthand). With
`NOT` it filters for the absence of a match:

```cypher
MATCH (p:Person)
WHERE NOT EXISTS { MATCH (p)-[:KNOWS]->() }
RETURN p.name AS name
ORDER BY name;
```

Output:

```nocopy
+---------+
| name    |
+---------+
| "Carol" |
| "Dave"  |
+---------+
```

### EXISTS versus a pattern expression

The [`exists(pattern)` function](https://memgraph.com/docs/querying/functions#pattern-functions) and a bare
pattern filter such as `MATCH (n) WHERE (n)-->()` are convenient for a simple
existence check, but a pattern on its own cannot carry additional clauses. Reach
for `EXISTS { … }` when the check needs a `WHERE`, a `WITH` with an aggregation,
or more than one pattern part — as in [supported clauses](#supported-clauses).

For `EXISTS` as a filter in the `WHERE` clause, see also [Existential
subqueries](https://memgraph.com/docs/querying/clauses/where#4-existential-subqueries).

## COUNT

`COUNT { … }` returns the number of rows its body produces, and over a single
pattern it can be written as a [bare pattern](#the-bare-pattern-shorthand).
Because the result is an integer, it can be compared directly in a `WHERE`:

```cypher
MATCH (p:Person)
WHERE COUNT { MATCH (p)-[:KNOWS]->(f) } > 1
RETURN p.name AS name
ORDER BY name;
```

Output:

```nocopy
+---------+
| name    |
+---------+
| "Alice" |
+---------+
```

`DISTINCT` in the body counts distinct rows. Alice acted in three movies, but
they were released in two distinct years:

```cypher
MATCH (p:Person)
RETURN p.name AS name,
       COUNT { MATCH (p)-[:ACTED_IN]->(m) RETURN DISTINCT m.released } AS years
ORDER BY name;
```

Output:

```nocopy
+---------+-------+
| name    | years |
+---------+-------+
| "Alice" | 2     |
| "Bob"   | 1     |
| "Carol" | 0     |
| "Dave"  | 0     |
+---------+-------+
```

Prefer `COUNT { … }` to counting a [pattern
comprehension](https://memgraph.com/docs/querying/expressions#pattern-comprehension) with
`size([(p)-[:KNOWS]->(f) | f])`, which builds the whole list before measuring it.

## COLLECT

`COLLECT { … }` returns the body's single return column as a list, in the order
the body produced its rows. Its body must end in a `RETURN` of exactly one
column.

Because the body's own order becomes the list's order, `ORDER BY` inside the body
sorts the list:

```cypher
MATCH (p:Person)
RETURN p.name AS name,
       COLLECT { MATCH (p)-[:ACTED_IN]->(m) RETURN m.title ORDER BY m.title } AS titles
ORDER BY name;
```

Output:

```nocopy
+---------+----------------------------------------------+
| name    | titles                                       |
+---------+----------------------------------------------+
| "Alice" | ["Johnny Mnemonic", "Jumanji", "The Matrix"] |
| "Bob"   | ["Jumanji"]                                  |
| "Carol" | []                                           |
| "Dave"  | []                                           |
+---------+----------------------------------------------+
```

`SKIP` and `LIMIT` apply too, which is how you pick the top *n* per row:

```cypher
MATCH (p:Person)
RETURN p.name AS name,
       COLLECT {
         MATCH (p)-[:ACTED_IN]->(m)
         RETURN m.title ORDER BY m.title LIMIT 1
       } AS first
ORDER BY name;
```

Output:

```nocopy
+---------+---------------------+
| name    | first               |
+---------+---------------------+
| "Alice" | ["Johnny Mnemonic"] |
| "Bob"   | ["Jumanji"]         |
| "Carol" | []                  |
| "Dave"  | []                  |
+---------+---------------------+
```

### COLLECT compared with collect()

`COLLECT { … }` gathers every row its body returns, including the rows whose
value is `null`. The [`collect()` aggregation
function](https://memgraph.com/docs/querying/functions#aggregation-functions) drops them. Two of the four
people have a `nickname`:

```cypher
MATCH (p:Person)
RETURN collect(p.nickname) AS nicknames;
```

Output:

```nocopy
+-----------------+
| nicknames       |
+-----------------+
| ["Al", "Bobby"] |
+-----------------+
```

```cypher
RETURN COLLECT { MATCH (p:Person) RETURN p.nickname ORDER BY p.name } AS nicknames;
```

Output:

```nocopy
+-----------------------------+
| nicknames                   |
+-----------------------------+
| ["Al", "Bobby", Null, Null] |
+-----------------------------+
```

Filter in the body to leave them out:

```cypher
RETURN COLLECT {
         MATCH (p:Person) WHERE p.nickname IS NOT NULL
         RETURN p.nickname ORDER BY p.name
       } AS nicknames;
```

Output:

```nocopy
+-----------------+
| nicknames       |
+-----------------+
| ["Al", "Bobby"] |
+-----------------+
```

The `COLLECT` keyword does not reserve the name: `collect` remains usable as a
variable, alias, property key and label, and `collect(x)` remains the [list
aggregation function](https://memgraph.com/docs/querying/functions#aggregation-functions).

## Where you can use a subquery expression

`EXISTS { … }`, `COUNT { … }` and `COLLECT { … }` are accepted in the same
positions:

- a `WITH` or `RETURN` projection
- a `WHERE`, whether it hangs off `MATCH`, `OPTIONAL MATCH` or `WITH`
- an `ORDER BY`
- a [`CASE` expression](https://memgraph.com/docs/querying/expressions#case)
- an argument of an [aggregation function](https://memgraph.com/docs/querying/functions#aggregation-functions)

Ordering on one — fewest credits first:

```cypher
MATCH (p:Person)
RETURN p.name AS name
ORDER BY COUNT { MATCH (p)-[:ACTED_IN]->() } ASC, name;
```

Output:

```nocopy
+---------+
| name    |
+---------+
| "Carol" |
| "Dave"  |
| "Bob"   |
| "Alice" |
+---------+
```

Inside a `CASE`:

```cypher
MATCH (p:Person)
RETURN p.name AS name,
       CASE WHEN EXISTS { MATCH (p)-[:ACTED_IN]->() } THEN 'actor' ELSE 'crew' END AS role
ORDER BY name;
```

Output:

```nocopy
+---------+---------+
| name    | role    |
+---------+---------+
| "Alice" | "actor" |
| "Bob"   | "actor" |
| "Carol" | "crew"  |
| "Dave"  | "crew"  |
+---------+---------+
```

A subquery expression in a projection alongside an aggregation becomes part of the
grouping key, so it decides how the rows are grouped — here, people grouped by how
many credits they have:

```cypher
MATCH (p:Person)
RETURN COUNT { (p)-[:ACTED_IN]->() } AS credits, collect(p.name) AS people
ORDER BY credits;
```

Output:

```nocopy
+---------+-------------------+
| credits | people            |
+---------+-------------------+
| 0       | ["Carol", "Dave"] |
| 1       | ["Bob"]           |
| 3       | ["Alice"]         |
+---------+-------------------+
```

As an aggregation argument:

```cypher
MATCH (p:Person)
RETURN sum(COUNT { MATCH (p)-[:KNOWS]->(f) }) AS total;
```

Output:

```nocopy
+-------+
| total |
+-------+
| 3     |
+-------+
```

## The subquery body

### Supported clauses

A body is a read-only query — it never writes to the graph. It matches with
`MATCH`, filters with `WHERE`, aggregates through `WITH`, and may end in a
`RETURN`; it may also be a [`UNION`](#union) of such branches.

`WITH` lets the body aggregate and then filter on the aggregate — here, people
who acted in at least two movies:

```cypher
MATCH (p:Person)
WHERE EXISTS {
  MATCH (p)-[:ACTED_IN]->(m)
  WITH count(*) AS c
  WHERE c >= 2
}
RETURN p.name AS name
ORDER BY name;
```

Output:

```nocopy
+---------+
| name    |
+---------+
| "Alice" |
+---------+
```

A `WHERE` in the body can test relationship properties:

```cypher
MATCH (p:Person)
WHERE EXISTS { MATCH (p)-[r:KNOWS]->(f) WHERE r.since < 2015 }
RETURN p.name AS name
ORDER BY name;
```

Output:

```nocopy
+---------+
| name    |
+---------+
| "Alice" |
+---------+
```

### The bare pattern shorthand

`EXISTS` and `COUNT` accept a bare pattern in place of a full body, which is a
shorter way to write a check or a count over one pattern. The pattern cannot
introduce names of its own — a variable already bound outside may appear in it,
and acts as a join:

```cypher
MATCH (p:Person)
RETURN p.name AS name,
       EXISTS { (p)-[:KNOWS]->() } AS knows,
       COUNT  { (p)-[:ACTED_IN]->() } AS credits
ORDER BY name;
```

Output:

```nocopy
+---------+-------+---------+
| name    | knows | credits |
+---------+-------+---------+
| "Alice" | true  | 3       |
| "Bob"   | true  | 1       |
| "Carol" | false | 0       |
| "Dave"  | false | 0       |
+---------+-------+---------+
```

The shorthand holds a pattern and nothing else, so a filter or any other clause
needs the `MATCH` form — `COUNT { MATCH (p)-[r:KNOWS]->() WHERE r.since < 2015 }`.
`COLLECT` has no shorthand, since a bare pattern returns no column for it to
gather.

### Correlation with the enclosing query

Variables of the enclosing query are visible throughout the body, including after
a `WITH` inside it. The correlation may live in the body's `WHERE` rather than in
its pattern:

```cypher
MATCH (p:Person)
RETURN p.name AS name,
       COUNT { MATCH (f:Person) WHERE (p)-[:KNOWS]->(f) } AS friends
ORDER BY name;
```

Output:

```nocopy
+---------+---------+
| name    | friends |
+---------+---------+
| "Alice" | 2       |
| "Bob"   | 1       |
| "Carol" | 0       |
| "Dave"  | 0       |
+---------+---------+
```

A body need not be correlated at all. An uncorrelated body produces the same
value for every row:

```cypher
RETURN COUNT { MATCH (m:Movie) } AS movies,
       COLLECT { MATCH (m:Movie) RETURN m.title ORDER BY m.title } AS titles;
```

Output:

```nocopy
+--------+----------------------------------------------+
| movies | titles                                       |
+--------+----------------------------------------------+
| 3      | ["Johnny Mnemonic", "Jumanji", "The Matrix"] |
+--------+----------------------------------------------+
```

Variables introduced inside the body are scoped to it and are not available after
the subquery expression.

### The body's RETURN decides the result

The body is a query, and its final `RETURN` shapes the row set that the
expression reduces. `DISTINCT`, `SKIP`, `LIMIT` and aggregation in the body all
count.

This matters most for aggregation. An aggregating `RETURN` produces one row even
when nothing matched, so an `EXISTS` over it is always `true`:

```cypher
MATCH (p:Person)
RETURN p.name AS name,
       EXISTS { MATCH (p)-[:ACTED_IN]->(m) RETURN count(m) } AS v
ORDER BY name;
```

Output:

```nocopy
+---------+------+
| name    | v    |
+---------+------+
| "Alice" | true |
| "Bob"   | true |
| "Carol" | true |
| "Dave"  | true |
+---------+------+
```

To test whether anything matched, leave the aggregation out and let the `MATCH`
decide, as in the [EXISTS section](#exists).

`EXISTS` and `COUNT` do not require a `RETURN` at all; `COLLECT` does, because it
needs a column to gather.

### UNION

A body may be a `UNION` of branches. `UNION` reduces over the branches' rows with
duplicates removed, while `UNION ALL` keeps every row — so for `COUNT` and
`COLLECT` the choice decides the answer:

```cypher
MATCH (p:Person)
RETURN p.name AS name,
       COUNT {
         MATCH (p)-[:KNOWS]->(x) RETURN x
         UNION
         MATCH (p)-[:ACTED_IN]->(x) RETURN x
       } AS related
ORDER BY name;
```

Output:

```nocopy
+---------+---------+
| name    | related |
+---------+---------+
| "Alice" | 5       |
| "Bob"   | 2       |
| "Carol" | 0       |
| "Dave"  | 0       |
+---------+---------+
```

Every branch must return its one column under the same name. Alice and Bob both
know Carol, so `UNION` reports her once:

```cypher
MATCH (p:Person {name: 'Alice'})
RETURN COLLECT {
         MATCH (p)-[:KNOWS]->(f) RETURN f.name AS n
         UNION
         MATCH (:Person {name: 'Bob'})-[:KNOWS]->(f) RETURN f.name AS n
       } AS people;
```

Output:

```nocopy
+------------------+
| people           |
+------------------+
| ["Bob", "Carol"] |
+------------------+
```

The same body with `UNION ALL` keeps the repeat:

```cypher
MATCH (p:Person {name: 'Alice'})
RETURN COLLECT {
         MATCH (p)-[:KNOWS]->(f) RETURN f.name AS n
         UNION ALL
         MATCH (:Person {name: 'Bob'})-[:KNOWS]->(f) RETURN f.name AS n
       } AS people;
```

Output:

```nocopy
+---------------------------+
| people                    |
+---------------------------+
| ["Bob", "Carol", "Carol"] |
+---------------------------+
```

## Nesting

A body may itself contain subquery expressions. Here `EXISTS` filters on a nested
`EXISTS` — people who know an actor:

```cypher
MATCH (p:Person)
WHERE EXISTS {
  MATCH (p)-[:KNOWS]->(f)
  WHERE EXISTS { MATCH (f)-[:ACTED_IN]->() }
}
RETURN p.name AS name
ORDER BY name;
```

Output:

```nocopy
+---------+
| name    |
+---------+
| "Alice" |
+---------+
```

They can also be combined, for example collecting a per-friend count:

```cypher
MATCH (p:Person)
RETURN p.name AS name,
       COLLECT {
         MATCH (p)-[:KNOWS]->(f)
         RETURN COUNT { (f)-[:ACTED_IN]->() } ORDER BY f.name
       } AS friendMovies
ORDER BY name;
```

Output:

```nocopy
+---------+--------------+
| name    | friendMovies |
+---------+--------------+
| "Alice" | [1, 0]       |
| "Bob"   | [0]          |
| "Carol" | []           |
| "Dave"  | []           |
+---------+--------------+
```

## Dataset queries

We encourage you to try out the examples by yourself.
You can get the dataset locally by executing the following query block.

```cypher
MATCH (n) DETACH DELETE n;

CREATE (alice:Person {name: 'Alice', nickname: 'Al'});
CREATE (bob:Person {name: 'Bob', nickname: 'Bobby'});
CREATE (carol:Person {name: 'Carol'});
CREATE (dave:Person {name: 'Dave'});
CREATE (:Movie {title: 'The Matrix', released: 1999});
CREATE (:Movie {title: 'Jumanji', released: 1995});
CREATE (:Movie {title: 'Johnny Mnemonic', released: 1995});
MATCH (a:Person {name: 'Alice'}), (b:Person {name: 'Bob'})
CREATE (a)-[:KNOWS {since: 2010}]->(b);
MATCH (a:Person {name: 'Alice'}), (c:Person {name: 'Carol'})
CREATE (a)-[:KNOWS {since: 2015}]->(c);
MATCH (b:Person {name: 'Bob'}), (c:Person {name: 'Carol'})
CREATE (b)-[:KNOWS {since: 2018}]->(c);
MATCH (a:Person {name: 'Alice'}), (m:Movie {title: 'The Matrix'})
CREATE (a)-[:ACTED_IN]->(m);
MATCH (a:Person {name: 'Alice'}), (m:Movie {title: 'Johnny Mnemonic'})
CREATE (a)-[:ACTED_IN]->(m);
MATCH (a:Person {name: 'Alice'}), (m:Movie {title: 'Jumanji'})
CREATE (a)-[:ACTED_IN]->(m);
MATCH (b:Person {name: 'Bob'}), (m:Movie {title: 'Jumanji'})
CREATE (b)-[:ACTED_IN]->(m);
```