跳转至

INNER JOIN

INNER JOIN是一种连接查询。它基于两个表之间的共同列值进行匹配,并返回满足条件的记录。INNER JOIN通常用于根据关联列的值来创建一个包含两个表的结果集。在 NebulaGraph 中,可以显示地使用INNER JOIN子句在两个表之间进行连接查询,以获取更加复杂的查询结果。

Note

在 nGQL 语句中,GO的多跳查询隐式地使用了INNER JOIN子句。例如,GO 1 TO 2 STEPS FROM "player101" OVER follow YIELD $$.player.name AS name, $$.player.age AS age语句中,GO子句隐式地使用了INNER JOIN子句,将从player101出发沿follow边第一步查询的结果列和第二步查询的起点列进行匹配,然后基于匹配结果返回nameage

openCypher 兼容性

INNER JOIN子句仅适用于原生 nGQL 语法。

语法

YIELD <column_name_list>
FROM <first_table> INNER JOIN <second_table> ON <join_condition>

使用说明

  • 必须使用YIELD子句指定返回的列,并且YIELD子句需放置在INNER JOIN子句之前。
  • 使用FROM子句指定要连接的两个表。
  • INNER JOIN子句必须包含ON子句,ON子句指定了连接条件,并且连接条件只支持等值连接(即==)。
  • <first_table><second_table>是要连接的两个表,两表名不能相同。
  • 使用自定义变量来指定表名。详情参见使用自定义变量

使用示例

以下示例介绍如何通过INNER JOIN关联 nGQL 语句中的两个查询结果。

示例一:通过INNER JOIN将第一个LOOKUP查询中的结果dst列(Tony Parker的 ID player101)和第二个GO查询中的结果src列(player101player125两个 ID)进行匹配。匹配的结果集为两个查询结果中dstsrc列都包含的player101,基于这个player101匹配的结果,然后通过YIELD $b.vid AS vid, $a.v AS v, $b.e2 AS e2返回最终查询结果。

nebula> $a = LOOKUP ON player WHERE player.name == 'Tony Parker' YIELD id(vertex) as dst, vertex AS v; \
        $b = GO FROM 'player101', 'player125' OVER follow YIELD id($^) as src, id($$) as vid, edge AS e2; \
        YIELD $b.vid AS vid, $a.v AS v, $b.e2 AS e2 FROM $a INNER JOIN $b ON $a.dst == $b.src;
+-------------+-----------------------------------------------------+----------------------------------------------------+
| vid         | v                                                   | e2                                                 |
+-------------+-----------------------------------------------------+----------------------------------------------------+
| "player100" | ("player101" :player{age: 36, name: "Tony Parker"}) | [:follow "player101"->"player100" @0 {degree: 95}] |
| "player102" | ("player101" :player{age: 36, name: "Tony Parker"}) | [:follow "player101"->"player102" @0 {degree: 90}] |
| "player125" | ("player101" :player{age: 36, name: "Tony Parker"}) | [:follow "player101"->"player125" @0 {degree: 95}] |
+-------------+-----------------------------------------------------+----------------------------------------------------+

示例二:通过INNER JOIN将第一个LOOKUP查询中的结果src列(Tony Parker的 ID player101)和第二个FETCH查询中的结果src列(player101player100的起点 ID player101)进行匹配。匹配的结果集为都包含的player101,基于这个player101匹配的结果,然后通过YIELD $a.src AS src, $a.v AS v, $b.e AS e返回最终查询结果。

nebula> $a = LOOKUP ON player WHERE player.name == 'Tony Parker' YIELD id(vertex) as src, vertex AS v; \
        $b = FETCH PROP ON follow 'player101'->'player100' YIELD src(edge) as src, edge as e; \
        YIELD $a.src AS src, $a.v AS v, $b.e AS e FROM $a INNER JOIN $b ON $a.src == $b.src;
