Return

RETURN

RETURN is similar to the SELECT clause of SQL. RETURN is where the final results of the query are specified, which can be listed as a list of expressions, e.g., variables that have bound to nodes or relationships, aliases, or more complex expressions. RETURN can also be used for performing group-by and aggregations as well as duplication removing (using the DISTINCT clause). We discuss several common expressions used in RETURN.

We will use the example database for demonstration, whose schema and data import commands are given here

Returning node and relationship variables

Returning variables in the query that are bound to node and relationships in the query is a syntactic sugar to return all properties of those variables. For example:

MATCH (a:User)-[e:Follows]->(b:User)
RETURN a, e;

Output:

---------------------------------------------------------------------------------------------------
| a                                         | e                                                   |
---------------------------------------------------------------------------------------------------
| (label:User, 0:0, {name:Adam, age:30})    | (0:0)-[label:Follows, {_id:2:0, since:2020}]->(0:1) |
---------------------------------------------------------------------------------------------------
| (label:User, 0:0, {name:Adam, age:30})    | (0:0)-[label:Follows, {_id:2:1, since:2020}]->(0:2) |
---------------------------------------------------------------------------------------------------
| (label:User, 0:1, {name:Karissa, age:40}) | (0:1)-[label:Follows, {_id:2:2, since:2021}]->(0:2) |
---------------------------------------------------------------------------------------------------
| (label:User, 0:2, {name:Zhang, age:50})   | (0:2)-[label:Follows, {_id:2:3, since:2022}]->(0:3) |
---------------------------------------------------------------------------------------------------

Returning all variables

Returning all variables in the query can be written as RETURN * as a syntactic sugar. Below query returns a and b, relationship is omitted because no variable binds to it.

MATCH (a:User)-[:Follows]->(b:User)
RETURN *;

Output:

-----------------------------------------------------------------------------------------
| b                                         | a                                         |
-----------------------------------------------------------------------------------------
| (label:User, 0:1, {name:Karissa, age:40}) | (label:User, 0:0, {name:Adam, age:30})    |
-----------------------------------------------------------------------------------------
| (label:User, 0:2, {name:Zhang, age:50})   | (label:User, 0:0, {name:Adam, age:30})    |
-----------------------------------------------------------------------------------------
| (label:User, 0:2, {name:Zhang, age:50})   | (label:User, 0:1, {name:Karissa, age:40}) |
-----------------------------------------------------------------------------------------
| (label:User, 0:3, {name:Noura, age:25})   | (label:User, 0:2, {name:Zhang, age:50})   |
-----------------------------------------------------------------------------------------

Returning node and relationship properties

You can also return properties of variables by explicitly specifying properties in the RETURN clause.

MATCH (a:User)-[e:Follows]->(b:User)
RETURN a.name, a.age, e.since;

Output:

-----------------------------
| a.name  | a.age | e.since |
-----------------------------
| Adam    | 30    | 2020    |
-----------------------------
| Adam    | 30    | 2020    |
-----------------------------
| Karissa | 40    | 2021    |
-----------------------------
| Zhang   | 50    | 2022    |
-----------------------------

As a syntactic sugar, Kùzu supports returning all properties of node or rel with *.

MATCH (a:User) RETURN a.*;

Output:

-------------------
| a.name  | a.age |
-------------------
| Adam    | 30    |
-------------------
| Karissa | 40    |
-------------------
| Zhang   | 50    |
-------------------
| Noura   | 25    |
-------------------

```cypher
MATCH (a:User)-[e:Follows]->(b:User) WHERE a.name='Adam' RETURN e.*;

Output:

-----------
| e.since |
-----------
| 2020    |
-----------
| 2020    |
-----------

Using distinct for duplicate elimination

You can use RETURN DISTINCT to do duplicate elimination of the returned tuples. For example, if we instead wrote RETURN DISTINCT in the above query, we would eliminate one of the 2 (Adam, 30, 2020) tuples above:

MATCH (a:User)-[e:Follows]->(b:User)
RETURN DISTINCT a.name, a.age, e.since;

Output:

-----------------------------
| a.name  | a.age | e.since |
-----------------------------
| Adam    | 30    | 2020    |
-----------------------------
| Karissa | 40    | 2021    |
-----------------------------
| Zhang   | 50    | 2022    |
-----------------------------

Group by and aggregations

You can group by one or more expression and perform one or more aggregations in a RETURN clause. For example:

MATCH (a:User)-[:Follows]->(b:User)
RETURN a, avg(b.age) as avgFriendAge;

Output:

------------------------------------------------------------
| a                                         | avgFriendAge |
------------------------------------------------------------
| (label:User, 0:0, {name:Adam, age:30})    | 45.000000    |
------------------------------------------------------------
| (label:User, 0:1, {name:Karissa, age:40}) | 50.000000    |
------------------------------------------------------------
| (label:User, 0:2, {name:Zhang, age:50})   | 25.000000    |
------------------------------------------------------------

The semantics is exactly the same as SQL’s semantics, which is a 3-step process:

for each tuple t in the previous part of the query, i.e., before the RETURN clause, group t according to (one or more) group by key expressions into a group. Let us refer to the result of these expressions as t’s keys.
For each group G, compute the (or or more) aggregations in the query.
Output for each group G, G’s key(s) and the result of the aggregations.

You can find the list of aggregation functions supported in Kùzu here.