SQL Server Graph Databases: Querying Graph Data

SQL Server Graph Databases supports the MATCH function to query graph data. The syntax and the semantics of this function are similar to those of the MATCH function from the Cypher query language of another graph database system, Neo4J. Cypher is a declarative query language that is implemented to efficiently query graphs. (Note that currently only part of the original language has been implemented in SQL Server Graph Databases.)

After the introduction of the MATCH function, we will discuss recursive relationships and how they can be implemented in SQL Server Graph Databases.

1. The MATCH Function

The MATCH function allows you to specify a search pattern based on relationships between two nodes. This function is a part of the WHERE clause of the SELECT statement that queries node and edge tables. Example 31.7 shows the use of this function.

Example 31.7

— Get the names of the companies and the names of their employees

USE graph_db;

SELECT DISTINCT Cmp.Name CName, Emp.Name EName

FROM Employee Emp, WorksIn, Company Cmp

WHERE MATCH(Emp-(WorksIn)->Cmp);

The result is

Each MATCH function must contain a search pattern. Such patterns represent one or more relationships. For each relationship, you must specify the originating node and the terminating node, as well as the edge table that connects the two nodes together. You must also specify the direction of the relationship, using dashes and arrows, with the edge table situated between the two node tables. In Example 31.7, the term

(Emp-(WorksIn)->Cmp)

specifies that the originating node is Employee (represented by its alias, Emp), the edge table is WorksIn, and the terminating node is Company (represented by its alias, Cmp). Generally, if you want to specify a single relationship called Edge with the originating node Nodel and terminating node Node2, you would use the following syntax:

MATCH(Node1-(Edge)->Node2)

