What is the difference between the following (about "WITH")?

user_17456440 0
Viewed 30

I trying to understand what is the difference between the following:

match (n:Crew)-[:KNOWS]-()
with n,collect(DISTINCT n) AS mygroup 
match (m:Crew) 
where not m in mygroup 
return count(*)

and:

match (n:Crew)-[:KNOWS]-()
with collect(DISTINCT n) AS mygroup 
match (m:Crew) 
where not m in mygroup 
return count(*)

What WITH pass in the first case and WITH pass in the second case? and how it affects about the answer.

cypher neo4j
2 Answers

The first one will return both n and mygroup, while the second one will return only mygroup. The n is not used in the rest of the query, thus not needed. Using the first option is not just redundant, it will probably also run the rest of the query multiple times, according to the concept of cardinality, as the number of rows will be the count of n which will probably be larger than the count of mygroup.

user_184823100

This is the difference.

In "with n,collect(DISTINCT n) AS mygroup", you will get each crew and a set with that crew. It is similar to SQL group by clause wherein you group by n then aggregate distinct n. For example;

        n    collect(n)
 row1 crewA  [crewA]
 row2 crewB  [crewB]
 row3 crewZ  [crewZ]

Meanwhile, if you use collect(DISTINCT n) without n on the query, it will simply aggregate all distinct n. Thus the result is just one group of crews (crewA...to crewZ).

 row 1 [crewA, crewB, crewZ]

Then you return how many nodes does not belong to this group (return count(*)).

I have a suggestion and this query is much better. Change your query into this. It will give you the same result on how many crew nodes has no relationship (:KNOWS). It will count the number of nodes (m) that does not have the relationship (:KNOWS),

match (m:Crew) 
where not exists ((m)-[:KNOWS]-())
return count(distinct m)
user_73718930
Related