对父子表使用内部联接

JOIN 用于基于两个或更多表之间的相关列组合行。在分层表中，子表会继承其父表的主键列。隐式执行此操作，但不会在子项的 CREATE TABLE 语句中包含父列。层次结构中的所有表都具有相同的分片关键字列。

内部联接是用于组合 Oracle NoSQL Database Cloud Service 中属于同一表层次结构的表的一种联接类型。

• 内部联接概览

• 使用内部联接的示例

内部联接概览

内部联接是通过组合两个或更多表中的行来生成新行的操作，该组合基于应用于它们之间的相关列或字段的联接谓词。结果集仅包含满足联接谓词的组合行。

从概念上讲，内部联接的作用如下：

考虑一下，需要执行三个表 A、B 和 C 的内部联接。首先联接表 A 和表 B。也就是说，如果表 A 包含 N 行和 n 列，而表 B 包含 M 行和 m 列，则表 A 中的每一行都会与表 B 中的每一行相联接。因此，结果表 AB 将具有 (N * M) 行和 (n + m) 列。同样，表 AB 现在也与表 C 联接在一起，形成表 ABC。然后，WHERE 子句中的联接谓词应用于表 ABC。请注意，联接谓词必须包括联接表的所有分片关键字列之间的等号谓词。最终结果集仅包含参与表中的匹配行。

在 SELECT 语句的 FROM 子句中指定要联接的表，在 WHERE 子句中指定联接谓词。联接谓词是一种谓词，它引用一个或多个要联接的表中的列或字段，并指定需要对其应用过滤条件。对于内部联接，WHERE 子句必须包含参与表的所有分片关键字的等号谓词。

如果将‘ * ’与‘ SELECT ’子句结合使用，返回表中的所有字段，则结果集中的字段顺序取决于您在 FROM 子句中指定的表的顺序。如果在 SELECT 子句中提供了字段列表，则结果集中的字段顺序将如 SELECT 子句中指定的那样。

执行内部联接时，以下各项适用：

• 只允许在同一表层次中的表之间建立联接。

• 支持联接祖先 - 后代关系中的表以及不属于祖先 - 后代关系的表。

• 联接谓词必须包括联接表的所有分片关键字列之间的等号谓词。要了解有关分片密钥的更多信息，请参见 CREATE TABLE 。也就是说，对于任何一对联接的表，一个表中的一行只与另一个表中的一行匹配，但前提是它们在分片键列上的值相同。可以使用 DESCRIBE TABLE 语句标识分片键。

• WHERE 子句中的其余谓词将应用于这些匹配行。

内部联接不同于 NESTED TABLES 和 left outer join ，主要在以下方面：

• 内部联接基于匹配参与表的分片键，而 NESTED TABLES 和左外部联接基于匹配参与表的主键。

• 内部联接的结果集仅包含匹配行。而对于 NESTED TABLES 和左外部联接，左表中不匹配的行也会在结果集中返回，右表中有对应的 NULL 行。

• 内部联接可用于联接不在祖先 - 后代关系中的表。如果存在左外部联接和 NESTED_TABLES，则无法实现此目的。有关更多详细信息，请参阅内部联接与 LOJ 与 NESTED 表。

从本质上讲，可以使用这三种类型的联接中的任意一种来联接具有它们之间的祖先 - 后代关系的表。您可以根据您的用例选择使用其中一个。如果要联接的表不在祖先 - 后代关系中，则必须使用内部联接。

使用内部联接的示例

考虑使用航空公司行李跟踪应用程序。对于每个机票号码，有一个乘客和他们的行李与它有关。根表为 ticket，它具有 2 个子表 passengerInfo 和 baggageInfo。passengerInfo 表包含乘客的详细信息，baggageInfo 包含乘客签入的包的详细信息。这些袋子通过多个中介站的运输进行跟踪。此跟踪信息捕获在名为 flightlegs 的表中，该表是 baggageInfo 表的子项。

下载脚本 parentchildtbls_loaddata.sql 并按如下所示运行该脚本。此脚本将创建示例中使用的表，并将数据加载到表中。

• 启动 KVSTORE 或 KVLite

  java -jar lib/kvstore.jar kvlite -secure-config disable

• 打开 SQL shell

  java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

  将显示 SQL 提示符。

• 加载 DDL 文件以创建示例中使用的必需表

  load -file parentchild.ddl

• 使用 load 命令运行脚本。JSON 文件中的数据将加载到表中。

  load -file parentchildtbls_loaddata.sql

