親子表での内部結合の使用

JOINは、2つ以上の表の行を、それらの表の関連列に基づいて結合するために使用します。階層表では、子表は親表の主キー列を継承します。これは暗黙的に行われ、子のCREATE TABLE文に親列が含まれることはありません。階層内のすべての表には、同じシャード・キー列があります。

内部結合は、Oracle NoSQL Database Cloud Service内の同じ表階層に属する表を結合するために使用される結合のタイプの1つです。

• 内部結合の概要

• 内部結合の使用例

内部結合の概要

内部結合は、関連する列またはそれらの間のフィールドに適用される結合述語に基づいて、2つ以上の表の行を結合することによって新しい行を生成する操作です。結果セットには、結合述語を満たす結合された行のみが含まれます。

概念的には、内部結合は次のように機能します。

3つの表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句の結合述語で指定します。結合述語は、結合する1つ以上の表の列またはフィールドを参照し、それらに適用する必要があるフィルタ条件を指定する述語です。内部結合の場合、WHERE句には、参加表のすべてのシャード・キーに対する等価述語が含まれている必要があります。

'SELECT'句で'*'を使用すると、表内のすべてのフィールドが返される場合、結果セット内のフィールドの順序は、FROM句で表を指定した順序によって異なります。SELECT句でフィールドのリストを指定した場合、結果セット内のフィールドの順序はSELECT句で指定されているとおりになります。

内部結合の実行中に、次のことが適用されます。

• 同じ表階層内の表間の結合のみが許可されます。

• 祖先と子孫の関係にある表と、祖先と子孫の関係にない表の結合をサポートします。

• 結合述語には、結合表のすべてのシャード・キー列間の等価述語が含まれている必要があります。シャード・キーの詳細は、CREATE TABLEを参照してください。つまり、結合表の任意のペアについて、一方の表からの行が、もう一方の表からの行と一致するのは、両方ともシャード・キー列に同じ値がある場合のみです。DESCRIBE TABLE文を使用して、シャード・キーを識別できます。

• WHERE句の述語は、これらの一致する行に適用されます。

内部結合は、主に次の点でNESTED TABLESおよび左外部結合とは異なります。

• 内部結合は、参加表のシャード・キーの一致に基づきますが、NESTED TABLESおよび左外部結合は、参加表の主キーの一致に基づきます。

• 内部結合の結果セットには、一致する行のみが含まれます。一方、NESTED TABLESおよび左外部結合の場合、左表の一致しない行も、右表に対応するNULL行を持つ結果セットで返されます。

• 内部結合は、祖先と子孫の関係にない表を結合するために使用できます。これは、左外部結合およびNESTED_TABLESの場合には実行できません。詳細は、「内部結合vs LOJvs NESTED TABLES」を参照してください。

基本的に、それらの間に祖先と子孫の関係を持つ表は、3つのタイプの結合のいずれかを使用して結合できます。ユース・ケースに基づいて、いずれかを使用できます。結合する表が祖先と子孫の関係にない場合、内部結合を使用する必要があります。

内部結合の使用例

航空会社手荷物の追跡アプリケーションについて考えてみます。航空券番号ごとに、乗客とその手荷物が関連付けられています。ルート表はticketで、2つの子表passengerInfoおよびbaggageInfoがあります。passengerInfo表には乗客の詳細が含まれ、baggageInfoには乗客がチェックインしたバッグの詳細が含まれます。これらのバッグは、複数の中間ステーションを介して輸送を介して追跡されます。このトラッキング情報は、baggageInfo表の子であるflightlegsという表に取得されます。

スクリプトparentchildtbls_loaddata.sqlをダウンロードして、次に示すように実行します。このスクリプトは、例で使用されている表を作成し、表にデータをロードします。

• KVSTOREまたはKVLiteを起動

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

• SQLシェルを開きます。

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

作成された表は次のとおりです。

• チケット

  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)

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

説明:これは、親テーブルチケットをその子テーブル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はticketの子であるbagInfoの子であるため、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

説明:(GROUP BYを使用して)手荷物IDに基づいてデータをグループ化し、それぞれの手荷物の飛行区間間の数を取得します(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

説明:これは、3つのテーブル、つまり親テーブル 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"

説明:これは、祖先と子孫の関係にない2つのテーブル passengerInfoと flightlegsの内部結合です。

出力:

{"fullName":"Adam Phillips"}

祖先と子孫の関係にない表間のこのような結合は、左外部結合およびNESTED TABLESでは実行できません。

問合せAPIの例

• Java
• Python
• Go
• Node.js
• C#

問合せを実行するには、NoSQLHandle.query() APIを使用します。

こちらにあるサンプルの中にフル・コードTableJoins.javaをダウンロードします。

/* 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メソッドをコールして、結果の非同期列挙可能性に対して反復処理します。

こちらにあるサンプル中からフル・コードTableJoins.csをダウンロードします。

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

関連トピック

• 表の作成
• データの追加