在Microsoft SQL Server 2017图形数据库中查询可选关系的语法？ [英] Syntax to query for optional relationships in Microsoft SQL Server 2017 Graph Database?

问题描述

我想在 sql-server-2017-graph 。类似于可选在 sparql 例如：

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 < a href =https://www.w3.org/2001/sw/DataAccess/rq23/#OptionalMatching =nofollow noreferrer> https://www.w3.org/2001/sw/DataAccess/rq23/ #OptionalMatching 。

类似于 > LEFT JOIN 。 / q​​uestion / tagged / sqlclass =post-tagtitle =显示标记为'sql'的标签'rel =tag> sql ;例如：

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

通过 MATCH ？

MATCH 描述了可选构造和 remarks state：

t不支持

OR 和 NOT 运算符他 MATCH 模式。 MATCH 可以在 WHERE AND 与其他表达式结合使用>条款。但是，不支持使用 OR 或 NOT 与其他表达式结合使用。

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.

推荐答案

您可以将 LEFT JOIN 与 MATCH 。把可选的 MATCH 在一个单独的嵌套查询中。将可选子查询放在 LEFT JOIN -clause。

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.

查询是有点麻烦。主要图形搜索模式和可选图形搜索模式需要单独 Node - 要使用的表图 MATCH - 语法。 Node -table的第三个实例需要 LEFT JOIN 上的可选子句。这第三个 Node -table实例必须独立于 Node -table用于 MATCH 自从使用 MATCH 之后的主查询部分需要 1 a table_or_view_name 并且不能使用 < joined_table> 。

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>.

OP示例没有主图搜索模式，所以使用嵌套的 JOIN 几乎没有什么好处。但是，这可能是由此产生的查询：

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];

具有主图搜索模式和可选图搜索模式的扩展示例提供了更好的演示使用可选的 LEFT JOIN 结合图 MATCH 。以下使用 SQL图表示例数据库;选择John的朋友以及这些朋友喜欢的餐厅：

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

在最初的示例数据库一个餐厅，所以在上面的复杂查询和 MATCH（person1-（friendOf） - > person2-（likes） - > Restaurant）之间没有区别。然而，当你删除Sally喜欢姜和香料时：

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')

具有可选 LEFT JOIN 的查询仍返回Sally作为 John 。结果显示Sally餐厅的 NULL 。 MATCH（person1-（friendOf） - > person2-（likes） - > Restaurant）不会显示莎莉

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和使用视图和表值函数作为匹配子句中的节点或边缘表在可以使用的表上描述此限制在 MATCH -clause中。}

¹ _{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.}

这篇关于在Microsoft SQL Server 2017图形数据库中查询可选关系的语法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！