parentchildtbls_loaddata.sql 包含以下内容：

### Begin Script ###
load -file parentchild.ddl
import -table ticket -file ticket.json
import -table ticket.bagInfo -file bagInfo.json
import -table ticket.passengerInfo -file passengerInfo.json
import -table ticket.bagInfo.flightLegs -file flightLegs.json
### End Script ###

下面是创建的表：

• Ticket (票证)

  ticketNo LONG
  confNo STRING
  PRIMARY KEY(ticketNo)

• 票务信息

  id LONG
  tagNum LONG
  routing STRING
  lastActionCode STRING
  lastActionDesc STRING
  lastSeenStation STRING
  lastSeenTimeGmt TIMESTAMP(4)
  bagArrivalDate TIMESTAMP(4)
  PRIMARY KEY(id)

• 机票 .bagInfo.flightLegs

  flightNo STRING
  flightDate TIMESTAMP(4)
  fltRouteSrc STRING
  fltRouteDest STRING
  estimatedArrival TIMESTAMP(4)
  actions JSON
  PRIMARY KEY(flightNo)

• 车票。乘客信息

  contactPhone STRING
  fullName STRING
  gender STRING
  PRIMARY KEY(contactPhone)

  SQL 示例

现在，让我们看到一些用于内部联接的 SQL 查询示例：

示例 1 - 获取票号为 1762324912391 的乘客的详细信息。

SELECT fullname, contactPhone, gender FROM ticket a,ticket.passengerInfo b WHERE
      a.ticketNo=b.ticketNo AND a.ticketNo=1762324912391

解释：这是内部联接的一个示例，其中父表票证与其子表乘客信息联接，并应用过滤器来限制结果。请注意，此处的分片密钥为 ticketNo。如果在创建根表时未显式指定分片键，则将根表的主键作为分片键。此分片键由所有后代表继承。

输出：

{"fullname":"Elane Lemons","contactPhone":"600-918-8404","gender":"F"}
1 row returned

示例 2 - 获取所有已签发机票的乘客的包裹详细信息。

SELECT * FROM ticket a, ticket.bagInfo b WHERE a.ticketNo=b.ticketNo

解释：这是内部联接的一个示例，其中父表 ticket 与其子表 bagInfo 联接。

输出：

{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"},"b":{"ticketNo":1762324912391,"id":79039899168383,"tagNum":1765780623244,"routing":"MXP/CDG/SLC/BZN","lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"BZN","lastSeenTimeGmt":"2019-03-15T10:13:00.0000Z","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}}
{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},"b":{"ticketNo":1762355527825,"id":79039899197492,"tagNum":17657806232501,"routing":"BZN/SEA/CDG/MXP","lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MXP","lastSeenTimeGmt":"2019-03-22T10:17:00.0000Z","bagArrivalDate":"2019-03-22T10:17:00.0000Z"}}
{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},"b":{"ticketNo":1762344493810,"id":79039899165297,"tagNum":17657806255240,"routing":"MIA/LAX/MEL","lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MEL","lastSeenTimeGmt":"2019-02-01T16:13:00.0000Z","bagArrivalDate":"2019-02-01T16:13:00.0000Z"}}
{"a":{"ticketNo":1762376407826,"confNo":"ZG8Z5N"},"b":{"ticketNo":1762376407826,"id":7903989918469,"tagNum":17657806240229,"routing":"JFK/MAD","lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"MAD","lastSeenTimeGmt":"2019-03-07T13:51:00.0000Z","bagArrivalDate":"2019-03-07T13:51:00.0000Z"}}
{"a":{"ticketNo":1762392135540,"confNo":"DN3I4Q"},"b":{"ticketNo":1762392135540,"id":79039899156435,"tagNum":17657806224224,"routing":"GRU/ORD/SEA","lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD","lastSeenStation":"SEA","lastSeenTimeGmt":"2019-02-15T21:21:00.0000Z","bagArrivalDate":"2019-02-15T21:21:00.0000Z"}}
5 rows returned

示例 3 - 获取票号为 1762344493810 的乘客包的航班行程详细信息。

SELECT * FROM ticket a, ticket.bagInfo.flightLegs b WHERE a.ticketNo=b.ticketNo AND
      a.ticketNo=1762344493810

解释：这是内部联接的示例，其中父表 ticket 与其后代 flightlegs 联接。后代表可以是表下的任何分层级别（例如，flightLegs 是 bagInfo 的子项，它是 ticket 的子项，因此 flightLegs 是 ticket 的子项）。然后，将针对特定票证编号筛选结果。

输出：

{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},"b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM604","flightDate":"2019-02-01T06:00:00.0000Z","fltRouteSrc":"MIA","fltRouteDest":"LAX","estimatedArrival":"2019-02-01T11:00:00.0000Z","actions":[{"actionAt":"MIA","actionCode":"ONLOAD to LAX","actionTime":"2019-02-01T06:13:00Z"},{"actionAt":"MIA","actionCode":"BagTag Scan at MIA","actionTime":"2019-02-01T05:47:00Z"},{"actionAt":"MIA","actionCode":"Checkin at MIA","actionTime":"2019-02-01T04:38:00Z"}]}}
{"a":{"ticketNo":1762344493810,"confNo":"LE6J4Z"},"b":{"ticketNo":1762344493810,"id":79039899165297,"flightNo":"BM667","flightDate":"2019-02-01T06:13:00.0000Z","fltRouteSrc":"LAX","fltRouteDest":"MEL","estimatedArrival":"2019-02-01T16:15:00.0000Z","actions":[{"actionAt":"MEL","actionCode":"Offload to Carousel at MEL","actionTime":"2019-02-01T16:15:00Z"},{"actionAt":"LAX","actionCode":"ONLOAD to MEL","actionTime":"2019-02-01T15:35:00Z"},{"actionAt":"LAX","actionCode":"OFFLOAD from LAX","actionTime":"2019-02-01T15:18:00Z"}]}}
2 rows returned

