OPTIONAL MATCH matches patterns against your graph database, just like MATCH does. The difference is that if no matches are found, OPTIONAL MATCH will use a null for missing parts of the pattern. It is equivalent to Outer Join in SQL.
Example
Get all the persons age and name, where he report to and relative to same person.
OPTIONAL MATCH (n1:Person)-[:REPORT_TO]->(n2)<-[:RELATIVE_TO]-(n1) RETURN n1.name, n1.age
Let’s see it with an example.
Step 1: Open neo4j browser by hitting following url in browser.
http://localhost:7474/browser/
Step 2: Insert data by executing below query.
CREATE (p1:Person{name: "Rama Krishna", age: 32, city: "Bangalore", male: true, designation: "CEO"}), (p2:Person{name: "Murali", age: 32, city: "Hyderabad", male: true, designation: "Vice President"}), (p3:Person{name: "Gopi", age: 34, city: "Chennai", male: true, designation: "Vice President"}), (p4:Person{name: "Kiran", age: 28, city: "Bangalore", male: true, designation: "Director"}), (p5:Person{name: "Siva", age: 45, city: "Hyderabad", male: true, designation: "Director"}), (p6:Person{name: "Kiran", age: 36, city: "Hyderabad", male: true, designation: "Manager"}), (p7:Person{name: "Ritweek", age: 23, city: "Bangalore", male: true, designation: "Manager"}), (p8:Person{name: "Sudheer", age: 24, city: "Chennai", male: true, designation: "Manager"}), (p9:Person{name: "Naveen", age: 37, city: "Bangalore", male: true, designation: "Employee"}), (p10:Person{name: "Sailaja", age: 31, city: "Hyderabad", female: true, designation: "Employee"}), (p11:Person{name: "Harika", age: 36, city: "Bangalore", female: true, designation: "Employee"}), (p12:Person{name: "Brahmam", age: 41, city: "Bangalore", male: true, designation: "Employee"}), (office1:Location{name: "Pluto", city: "Bangalore"}), (office2:Location{name: "Mars", city: "Chennai"}) MERGE (p2)-[:REPORT_TO{from: 2014, description: "reports to"}]->(p1) MERGE (p3)-[:REPORT_TO{from: 2016, description: "reports to"}]->(p1) MERGE (p4)-[:REPORT_TO{from: 1997, description: "reports to"}]->(p2) MERGE (p5)-[:REPORT_TO{from: 1997, description: "reports to"}]->(p3) MERGE (p6)-[:REPORT_TO{from: 2001, description: "reports to"}]->(p4) MERGE (p7)-[:REPORT_TO{from: 2009, description: "reports to"}]->(p4) MERGE (p8)-[:REPORT_TO{from: 1998, description: "reports to"}]->(p5) MERGE (p9)-[:REPORT_TO{from: 2003, description: "reports to"}]->(p6) MERGE (p10)-[:REPORT_TO{from: 2004, description: "reports to"}]->(p6) MERGE (p11)-[:REPORT_TO{from: 2008, description: "reports to"}]->(p7) MERGE (p12)-[:REPORT_TO{from: 2019, description: "reports to"}]->(p8) MERGE (p2)-[:FRIEND_TO{from: 2014, description: "friend to"}]->(p1) MERGE (p10)-[:FRIEND_TO{from: 2014, description: "friend to"}]->(p8) MERGE (p7)-[:FRIEND_TO{from: 2014, description: "friend to"}]->(p9) MERGE (p3)-[:RELATIVE_TO{from: 2014, description: "relative to"}]->(p4) RETURN p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, office1, office2
Step 3: Execute MATCH query to get all the persons age and name, where he report to and relative to same person.
MATCH (n1:Person)-[:REPORT_TO]->(n2)<-[:RELATIVE_TO]-(n1) RETURN n1.name, n1.age
No execute OPTIONAL MATCH query to see the difference.
OPTIONAL MATCH (n1:Person)-[:REPORT_TO]->(n2)<-[:RELATIVE_TO]-(n1) RETURN n1.name, n1.age
As you see the above image, OPTIONAL MATCH return null, if there is no match found.
No comments:
Post a Comment