+-------------+-----------------------------------------------------+----------------------------------------------------+
| src         | v                                                   | e                                                  |
+-------------+-----------------------------------------------------+----------------------------------------------------+
| "player101" | ("player101" :player{age: 36, name: "Tony Parker"}) | [:follow "player101"->"player100" @0 {degree: 95}] |
+-------------+-----------------------------------------------------+----------------------------------------------------+

示例三:使用INNER JOIN关联LOOKUPGOFIND PATH子句的查询的过程示例步骤:

  1. 通过LOOKUP ON语句,先对player表进行查询,找到名为Tony Parker的球员顶点,并将其 ID 和属性存储到$a.srcv列中。
  2. 再通过GO查找从$a.src节点出发经过follow边往外走 2-5 步的球员节点,其中要求这些节点对应的player点年龄大于 30 岁,返回这些节点的 ID,并将它们存储在$b.dst列中。
  3. 然后通过FIND ALL PATH语句,查找从$a.src$b.dst的沿follow所有路径,并返回路径$c.p以及路径中的终点$c.dst
  4. 最后通过FIND SHORTEST PATH语句,查找从$c.dst$a.src的最短路径,并返回路径$d.p和路径起点$d.src
  5. 使用INNER JOIN将 3 和 4 中得到的结果联接起来,匹配$c.dst$d.src列,为每个联接匹配的记录返回YIELD $c.forward AS forwardPath, $c.dst AS end, $d.p AS backwordPath
nebula> $a = LOOKUP ON player WHERE player.name == 'Tony Parker' YIELD id(vertex) as src, vertex AS v; \
        $b = GO 2 TO 5 STEPS FROM $a.src OVER follow WHERE $$.player.age > 30 YIELD id($$) AS dst; \
        $c = (FIND ALL PATH FROM $a.src TO $b.dst OVER follow YIELD path AS p | YIELD $-.p AS forward, id(endNode($-.p)) AS dst); \
        $d = (FIND SHORTEST PATH FROM $c.dst TO $a.src OVER follow YIELD path AS p | YIELD $-.p AS p, id(startNode($-.p)) AS src); \
        YIELD $c.forward AS forwardPath, $c.dst AS end, $d.p AS backwordPath FROM $c INNER JOIN $d ON $c.dst == $d.src;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-----------------------------------------------------------------------------+
| forwardPath                                                                                                                                                           | end         | backwordPath                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-----------------------------------------------------------------------------+
| <("player101")-[:follow@0 {}]->("player102")>                                                                                                                         | "player102" | <("player102")-[:follow@0 {}]->("player101")>                               |
| <("player101")-[:follow@0 {}]->("player100")-[:follow@0 {}]->("player101")-[:follow@0 {}]->("player102")>                                                             | "player102" | <("player102")-[:follow@0 {}]->("player101")>                               |
| <("player101")-[:follow@0 {}]->("player125")>                                                                                                                         | "player125" | <("player125")-[:follow@0 {}]->("player100")-[:follow@0 {}]->("player101")> |
| <("player101")-[:follow@0 {}]->("player100")-[:follow@0 {}]->("player125")>                                                                                           | "player125" | <("player125")-[:follow@0 {}]->("player100")-[:follow@0 {}]->("player101")> |
| <("player101")-[:follow@0 {}]->("player100")-[:follow@0 {}]->("player101")-[:follow@0 {}]->("player125")>                                                             | "player125" | <("player125")-[:follow@0 {}]->("player100")-[:follow@0 {}]->("player101")> |
| <("player101")-[:follow@0 {}]->("player102")-[:follow@0 {}]->("player100")-[:follow@0 {}]->("player125")>                                                             | "player125" | <("player125")-[:follow@0 {}]->("player100")-[:follow@0 {}]->("player101")> |
| <("player101")-[:follow@0 {}]->("player102")-[:follow@0 {}]->("player101")-[:follow@0 {}]->("player125")>                                                             | "player125" | <("player125")-[:follow@0 {}]->("player100")-[:follow@0 {}]->("player101")> |
...
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-----------------------------------------------------------------------------+

最后更新: September 6, 2024