Querying graph database data
The original form of the Utb table can be restored by means of an ordinary SQL query:
However, to make graph navigation easier SQL Server introduces the MATCH function that defines a search pattern for nodes based on their relations. The pattern syntax resembles the one used by Cypher, the Neo4j’s graph query language.
In a simplified form, a pattern definition looks as follows:
The node is represented by the table name or alias. The use of aliases becomes indispensable if a table is used in a pattern more than once, e.g. in case of a self-join. The MATCH function patterns can be combined using the AND operator. Let’s continue with some examples. For a start, we rewrite the previous query, making it graph-oriented:
Here, we use aliases for the sake of shortness only. We can rearrange the nodes, but the direction of the edge (from the spray can to the square) has to be retained:
Unlike in Cypher, the direction of the relationship has to be specified, in other words, the following function call
Get the squares dyed with red paint. Display the square ID and the amount of red paint.
Get the squares dyed both with red and blue paint. Display the names of the squares.
Note that you have to include the corresponding table into the FROM clause for each (generally different) node used in the pattern, as well as the relationship table. If the same node is implied, as the square in our case, we can use it for a bidirectional relationship. The predicate
DISTINCT is necessary in these queries, because without it, duplicate records will occur for squares painted by more than one red (and/or blue) spray cans.
Get squares dyed with all three colors.
The most straightforward approach would be INTERSECTing our previous example with an additional query. However, let’s present a more effective solution that uses the fact the number of distinct colors the square has been painted with is three:
Get the spray cans used for painting more than one square.
The condition match(Q2<-(B2)-V-(B1)->Q) states a spray can participated in two paint events, while Q.q_id <> Q2.q_id claims there were different squares involved.
At first glance, relational and graph-oriented queries don't seem to be radically different. Moreover, one can omit the graph-oriented syntax completely, keeping within SQL boundaries. However, the examples about aren’t very illustrative, since the advantages of graph databases become apparent for subject areas with complex relationships between the data entities.