Neo4j & Cypher #3 UNION & WITH clasuse
Today’s post is going to be super easy, especially if you’re familiar with SQL, so without much explanation let’s discuss a little bit about two clauses that you might spot later in my Neo4j & Cypher series – Union and With.
The Union clause can be used to merge the results of two queries. There are just two restrictions:
- the number of columns in both queries must be the same
- column names in both queries must be the same
Let’s demonstrate that by creating a simple query:
MATCH (a: Actor) RETURN a.id as result LIMIT 5 UNION MATCH (d: Director) RETURN d.name as result LIMIT 5
The code is very simple. We take the Actor nodes from which we retrieve the id. We also limit the number of the results to five. Then, the UNION clause comes into play which will merge the result with the next query which did the same thing as in the first one. The difference is that instead of Actor nodes we want a Director, and we replaced the id property with the name. Notice that both queries have exactly one column with the same name – result. Let’s run it:
As expected the result contains the ids of some not specified actors but also names of some directors. The cool thing about it is that we could merge the result even though both queries return different properties (id and name). What’s also worth to point here is that UNION excludes the duplicates in the final result. If you want to include them anyway, use the UNION ALL instead.
The second clause is WITH. What it does is defining what will be available later in the query. I think that in this case, the code will be the best explanation:
MATCH ()-[ACTS_IN]->(m) WITH m.title as title RETURN m
We created a simple pattern which matches Actor with Movie. Later we used a WITH to specify what will be available after it. It could be the single property (like in the above case), node, relationship etc. Notice that in the RETURN we want to retrieve the whole node, not the title. Let’s run that:
As you can see, Cypher didn’t know anything about m variable since we had limited the access only to the title. Let’s replace the above code with this one:
MATCH ()-[ACTS_IN]->(m) WITH m.title as title RETURN title
Now, it works as expected. So, if you want to make sure that specific of the variables will not be used, somewhere later in the query, simply use WITH clause.