UNION
UNION
combines the results of two or more queries into a single result set that includes all the rows that belong to any queries in the union.
The number and the names of the columns must be identical in all queries combined by using UNION
.
To keep all the result rows, use UNION ALL
.
Using just UNION
(or UNION DISTINCT
) will combine and remove duplicates from the result set.
If any of the queries in a Any clause before the See clause composition in queries with |
Example graph
The following graph is used for the examples below:
To recreate the graph, run the following query against an empty Neo4j database:
CREATE (johnny:Actor {name: 'Johnny Depp'}),
(sarah:Actor {name: 'Sarah Jessica Parker'}),
(ed:Actor&Director {name: 'Ed Wood'}),
(edWoodMovie:Movie {title: 'Ed Wood'}),
(johnny)-[:ACTED_IN]->(edWoodMovie),
(sarah)-[:ACTED_IN]->(edWoodMovie)
Combine two queries and retain duplicates
Combining the results from two queries is done using UNION ALL
.
MATCH (n:Actor)
RETURN n.name AS name
UNION ALL
MATCH (n:Movie)
RETURN n.title AS name
The combined result is returned, including duplicates.
name |
---|
|
|
|
|
|
Combine two queries and remove duplicates
By not including ALL
in the UNION
, duplicates are removed from the combined result set.
MATCH (n:Actor)
RETURN n.name AS name
UNION
MATCH (n:Movie)
RETURN n.title AS name
The combined result is returned, without duplicates.
name |
---|
|
|
|
|
UNION DISTINCT
Removal of duplicates can also be accomplished by explicitly including DISTINCT
in the UNION
.
The UNION DISTINCT
keyword was introduced as part of Cypher®'s GQL conformance, and using it is functionally the same as using simple UNION
.
MATCH (n:Actor)
RETURN n.name AS name
UNION DISTINCT
MATCH (n:Movie)
RETURN n.title AS name
The combined result is returned, without duplicates.
name |
---|
|
|
|
|
Post-union processing
The UNION
clause can be used within a CALL
subquery to further process the combined results before a final output is returned.
For example, the below query counts the occurrences of each name
property returned after the UNION ALL
within the CALL
subquery.
The below query uses an empty variable scope clause: CALL () { … } (introduced in Neo4j 5.23).
If you are using an older version of Neo4j, use CALL { … } instead.
For more information, see CALL subqueries → Importing variables.
|
CALL () {
MATCH (a:Actor)
RETURN a.name AS name
UNION ALL
MATCH (m:Movie)
RETURN m.title AS name
}
RETURN name, count(*) AS count
ORDER BY count
name | count |
---|---|
|
|
|
|
|
|
Rows: 3 |
For more information, see CALL
subqueries → Post-union processing.