示例 4 - 查找票号为 1762355527825 的乘客的所有行李的跳跃数。如果一个乘客签入了多个行李，则会显示所有行李的跃点数。

SELECT b.id,count(*) AS NUMBER_HOPS FROM ticket a, ticket.bagInfo.flightLegs b WHERE a.ticketNo=b.ticketNo AND a.ticketNo=1762355527825 GROUP BY
    b.id

解释：在此处，您可以基于包 ID（使用 GROUP BY）对数据进行分组，并获取每个包的飞行腿数（使用 count()）。此外，还可以过滤特定票证编号的结果。

输出：

{"id":79039899197492,"NUMBER_HOPS":3}
1 row returned

示例 5 - 获取所有乘客的车票编号、乘客姓名和包裹详细信息。

SELECT a.ticketNo, b.fullName, c.bagArrivalDate FROM ticket a, ticket.passengerInfo b, ticket.bagInfo c WHERE a.ticketNo = b.ticketNo AND b.ticketNo=c.ticketNo

解释：这是三个表的内部联接示例，即父表 ticket 以及同级表 passengerInfo 和 bagInfo。

输出：

{"ticketNo":1762324912391,"fullName":"Elane Lemons","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}
{"ticketNo":1762355527825,"fullName":"Doris Martin","bagArrivalDate":"2019-03-22T10:17:00.0000Z"}
{"ticketNo":1762344493810,"fullName":"Adam Phillips","bagArrivalDate":"2019-02-01T16:13:00.0000Z"}
{"ticketNo":1762392135540,"fullName":"Adelaide Willard","bagArrivalDate":"2019-02-15T21:21:00.0000Z"}
{"ticketNo":1762376407826,"fullName":"Dierdre Amador","bagArrivalDate":"2019-03-07T13:51:00.0000Z"}
5 rows returned

示例 6 - 获取乘客姓名，其包袋的最后一个看到站是“MEL”

SELECT a.fullName FROM ticket.passengerInfo a, ticket.bagInfo b WHERE a.ticketNo = b.ticketNo AND b.lastSeenStation = "MEL"

解释：这是同级表 passengerInfo 和 bagInfo 的内部联接示例。最后一次在“MEL”车站看到的乘客的名字被退回。

输出：

{"fullName":"Adam Phillips"}
1 row returned

示例 7 - 获取航班航线目的地为“MEL”的乘客姓名

SELECT a.fullName FROM ticket.passengerInfo a, ticket.bagInfo.flightlegs b WHERE a.ticketNo = b.ticketNo AND b.fltRouteDest  = "MEL"

解释：这是两个表（passengerInfo 和 flightlegs）的内部联接，它们不在祖先 - 后代关系中。

输出：

{"fullName":"Adam Phillips"}

使用左外部联接和 NESTED TABLES ，无法在不属于祖先 - 后代关系的表之间建立此类联接。

查询 API 示例

• Java
• Python
• 开始
• Node.js
• C#

要执行查询，请使用 NoSQLHandle.query() API。

Download the full code TableJoins.java from the examples here.

/* fetch rows based on joins*/
private static void fetchRows(NoSQLHandle handle,String sql_stmt) throws Exception {
   try (
      QueryRequest queryRequest = new QueryRequest().setStatement(sql_stmt);
      QueryIterableResult results = handle.queryIterable(queryRequest)) {
         System.out.println("Query results:");
         for (MapValue res : results) {
            System.out.println("\t" + res);
         }
      }
}

/* fetching rows using inner join*/
String sql_stmt_innerjoin ="SELECT * FROM ticket a, ticket.bagInfo.flightLegs b WHERE a.ticketNo=b.ticketNo";
System.out.println("Fetching data using inner join:");
fetchRows(handle,sql_stmt_innerjoin);

要执行查询，请使用 borneo.NoSQLHandle.query() 方法。

Download the full code TableJoins.py from the examples here

# Fetch data from the table based on joins
def fetch_data(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   print('Query results for: ' + sqlstmt)
   result = handle.query(request)
   for r in result.get_results():
      print('\t' + str(r))

sql_stmt_ij='SELECT * FROM ticket a, ticket.bagInfo.flightLegs b WHERE a.ticketNo=b.ticketNo'
print('Fetching data using Inner Join ')
fetch_data(handle,sql_stmt_ij)

要执行查询，请使用 Client.Query 函数。

Download the full code TableJoins.go from the examples here.

func fetchData(client *nosqldb.Client, err error,
               tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{ Statement: querystmt,}

   prepRes, err := client.Prepare(prepReq)
   if err != nil {
      fmt.Printf("Prepare failed: %v\n", err)
      return
   }

   queryReq := &nosqldb.QueryRequest{
		 PreparedStatement: &prepRes.PreparedStatement,}
   var results []*types.MapValue

   for {
      queryRes, err := client.Query(queryReq)
      if err != nil {
         fmt.Printf("Query failed: %v\n", err)
	  return
      }
      res, err := queryRes.GetResults()

      if err != nil {
         fmt.Printf("GetResults() failed: %v\n", err)
  	return
      }

      results = append(results, res...)
      if queryReq.IsDone() {
         break
      }
   }
   for i, r := range results {
      fmt.Printf("\t%d: %s\n", i+1,
                  jsonutil.AsJSON(r.Map()))
   }
}

querystmt_ij:= "SELECT * FROM ticket a, ticket.bagInfo.flightLegs b WHERE a.ticketNo=b.ticketNo"
fmt.Println("Fetching data using Inner Join")
fetchData(client, err,querystmt_ij)

要执行查询，请使用 query 方法。

JavaScript：从此处的示例下载完整的代码 TableJoins.js。

//fetches data from the table
async function fetchData(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

const stmt_ij = 'SELECT * FROM ticket a, ticket.bagInfo.flightLegs b WHERE a.ticketNo=b.ticketNo';
console.log("Fetching data using Inner Join");
await fetchData(handle,stmt_ij);

TypeScript：从此处的示例下载完整代码 TableJoins.ts。

interface StreamInt {
   acct_Id: Integer;
   profile_name: String;
   account_expiry: TIMESTAMP;
   acct_data: JSON;
}

/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: string) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

const stmt_ij = 'SELECT * FROM ticket a, ticket.bagInfo.flightLegs b WHERE a.ticketNo=b.ticketNo';
console.log("Fetching data using Inner Join");
await fetchData(handle,stmt_ij);

要执行查询，可以调用 QueryAsync 方法或调用 GetQueryAsyncEnumerable 方法并迭代生成的异步枚举。

Download the full code TableJoins.cs from the examples here.

private static async Task fetchData(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
   await DoQuery(queryEnumerable);
}

private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
   Console.WriteLine("  Query results:");
   await foreach (var result in queryEnumerable) {
      foreach (var row in result.Row
      {
         Console.WriteLine();
         Console.WriteLine(row.ToJsonString());
      }
   }
}

private const string stmt_ij ="SELECT * FROM ticket a, ticket.bagInfo.flightLegs b WHERE a.ticketNo=b.ticketNo";
Console.WriteLine("Fetching data using Inner Join: ");
await fetchData(client,stmt_ij);

相关主题

• 创建表
• 添加数据