Syntax to query for optional relationships in Microsoft SQL Server 2017 Graph Database?

Question

I want to select optional relationships in sql-server-2017-graph. Similar to optional in sparql e.g.:

PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT ?name ?mbox
WHERE  { ?x foaf:name  ?name .
     OPTIONAL { ?x  foaf:mbox  ?mbox }
   }

from https://www.w3.org/2001/sw/DataAccess/rq23/#OptionalMatching.

And similar to LEFT JOIN in normal sql; e.g.:

SELECT name, mbox
FROM Persons
LEFT JOIN PersonMailBoxLink ON Persons.$node_id = PersonMailBoxLink.$from_id
LEFT JOIN MailBoxes ON PersonMailBoxLink.$to_id = MailBoxes.$node_id

Is there an easier way via MATCH?

The documentation of MATCH describes no 'optional' construct and the remarks state:

OR and NOT operators are not supported in the MATCH pattern. MATCH can be combined with other expressions using AND in the WHERE clause. However, combining it with other expressions using OR or NOT is not supported.

Kasper van den Berg · Accepted Answer · 2018-04-19T10:03:33.023

You can combine LEFT JOIN with MATCH. Put the optional MATCH in a separate nested query. Put the optional subquery in a LEFT JOIN-clause.

The query is a bit cumbersome. The main graph search pattern and the optional graph search pattern need separate Node-tables to use the graph MATCH-syntax. A third instance of the Node-table is needed to LEFT JOIN the optional clause on. This third Node-table instance must be separate from the Node-table used to MATCH the main query part on since using MATCH requires¹ a table_or_view_name and cannot use a <joined_table>.

The OP example has no main graph search pattern, so there is little benefit from using a nested JOIN. However, this would be the resulting query:

SELECT [pLhs].[name],
    [mbox]
FROM [Persons] as [pLhs]
LEFT JOIN (
    SELECT [pRhs].$node_id AS [pRhsNodeId],
        [mbox]
    FROM [Persons] as [pRhs]
        [PersonMailBoxLink],
        [MailBoxes]
    WHERE MATCH ([Persons]-([PersonMailBoxLink])->[MailBoxes])
) AS [optionalGsp] ON [pLhs].$node_id = [optionalGsp].[pRhsNodeId];

A more extended example with both a main graph search pattern and an optional graph search pattern give a better demonstration of combing graph MATCH with optional LEFT JOIN. The following uses the SQL Graph Sample Database; select John's friends and optionally the restaurants that these friends like:

SELECT [Person].[Name] as friend,
    [optionalGsp].[resaurantName],
FROM [Person] AS person1,
    [Person] AS person2,
    [friendOf],
    [Person] AS person2Lhs
    LEFT JOIN (
        SELECT person2Rhs.$node_id AS rhsNodeId,
            [Restaurant].[Name] AS restaurantName
        FROM [Person] AS person2Rhs,
            [likes],
            [Restaurant]
        WHERE MATCH (person2Rhs-(likes)->Restaurant)
    ) AS optionalGsp
WHERE MATCH (person1-(friendOf)->person2)
AND person1.name = 'John'
AND person2.$node_id = person2Lhs.$node_id

In the original sample database every person likes a restaurant, so the is no difference between the complicate query above and MATCH(person1-(friendOf)->person2-(likes)->Restaurant). However, when you delete Sally liking Ginger and Spice:

DELETE FROM likes
WHERE $from_id = (SELECT $node_id FROM Persons WHERE name = 'Sally')
AND $to_id = (SELECT $node_id FROM Restaurants WHERE name = 'Ginger and Spice')

The query with the optional LEFT JOIN still returns Sally as a friend of John. The results show NULL for Sally's restaurant. MATCH(person1-(friendOf)->person2-(likes)->Restaurant) does not show Sally.

¹ _{MATCH §Arguments and Use views and table valued functions as node or edge tables in match clauses describe this restriction on the tables that can be used in the MATCH-clause.}

Syntax to query for optional relationships in Microsoft SQL Server 2017 Graph Database?

1 Answers1

Linked