# Procedures, subqueries and parameters

[Wiki](../README.md) / [Cypher query language](README.md)

**On this page**

- [CALL … YIELD (procedures)](#call--yield-procedures)
- [CALL { … } (subqueries)](#call----subqueries)
- [OPTIONAL CALL](#optional-call)
- [Parameters](#parameters)

## `CALL … YIELD` (procedures)

Invoke a built-in procedure; `YIELD` selects its output columns into the row
stream. See [Schema and maintenance procedures](procedures.md) for the full catalogue.

```cypher
CALL db.labels() YIELD label RETURN label ORDER BY label;

CALL db.index.vector.queryNodes('embIdx', 5, [0.1, 0.2, 0.3])
YIELD node, score
RETURN node, score;

// a standalone CALL (no RETURN) streams the procedure's own columns:
CALL db.constraints();
```

## `CALL { … }` (subqueries)

Run a subquery per input row. Useful for post-`UNION` composition and
side-effecting units.

```cypher
MATCH (p:Person)
CALL {
  WITH p
  MATCH (p)-[:KNOWS]->(f)
  RETURN count(f) AS friends
}
RETURN p.name, friends;
```

- A subquery that returns columns folds them into the outer row.
- A **unit** subquery (no `RETURN`) runs purely for side effects and leaves the
  outer cardinality unchanged.

## `OPTIONAL CALL`

From version 2.4.0. `OPTIONAL CALL` keeps every outer row. When the subquery, procedure or SQL
block produces nothing for a row, the row stays with its new columns set to
null, as `OPTIONAL MATCH` does for a pattern. A plain `CALL` drops that row.

```cypher
// Each person's primary phone, people without a phone included:
MATCH (p:Person)
OPTIONAL CALL (p) {
  MATCH (p)-[r:HAS_PHONE]->(x)
  RETURN x.number AS phone ORDER BY r.rank LIMIT 1
}
RETURN p.name, phone;

// A procedure that may yield nothing for a row:
MATCH (p:Person)
OPTIONAL CALL db.labels() YIELD label WHERE label = p.kind
RETURN p.name, label;

// A node's table that may be empty:
MATCH (p:Person)
OPTIONAL CALL (p) SQL { SELECT body FROM p.notes }
RETURN p.name, body;
```

- The subquery runs once per row and stops at its own `LIMIT`, so taking the
  top match per row reads only what it needs.
- A subquery that aggregates (`RETURN count(x) AS n`) always returns a row,
  so its value is the aggregate (`0`), not null.
- `OPTIONAL CALL { … } IN TRANSACTIONS` is a parse error.

## Parameters

Statements may use `$name` parameters, supplied by the driver (or the embedding
API). Parameters are values, never query fragments.

```cypher
MATCH (p:Person {email: $email}) RETURN p;       // $email bound by the driver
MATCH (p:Person) WHERE p.age > $minAge RETURN p.name;
```

## Subquery expressions: `COLLECT`, `EXISTS`, `COUNT`

A read-only query in braces can be used as a value. It runs once per row and
sees that row's variables without importing them.

```cypher
MATCH (p:Person)
RETURN p.name,
       COLLECT { MATCH (p)-[:OWNS]->(d:Dog) RETURN d.name ORDER BY d.name } AS dogs,
       COUNT   { MATCH (p)-[:OWNS]->(d:Dog) WHERE d.age > 5 } AS old_dogs,
       EXISTS  { MATCH (p)-[:KNOWS]->(f) WHERE f.age > p.age } AS knows_someone_older;
```

- `COLLECT { … }` returns a list of the subquery's single `RETURN` column — an
  empty list when nothing matches. It must end with `RETURN` of exactly one column.
- `EXISTS { … }` is true when the subquery produces a row; `COUNT { … }` is the
  number of rows. For these two the `RETURN` may be omitted, and the short
  single-pattern form (`EXISTS { (p)-[:OWNS]->() }`) still works.
- The body may use `WITH`, `ORDER BY`, `LIMIT`, `UNION` and nested subquery
  expressions. It may not write: an updating clause inside one is a parse error.

## Related articles

[Indexes and constraints](../indexes/README.md) · [Procedure directory](../reference/procedures.md) · [Transactions and concurrency](../operations/transactions.md)
