{"id":77704,"date":"2018-03-22T19:27:39","date_gmt":"2018-03-22T19:27:39","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=77704"},"modified":"2021-09-29T16:21:06","modified_gmt":"2021-09-29T16:21:06","slug":"sql-server-graph-databases-part-2-querying-data-graph-database","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-server-graph-databases-part-2-querying-data-graph-database\/","title":{"rendered":"SQL Server Graph Databases \u2013 Part 2: Querying Data in a Graph Database"},"content":{"rendered":"

The series so far:<\/h4>\n
    \n
  1. SQL Server Graph Databases - Part 1: Introduction<\/a><\/li>\n
  2. SQL Server Graph Databases - Part 2: Querying Data in a Graph Database<\/a><\/li>\n
  3. SQL Server Graph Databases - Part 3: Modifying Data in a Graph Database<\/a><\/li>\n
  4. SQL Server Graph Databases - Part 4: Working with hierarchical data in a graph database<\/a><\/li>\n
  5. SQL Server Graph Databases - Part 5:\u00a0Importing Relational Data into a Graph Database<\/a><\/li>\n<\/ol>\n\n

    Microsoft incorporated the graph database in SQL Server 2017, providing a logical structure for storing and querying data sets that contain complex many-to-many or hierarchical relationships. The first article in this series introduced you to the basics of graph databases and described how to define node and edge tables and populate them with data. In this article, we turn to the querying side of the equation, with a focus on retrieving related data in multiple node tables.<\/p>\n

    For the examples in this article, I used the FishGraph<\/strong> database from the first article in this series, except that I added more sample data. The database is based on a fictitious fish-lovers forum and includes three node tables (FishSpecies<\/strong>, FishLover<\/strong>, and FishPost<\/strong>) and three edge tables (Likes<\/strong>, Posts<\/strong>, and LinksTo<\/strong>). The following figure shows the data model used to build the database.<\/p>\n

    <\/p>\n

    The rectangles represent the nodes, and the arrows connecting the nodes represent the edges, with the arrows pointing in the direction of the relationships. You can download the T-SQL script used to create and populate the database at the bottom of this article.<\/p>\n

    Introducing the MATCH Function<\/h2>\n

    For the most part, querying tables in a graph database works much the same way as querying regular relational tables, although there are some limitations, such as not being able to perform cross-database queries on graph tables. (For more information about graph database limitations, refer to the Microsoft document SQL Graph Architecture<\/a>.)<\/p>\n

    Despite the limitations, you should find that most queries work as expected. For example, the following SELECT<\/strong> statement joins the FishLover<\/strong>, Likes<\/strong>, and FishSpecies<\/strong> tables in order to retrieve a list of users who like certain fish species:<\/p>\n

    SELECT fl.Username, fs.CommonName, fs.ScientificName\r\n  FROM FishLover fl INNER JOIN Likes lk\r\n      ON fl.$node_id = lk.$from_id\r\n    INNER JOIN FishSpecies fs\r\n      ON lk.$to_id = fs.$node_id;<\/pre>\n
    The SELECT<\/strong> statement uses the $node_id<\/strong>, $from_id<\/strong>, and $to_id<\/strong> column aliases to join the tables and return the data shown in the following figure<\/p>\n
    <\/p>\n
    As you can see, querying graph tables is a fairly straightforward process, especially when you take advantage of the graph column aliases. Of course, if you\u2019re trying to retrieve data based on more complex relationships, the query itself also becomes more complex and can even get a bit unwieldy. For this reason, Microsoft has added the MATCH<\/strong> function for retrieving related data from graph tables.<\/p>\n
    The MATCH<\/strong> function lets you define a search pattern based on the relationships between nodes. You can use the function only in the WHERE<\/strong> clause of a SELECT<\/strong> statement that queries node and edge tables. The following syntax shows the elements that go into defining the search pattern:<\/p>\n
    MATCH(<search_pattern>)\r\n<search_pattern>::=\r\n    <node_alias>\r\n    { -( <edge_alias> )-> | <-( <edge_alias> )- }\r\n    <node_alias>\r\n    [ { AND <search_pattern> } [ ...n ] ]\r\n  <node_alias> ::=\r\n    node_table_name | node_alias \r\n  <edge_alias> ::=\r\n    edge_table_name | edge_alias<\/pre>\n
    A search pattern can define one or more relationships. For each relationship, you must identify the originating and terminating nodes, as well as the edge that ties the two nodes together. You must also specify the direction of the relationship, using dashes and arrows, with the edge situated between the two nodes. For example, if you want to define a single relationship that originates with node1<\/strong> and terminates with node2<\/strong>, you would use the following syntax for your WHERE<\/strong> clause:<\/p>\n
    WHERE MATCH(node1-(edge)->node2)<\/pre>\n
    Notice that the edge is enclosed in parentheses, with a dash preceding the edge, and a dash and right arrow following the edge. This defines a relationship that moves from left-to-right. You can reverse this order by specifying node1<\/strong> on the right side of the search pattern and node2<\/strong> on the left side, with the arrow pointing in the opposite direction:<\/p>\n
    WHERE MATCH(node2<-(edge)-node1)<\/pre>\n
    In either case, the search pattern indicates that the WHERE<\/strong> clause should return only those rows in which a relationship exists between node1<\/strong> (the originating node) and node2<\/strong> (the terminating node), as defined in the edge<\/strong> element.<\/p>\n
    With these basics in mind, you can rewrite the SELECT<\/strong> statement above to simplify the query:<\/p>\n
    SELECT Lover.Username, Species.CommonName, Species.ScientificName\r\nFROM FishLover Lover, Likes, FishSpecies Species\r\nWHERE MATCH(Lover-(Likes)->Species);<\/pre>\n
    In the FROM<\/strong> clause, you simply list the participating tables \u2013 without the ON<\/strong> clause– providing table aliases where appropriate. You can then reference the aliases in the search pattern of the MATCH<\/strong> function. In this case, the search pattern defines the relationship fish lover likes a fish species<\/em>. The statement will return the same results as those returned by the SELECT<\/strong> statement above.<\/p>\n
    You can also reverse the order of the relationship so that it is defined from right-to-left:<\/p>\n
    SELECT Lover.Username, Species.CommonName, Species.ScientificName\r\nFROM FishLover Lover, Likes, FishSpecies Species\r\nWHERE MATCH(Species<-(Likes)-Lover);<\/pre>\n
    Again, the SELECT<\/strong> statement returns the same results as the previous two SELECT<\/strong> statements.<\/p>\n
    You can include other elements in the WHERE<\/strong> clause, in addition to the MATCH<\/strong> function. For example, the following WHERE<\/strong> clause adds a search condition specifying that only rows with a Username<\/strong> value of hooked<\/strong> should be returned:<\/p>\n
    SELECT Species.CommonName, Species.ScientificName\r\nFROM FishLover Lover, Likes, FishSpecies Species\r\nWHERE MATCH(Lover-(Likes)->Species)\r\n  AND Lover.Username = 'hooked';<\/pre>\n
    The statement now returns the results shown in the following figure.<\/p>\n
    <\/p>\n
    As you can see, using the MATCH<\/strong> function to define a search pattern based on a single relationship is a fairly straightforward process. In many cases, however, you\u2019ll want to return data based on multiple relationships, which is where the function can be particularly handy.<\/p>\n
    Creating Compound MATCH Expressions<\/h2>\n
    If you refer back to the syntax for the MATCH<\/strong> function, you\u2019ll notice that you can use the AND<\/strong> operator when defining your search pattern, allowing you to string together multiple relationships within a single expression. Note, however, that the MATCH<\/strong> function does not support the OR<\/strong> operator or the NOT<\/strong> operator, so the logic you can define is somewhat limited. Even so, the AND<\/strong> operator can still be very useful. For example, the following search pattern uses the operator to string together two relationships:<\/p>\n
    SELECT Lover.Username, Post.Title, Species.CommonName\r\nFROM FishLover Lover, Likes, FishPost Post, LinksTo, FishSpecies Species\r\nWHERE MATCH(Lover-(Likes)->Post AND Post-(LinksTo)->Species);<\/pre>\n
    For the SELECT<\/strong> statement to return a row, a fish lover must like a fish post and<\/em> the fish post must link to a fish species. In this case, the statement returns only two rows, as shown in the following figure.<\/p>\n
    <\/p>\n
    By being able to link together multiple relationships, you can dig more deeply into how the nodes in a graph database are interconnected. For example, the preceding SELECT<\/strong> statement might help to determine whether users are more inclined to like a post that specifically links to a fish species.<\/p>\n
    In some cases, you can link together relationships without using the AND<\/strong> operator, as long as your search pattern defines the same logic. For instance, you can rewrite the preceding SELECT<\/strong> statement by eliminating the AND<\/strong> operator and one of the references to the Post<\/strong> table alias:<\/p>\n
    SELECT Lover.Username, Post.Title, Species.CommonName\r\nFROM FishLover Lover, Likes, FishPost Post, LinksTo, FishSpecies Species\r\nWHERE MATCH(Lover-(Likes)->Post-(LinksTo)->Species);<\/pre>\n
    The SELECT<\/strong> statement returns the same results as the preceding one, even though the search pattern has been simplified. You can also define a search pattern that contains two relationships terminating with the same node, as in the following example:<\/p>\n
    SELECT Lover.Username, Species.CommonName, Post.Title\r\nFROM FishLover Lover, Likes, FishSpecies Species, FishPost Post, LinksTo\r\nWHERE MATCH(Lover-(Likes)->Species<-(LinksTo)-Post);<\/pre>\n
    The first relationship (fish lover likes a fish species<\/em>) is defined from left-to right, and the second relationship (fish post links to a fish species<\/em>) is defined from right-to-left. As a result, the SELECT<\/strong> statement returns only those rows in which a fish species is both liked and linked to, giving us the results shown in the following figure.<\/p>\n
    <\/p>\n
    You can also define search patterns that include more than two relationships, using the AND<\/strong> operator where appropriate. For example, the search pattern in the following SELECT<\/strong> statement includes three relationships but only one instance of the AND<\/strong> operator:<\/p>\n
    SELECT Lover.Username, Post.Title, Species.CommonName\r\nFROM FishLover Lover, Likes Likes1, FishPost Post, \r\n  LinksTo, FishSpecies Species, Likes Likes2\r\nWHERE MATCH(Lover-(Likes1)->Post-(LinksTo)->Species \r\n  AND Lover-(Likes2)->Species);<\/pre>\n
    Because the Likes<\/strong> table is referenced twice within the search pattern, it must be included twice in the FROM<\/strong> clause, with a different alias assigned to each instance. The search pattern then uses these aliases when defining the three relationships (fish lover likes a fish post, fish post links to a fish species,<\/em> and fish lover likes a fish species<\/em>). The SELECT<\/strong> statement will return only those rows in which a fish species is both liked and linked to from a post that is liked, as shown in the following figure.<\/p>\n
    In this case, the user underwatercasey<\/strong> likes both the fish post and fish species, and the fish post links to the fish species. You can also rewrite the search pattern to eliminate the AND<\/strong> operator altogether:<\/p>\n
    SELECT Lover.Username, Post.Title, Species.CommonName\r\nFROM FishLover Lover, Likes Likes1, FishPost Post, \r\n  LinksTo, FishSpecies Species, Likes Likes2\r\nWHERE MATCH(Lover-(Likes1)->Post-(LinksTo)->Species<-(Likes2)-Lover);<\/pre>\n
    The SELECT<\/strong> statement returns the same results as the preceding statement but simplifies the search pattern. In some cases, however, you might find that the AND<\/strong> operator makes it easier to read and troubleshoot your code as you heap on more and more relationships.<\/p>\n
    Defining a Self-referencing Query<\/h2>\n
    Because of the way in which a graph database is structured, it is just as easy to perform a self-referencing query as any other type of query. For example, the following SELECT<\/strong> statement returns a list of fish posts that link to other posts:<\/p>\n
    SELECT Post1.Title Title1, Post2.Title Title2\r\nFROM FishPost Post1, LinksTo, FishPost Post2\r\nWHERE MATCH(Post1-(LinksTo)->Post2);<\/pre>\n
    To define the relationship in the search pattern, you must include two instances of the FishPost<\/strong> table in the FROM<\/strong> clause, assigning a different alias to each instance, similar to how you included multiple instances of the Likes<\/strong> edge table in the preceding two examples. The SELECT<\/strong> statement returns the results shown in the following figure.<\/p>\n
    <\/p>\n
    You can use the same logic to determine which fish lovers like other fish lovers:<\/p>\n
    SELECT Lover1.Username User1, Lover2.Username User2\r\nFROM FishLover Lover1, Likes, FishLover Lover2\r\nWHERE MATCH(Lover1-(Likes)->Lover2);<\/pre>\n
    This time the FROM<\/strong> clause includes two instances of the FishLover<\/strong> table, with a unique alias assigned to each one, giving us the results shown in the following figure.<\/p>\n
    <\/p>\n
    You can also use this approach when defining more than two relationships in your search pattern. For example, the following SELECT<\/strong> statement returns a list of users that are liked by user hooked<\/strong>, along with the users that they like:<\/p>\n
    SELECT Lover2.Username User2, Lover3.Username User3\r\nFROM FishLover Lover1, Likes Likes1, FishLover Lover2, \r\n  Likes Likes2, FishLover Lover3\r\nWHERE MATCH(Lover1-(Likes1)->Lover2-(Likes2)->Lover3)\r\n  AND Lover1.Username = 'hooked';<\/pre>\n
    The FROM<\/strong> clause now includes three instances of the FishLover<\/strong> table and two instances of the Likes<\/strong> table. The search pattern uses these instances to define the relationships fish lover1 likes fish lover2<\/em> and fish lover2 likes fish lover3,<\/em> giving us the results shown in the following figure.<\/p>\n
    <\/p>\n
    Essentially what we have here is a friend-of-a-friend type scenario:<\/p>\n