跳转至

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边第一步查询的结果列和第二步查询的起点列进行匹配，然后基于匹配结果返回name和age。

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列（player101和player125两个 ID）进行匹配。匹配的结果集为两个查询结果中dst和src列都包含的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列（player101到player100的起点 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关联LOOKUP、GO和FIND PATH子句的查询的过程示例步骤：

  1. 通过LOOKUP ON语句，先对player表进行查询，找到名为Tony Parker的球员顶点，并将其 ID 和属性存储到$a.src和v列中。
  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")> |
...
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-----------------------------------------------------------------------------+
最后更新: May 9, 2024