Set operators¶
This topic will describe the set operators, including UNION, UNION ALL, INTERSECT, and MINUS. To combine multiple queries, use these set operators.
All set operators have equal precedence. If a nGQL statement contains multiple set operators, NebulaGraph will evaluate them from left to right unless parentheses explicitly specify another order.
Caution
The names and order of the variables defined in the query statements before and after the set operator must be consistent. For example, the names and order of a,b,c in RETURN a,b,c UNION RETURN a,b,c need to be consistent.
UNION, UNION DISTINCT, and UNION ALL¶
<left> UNION [DISTINCT | ALL] <right> [ UNION [DISTINCT | ALL] <right> ...]
- Operator
UNION DISTINCT(or by shortUNION) returns the union of two sets A and B without duplicated elements.
- Operator
UNION ALLreturns the union of two sets A and B with duplicated elements.
- The
<left>and<right>must have the same number of columns and data types. Different data types are converted according to the Type Conversion.
Examples¶
# The following statement returns the union of two query results without duplicated elements.
nebula> GO FROM "player102" OVER follow YIELD dst(edge) \
UNION \
GO FROM "player100" OVER follow YIELD dst(edge);
+-------------+
| dst(EDGE) |
+-------------+
| "player100" |
| "player101" |
| "player125" |
+-------------+
nebula> MATCH (v:player) \
WITH v.player.name AS v \
RETURN n ORDER BY n LIMIT 3 \
UNION \
UNWIND ["Tony Parker", "Ben Simmons"] AS n \
RETURN n;
+---------------------+
| n |
+---------------------+
| "Amar'e Stoudemire" |
| "Aron Baynes" |
| "Ben Simmons" |
| "Tony Parker" |
+---------------------+
# The following statement returns the union of two query results with duplicated elements.
nebula> GO FROM "player102" OVER follow YIELD dst(edge) \
UNION ALL \
GO FROM "player100" OVER follow YIELD dst(edge);
+-------------+
| dst(EDGE) |
+-------------+
| "player100" |
| "player101" |
| "player101" |
| "player125" |
+-------------+
nebula> MATCH (v:player) \
WITH v.player.name AS n \
RETURN n ORDER BY n LIMIT 3 \
UNION ALL \
UNWIND ["Tony Parker", "Ben Simmons"] AS n \
RETURN n;
+---------------------+
| n |
+---------------------+
| "Amar'e Stoudemire" |
| "Aron Baynes" |
| "Ben Simmons" |
| "Tony Parker" |
| "Ben Simmons" |
+---------------------+
# UNION can also work with the YIELD statement. The DISTINCT keyword will check duplication by all the columns for every line, and remove duplicated lines if every column is the same.
nebula> GO FROM "player102" OVER follow \
YIELD dst(edge) AS id, properties(edge).degree AS Degree, properties($$).age AS Age \
UNION /* DISTINCT */ \
GO FROM "player100" OVER follow \
YIELD dst(edge) AS id, properties(edge).degree AS Degree, properties($$).age AS Age;
+-------------+--------+-----+
| id | Degree | Age |
+-------------+--------+-----+
| "player100" | 75 | 42 |
| "player101" | 75 | 36 |
| "player101" | 95 | 36 |
| "player125" | 95 | 41 |
+-------------+--------+-----+
INTERSECT¶
<left> INTERSECT <right>
- Operator
INTERSECTreturns the intersection of two sets A and B (denoted by A ⋂ B).
- Similar to
UNION, theleftandrightmust have the same number of columns and data types. Different data types are converted according to the Type Conversion.
Example¶
# The following statement returns the intersection of two query results.
nebula> GO FROM "player102" OVER follow \
YIELD dst(edge) AS id, properties(edge).degree AS Degree, properties($$).age AS Age \
INTERSECT \
GO FROM "player100" OVER follow \
YIELD dst(edge) AS id, properties(edge).degree AS Degree, properties($$).age AS Age;
+----+--------+-----+
| id | Degree | Age |
+----+--------+-----+
+----+--------+-----+
nebula> MATCH (v:player)-[e:follow]->(v2) \
WHERE id(v) == "player102" \
RETURN id(v2) As id, e.degree As Degree, v2.player.age AS Age \
INTERSECT \
MATCH (v:player)-[e:follow]->(v2) \
WHERE id(v) == "player100" \
RETURN id(v2) As id, e.degree As Degree, v2.player.age AS Age;
+----+--------+-----+
| id | Degree | Age |
+----+--------+-----+
+----+--------+-----+
nebula> UNWIND [1,2] AS a RETURN a \
INTERSECT \
UNWIND [1,2,3,4] AS a \
RETURN a;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
MINUS¶
<left> MINUS <right>
Operator MINUS returns the subtraction (or difference) of two sets A and B (denoted by A-B). Always pay attention to the order of left and right. The set A-B consists of elements that are in A but not in B.
Example¶
# The following statement returns the elements in the first query result but not in the second query result.
nebula> GO FROM "player100" OVER follow YIELD dst(edge) \
MINUS \
GO FROM "player102" OVER follow YIELD dst(edge);
+-------------+
| dst(EDGE) |
+-------------+
| "player125" |
+-------------+
nebula> GO FROM "player102" OVER follow YIELD dst(edge) AS id\
MINUS \
GO FROM "player100" OVER follow YIELD dst(edge) AS id;
+-------------+
| id |
+-------------+
| "player100" |
+-------------+
nebula> MATCH (v:player)-[e:follow]->(v2) \
WHERE id(v) =="player102" \
RETURN id(v2) AS id\
MINUS \
MATCH (v:player)-[e:follow]->(v2) \
WHERE id(v) =="player100" \
RETURN id(v2) AS id;
+-------------+
| id |
+-------------+
| "player100" |
+-------------+
nebula> UNWIND [1,2,3] AS a RETURN a \
MINUS \
WITH 4 AS a \
RETURN a;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
Precedence of the set operators and pipe operators¶
Please note that when a query contains a pipe | and a set operator, the pipe takes precedence. Refer to Pipe for details. The query GO FROM 1 UNION GO FROM 2 | GO FROM 3 is the same as the query GO FROM 1 UNION (GO FROM 2 | GO FROM 3).
Examples¶
nebula> GO FROM "player102" OVER follow \
YIELD dst(edge) AS play_dst \
UNION \
GO FROM "team200" OVER serve REVERSELY \
YIELD src(edge) AS play_src \
| GO FROM $-.play_src OVER follow YIELD dst(edge) AS play_dst;
+-------------+
| play_dst |
+-------------+
| "player100" |
| "player101" |
| "player117" |
| "player105" |
+-------------+
The above query executes the statements in the red bar first and then executes the statement in the green box.
The parentheses can change the execution priority. For example:
nebula> (GO FROM "player102" OVER follow \
YIELD dst(edge) AS play_dst \
UNION \
GO FROM "team200" OVER serve REVERSELY \
YIELD src(edge) AS play_dst) \
| GO FROM $-.play_dst OVER follow YIELD dst(edge) AS play_dst;
In the above query, the statements within the parentheses take precedence. That is, the UNION operation will be executed first, and its output will be executed as the input of the next operation with pipes.