NOTE As shown in the syntax, the name of the edge table in the MATCH function is enclosed in parentheses, with a dash preceding its name and a dash and right arrow following its name. This specifies a relationship that moves from left to right. You can reverse this order and specify a relationship that moves from right to left by reversing the order of the node names (that is, MATCH(Node2-(Edge)->Node1). Generally, this relationship is semantically different from the left-to-right relationship, as demonstrated in Examples 31.15 and 31.16 later in the chapter.

Examples 31.8 and 31.9 show how to narrow the result of the query in Example 31.7 by using the AND operator.

Example 31.8

— Get the name of the company where Matthew Smith works

USE graph_db;

SELECT Cmp.Name CName

FROM Employee Emp, WorksIn, Company Cmp

WHERE MATCH(Emp-(WorksIn)->Cmp)

AND Emp.Name= ‘Matthew Smith’;

The result is

Example 31.9

— Get the list of the employees who live in Madison

USE graph_db;

SELECT Emp.Name EName

FROM Employee Emp, LivesIn, City

WHERE MATCH(Emp-(LivesIn)->City)

AND City.Name=’Madison’;

The result is

SQL Server Graph Databases allows you to use other clauses of the SELECT statement together with the MATCH function. Example 31.10 uses the ORDER BY clause to sort the result rows according to the first column in the SELECT list.

Example 31.10

— Get the list of companies located in the city of Hoboken

— Sort the companies according to their names

USE graph_db;

SELECT Cmp.Name CName

FROM City C, LocatedIn, Company Cmp

WHERE MATCH(Cmp-(LocatedIn)->C)

AND C.Name=’Hoboken’ ORDER BY 1;

The result is

SQL Server Graph Databases allows you to traverse a graph as deep as you wish. In this case you have to specify multiple relationships using the MATCH function. One way to do it is to use the AND operator, as shown in Example 31.11.

NOTE The Boolean operators, OR and NOT, cannot be used with the MATCH function.

Example 31.11

USE graph_db;

SELECT Employee.name EName, Company.name CName

FROM Employee, WorksIn, Company, LocatedIn, City

WHERE MATCH(Employee-(WorksIn)->Company

AND Company-(LocatedIn)->City )

AND WorksIn.Starts=’2017′ AND Company.name=’Comp_C’;

The result is

Example 31.11 links the instances of the Employee node with the instances of the Company node using the WorksIn relationship. After that, it links the instances of the Company node with the instances of the City node using the LocatedIn relationship. By being able to link together multiple relationships, you can traverse a graph as deep as you wish.

Generally, you can link together multiple relationships without using the AND operator, as long as the particular search pattern specifies the same logic. In other words, the terminating node table of the previous pattern must be the originating table of the subsequent one. Example 31.12 solves the same problem as the previous one, linking together multiple relationships without using the AND operator.

Example 31.12

USE graph_db;

SELECT Employee.name, Company.name

FROM Employee, WorksIn, Company, LocatedIn, City

WHERE MATCH(Employee-(WorksIn)->Company-(LocatedIn)->City)

AND WorksIn.Starts=’2017′ and Company.name=’Comp_C’;

2. Recursive Relationships

All relationships that have been used up to this point in this chapter are nonrecursive relationships, because each of them connects two different nodes. A recursive relationship is a special form of relationships in which a node is connected with itself.

In the following examples we will use a relationship called Is_Liked. This recursive relationship connects the Employee entity with itself to find employees who like other employees of the company. Example 31.13 creates the Is_Liked edge table and inserts several rows into this table.

Example 31.13

— Create an edge table for the recursive relationship

USE graph_db;

CREATE TABLE dbo.Is_Liked(start_date DATE) AS EDGE;

— Insert several rows

INSERT INTO Is Liked VALUES

((SELECT $node_id FROM Employee WHERE ID = 1),

(SELECT $node_id FROM Employee WHERE ID = 2),’1.1.2017′);

INSERT INTO Is_Liked VALUES

((SELECT $node_id FROM Employee WHERE ID = 1),

(SELECT $node_id FROM Employee WHERE ID = 3),’2.1.2018′);

INSERT INTO Is_Liked VALUES

((SELECT $node_id FROM Employee WHERE ID = 1),

(SELECT $node_id FROM Employee WHERE ID = 4),’3.1.2019′);

INSERT INTO Is_Liked VALUES

((SELECT $node_id FROM Employee WHERE ID = 2),

(SELECT $node_id FROM Employee WHERE ID = 3),’4.1.2016′);

INSERT INTO Is_Liked VALUES

((SELECT $node_id FROM Employee WHERE ID = 2),

(SELECT $node_id FROM Employee WHERE ID = 5),’5.1.2017′);

INSERT INTO Is_Liked VALUES

((SELECT $node_id FROM Employee WHERE ID = 2),

(SELECT $node_id FROM Employee WHERE ID = 6),’6.1.2017′);

INSERT INTO Is_Liked VALUES

((SELECT $node_id FROM Employee WHERE ID = 3),

(SELECT $node_id FROM Employee WHERE ID = 4),’7.1.2018′);

INSERT INTO Is_Liked VALUES

((SELECT $node_id FROM Employee WHERE ID = 4),

(SELECT $node_id FROM Employee WHERE ID = 5),’8.1.2016′);

INSERT INTO Is_Liked VALUES

((SELECT $node_id FROM Employee WHERE ID = 4),

(SELECT $node_id FROM Employee WHERE ID = 6),’9.1.2017′);

INSERT INTO Is_Liked VALUES

((SELECT $node_id FROM Employee WHERE ID = 5),

(SELECT $node_id FROM Employee WHERE ID = 6),’10.1.2019′);

Example 31.14 displays all employees who like other employees.

Example 31.14

USE graph_db;

SELECT E1.name AS SourceName, E2.name AS TargetName

FROM Employee E1, Is_Liked, Employee E2

WHERE MATCH(E1-(Is_Liked)->E2);

The result is

The syntax of the SELECT statement in Example 31.14 is similar to the syntax of the same statement in previous examples. The only difference is that you have to use at least one alias for the Employee node table because that name appears twice in the query.

Example 31.15

— Display all employees that like Matthew Smith

USE graph_db;

SELECT E2.name AS FriendName

FROM Employee E1, Is_Liked, Employee E2

WHERE MATCH(E1-(Is_Liked)->E2)

AND E1.name = ‘Matthew Smith’;

The result is

The following example retrieves all employees who are liked by Matthew Smith.

Example 31.16

— Display all employees who are liked by Matthew Smith

USE graph_db

SELECT E2.name AS FriendName

FROM Employee E1, Is_Liked, Employee E2

WHERE MATCH(E2-(Is_Liked)->E1)

AND E1.name = ‘Matthew Smith’;

The result of Example 31.16 does not contain any rows. If you take a closer look at Examples 31.15 and 31.16, you will see that relationship is between the same nodes, one moving from left to right and the other from right to left. Semantically, the results of these two examples tell us that three employees in the company like Matthew Smith (Example 31.15), but Matthew does not like any other employee (Example 31.16).

Example 31.17 shows how you can specify “second-level” likes.

Example 31.17

— Display all employees who like employees that like Ann Jones

USE graph_db;

SELECT Person3.name AS FriendName

FROM Employee Person1, Employee Person2,

Is_Liked, Is_Liked Is_Liked2, Employee Person3

WHERE MATCH(Person1-(Is_Liked)->Person2-(Is_Liked2)->Person3)

AND Person1.name = ‘Ann Jones’;

The result is

Example 31.17 also shows, generally, how you can navigate through a graph as deep as you want. Each time you reference the next sublevel of the graph, you have to add the name of the edge table, together with the name of the self-referencing node table, first in the FROM clause of the query and, after that, in the MATCH function, appending both names or their aliases to the single parameter of this function.

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.

Leave a Reply

Your email address will not be published. Required fields are marked *