使用內部結合與父項 - 子項表格

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 子句中所指定。

執行內部結合時，適用下列項目：

• 只允許在相同表格階層的表格之間結合。

• 支援結合在祖代子代關係中的表格，以及不在祖代子代關係中的表格。

• 結合述詞必須包含結合表格之所有分區索引鍵資料欄之間的相等性述詞。若要進一步瞭解分區索引鍵，請參閱建立表格。也就是說，對於任何結合的表格組，只有當其中一個表格的資料列在其分區索引鍵資料欄上有相同的值時，才會與另一個表格的資料列相符。您可以使用 DESCRIBE TABLE 敘述句來識別分區索引鍵。

• WHERE 子句中的其餘述詞會套用至這些相符的資料列。

內部結合與 NESTED TABLES 和左外部結合主要在以下方面不同：

• 內部結合是以符合參與表格的分區索引鍵為基礎，而 NESTED TABLES 和左外部結合則是以符合參與表格的主索引鍵為基礎。

• 內部結合的結果集只包含相符的資料列。而在 NESTED TABLES 與左外部結合的情況下，左側表格中的不相符資料列也會在結果集中傳回，右側表格中有對應的 NULL 資料列。

• 內部結合可用於結合不在祖代 - 子代關係中的表格。如果是左外部結合和 NESTED_TABLES，則無法這麼做。如需詳細資訊，請參閱內部結合 vs LOJ vs NESTED TABLES 。

本質上，兩者之間具有祖代 - 子代關係的表格可以使用這三種結合類型中的任何一種來結合。您可以根據使用案例選擇使用其中一種。如果要結合的表格不在祖代 - 子代關係中，則必須使用內部結合。

使用內部結合的範例

請考慮使用航空公司行李追蹤應用程式。每個機票號碼都有一個乘客及其行李與其相關。根表格為 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)

• 機票。包資訊。航班航段

  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

說明：這是內部結合的範例，其中父項表格擱置中活動與其子項表格 passengerInfo 結合，並套用篩選來限制結果。請注意，此處的分區索引鍵是 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"}

左外部結合和巢狀 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() 方法。

從此處的範例下載完整代碼 TableJoins.py

# 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 函數。

從此處的範例下載完整代碼 TableJoins.go 。

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

相關主題

• 建立表格
• 新增資料