Inner Joins mit übergeordneten/untergeordneten Tabellen verwenden

Mit JOIN werden Zeilen aus zwei oder mehr Tabellen basierend auf einer zugehörigen Spalte kombiniert. In einer hierarchischen Tabelle erbt die untergeordnete Tabelle die Primärschlüsselspalten der übergeordneten Tabelle. Dies geschieht implizit, ohne dass die übergeordneten Spalten in die CREATE TABLE-Anweisung des untergeordneten Elements aufgenommen werden. Alle Tabellen in der Hierarchie haben dieselben Shard-Schlüsselspalten.

Ein Inner Join ist einer der Join-Typen, mit denen Tabellen kombiniert werden, die zu derselben Tabellenhierarchie in einem Oracle NoSQL Database Cloud Service gehören.

Inner Join - Überblick

Ein Inner Join ist eine Operation, die neue Zeilen erzeugt, indem Zeilen aus zwei oder mehr Tabellen kombiniert werden, basierend auf den Join-Prädikaten, die auf zugehörige Spalten oder Felder zwischen ihnen angewendet werden. Die Ergebnismenge enthält nur die kombinierten Zeilen, die den Join-Prädikaten entsprechen.

Ein Inner Join funktioniert konzeptionell wie folgt:

Beachten Sie, dass Sie einen Inner Join der drei Tabellen A, B und C ausführen müssen. Die Tabellen A und B werden zuerst verknüpft. Das heißt, wenn Tabelle A N Zeilen und n Spalten enthält und Tabelle B M Zeilen und m Spalten enthält, wird jede Zeile in Tabelle A mit jeder Zeile in Tabelle B verknüpft. Die resultierende Tabelle AB hätte somit (N * M) Zeilen und (n + m) Spalten. Ebenso wird Tabelle AB nun mit Tabelle C verknüpft, um Tabelle ABC zu bilden. Die Join-Prädikate in der WHERE-Klausel werden dann auf die Tabelle ABC angewendet. Beachten Sie, dass die Join-Prädikate Gleichheitsprädikate zwischen allen Shard-Schlüsselspalten der verknüpften Tabellen enthalten müssen. Die endgültige Ergebnismenge enthält nur die übereinstimmenden Zeilen aus den beteiligten Tabellen.

Sie geben die Tabellen an, die in der FROM-Klausel der SELECT-Anweisung verknüpft werden sollen, und die Join-Prädikate in der WHERE-Klausel. Ein Join-Prädikat ist ein Prädikat, das die Spalten oder Felder aus einer oder mehreren Tabellen referenziert, die verknüpft werden sollen, und die Filterbedingungen angibt, die auf sie angewendet werden müssen. Bei einem Inner Join muss die WHERE-Klausel das Gleichheitsprädikat für alle Shard-Schlüssel der beteiligten Tabellen enthalten.

Wenn Sie ein "*" mit der "SELECT"-Klausel verwenden, bei der alle Felder in den Tabellen zurückgegeben werden, hängt die Reihenfolge der Felder in der Ergebnismenge von der Reihenfolge ab, in der Sie die Tabellen in der FROM-Klausel angeben. Wenn Sie eine Liste der Felder in der SELECT-Klausel angeben, entspricht die Reihenfolge der Felder in der Ergebnismenge der SELECT-Klausel.

Beim Ausführen eines Inner Joins gilt Folgendes:

Ein Inner Join unterscheidet sich von NESTED TABLES und Left Outer Join hauptsächlich in den folgenden Aspekten:

Im Wesentlichen können Tabellen mit einer Vorgänger-Abhängigkeitsbeziehung zwischen ihnen mit einem der drei Join-Typen verknüpft werden. Sie können einen davon basierend auf Ihrem Anwendungsfall verwenden. Wenn sich die zu verknüpfenden Tabellen nicht in einer Vorgänger-Abhängigkeitsbeziehung befinden, muss ein innerer Join verwendet werden.

Beispiele mit Inner Join

Betrachten Sie eine Anwendung zur Verfolgung von Fluggepäck. Für jede Flugscheinnummer ist ein Passagier und sein Gepäck damit verbunden. Die Root-Tabelle ist ticket und hat 2 untergeordnete Tabellen passengerInfo und baggageInfo. Die Tabelle passengerInfo enthält die Details des Passagiers und die baggageInfo enthält Details der vom Passagier eingecheckten Taschen. Diese Taschen werden durch ihren Transit durch mehrere Zwischenstationen verfolgt. Diese Trackinginformationen werden in einer Tabelle namens flightlegs erfasst, die der Tabelle baggageInfo untergeordnet ist.

Laden Sie das Skript parentchildtbls_loaddata.sql herunter, und führen Sie es wie unten dargestellt aus. Dieses Skript erstellt die in diesem Beispiel verwendeten Tabellen und lädt Daten in die Tabellen.

Die parentchildtbls_loaddata.sql enthält Folgendes:

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

Folgende Tabellen werden erstellt:

Beispiel für SQL-Abfragen für Inner Join:

Beispiel 1 - Rufen Sie die Details des Passagiers mit der Ticketnummer 1762324912391 ab.

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

Erläuterung: Dies ist ein Beispiel für einen Inner Join, bei dem das übergeordnete Tabellenticket mit seiner untergeordneten Tabelle "passagierInfo" verknüpft wird und ein Filter zum Einschränken des Ergebnisses angewendet wird. Beachten Sie, dass der Shard-Schlüssel hier Ticket-Nr. ist. Wenn der Shard-Schlüssel beim Erstellen der Root-Tabelle nicht explizit angegeben wird, wird der Primärschlüssel der Root-Tabelle als Shard-Schlüssel verwendet. Dieser Shard-Schlüssel wird von allen untergeordneten Tabellen geerbt.

Ausgabe:

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

Beispiel 2 - Rufen Sie die Details aller Passagiere ab, die ein Ticket ausgestellt haben.

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

Erläuterung: Dies ist ein Beispiel für einen Inner Join, bei dem die übergeordnete Tabelle ticket mit der untergeordneten Tabelle bagInfo verknüpft ist.

Ausgabe:

{"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

Beispiel 3 - Rufen Sie die Flugstreckendetails der Gepäckstücke des Passagiers mit der Ticketnummer 1762344493810 ab.

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

Erläuterung: Dies ist ein Beispiel für einen Inner Join, bei dem die übergeordnete Tabelle ticket mit dem untergeordneten Element flightlegs verknüpft ist. Eine untergeordnete Tabelle kann eine beliebige hierarchische Ebene unterhalb einer Tabelle sein (Beispiel: flightLegs ist das untergeordnete Element von bagInfo, das untergeordnete Element von ticket ist. Daher ist flightLegs ein untergeordnetes Element von ticket). Das Ergebnis wird dann nach einer bestimmten Ticketnummer gefiltert.

Ausgabe:

{"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

Beispiel 4 - Finden Sie die Anzahl der Hopfen für alle Taschen eines Passagiers mit der Ticketnummer 1762355527825. Wenn für einen Passagier mehrere Taschen eingecheckt sind, wird die Anzahl der Hopfen für alle Taschen angezeigt.

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

Erläuterung: Hier gruppieren Sie die Daten basierend auf der Beutel-ID (mit GROUP BY) und erhalten die Anzahl der Flugstrecken (mit count()) für jede Tasche. Außerdem filtern Sie die Ergebnisse nach einer bestimmten Ticketnummer.

Ausgabe:

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

Beispiel 5 - Rufen Sie die Ticketnummer, den Passagiernamen und die Gepäckdetails aller Passagiere ab.

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

Erläuterung: Dies ist ein Beispiel für einen Inner Join von drei Tabellen, d.h. der übergeordneten Tabelle ticket und den gleichgeordneten Tabellen passengerInfo und bagInfo.

Ausgabe:

{"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

Beispiel 6 - Rufen Sie den Namen des Passagiers ab, dessen letzte Station "MEL" ist

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

Erläuterung: Dies ist ein Beispiel für einen Inner Join der gleichgeordneten Tabellen passengerInfo und bagInfo. Der Name des Passagiers, dessen Tasche zuletzt an der Station "MEL" gesehen wurde, wird zurückgegeben.

Ausgabe:

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

Beispiel 7 - Name des Passagiers abrufen, dessen Flugroutenziel "MEL" ist

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

Erläuterung: Dies ist ein innerer Join von zwei Tabellen, passengerInfo und flightlegs, die sich nicht in einer Vorgänger-Abhängigkeitsbeziehung befinden.

Ausgabe:

{"fullName":"Adam Phillips"}

Ein solcher Join zwischen Tabellen, die sich nicht in einer Vorgänger-Abhängigkeitsbeziehung befinden, ist mit Linker Outer Join und NESTED TABLES nicht möglich.

Beispiele für Abfrage-API

Zum Ausführen der Abfrage verwenden Sie die NoSQLHandle.query()-API.

Laden Sie den vollständigen Code TableJoins.java aus den Beispielen hier herunter.

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

Um die Abfrage auszuführen, verwenden Sie die Methode borneo.NoSQLHandle.query().

Laden Sie den vollständigen Code TableJoins.py aus den Beispielen hier herunter

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

Um eine Abfrage auszuführen, verwenden Sie die Funktion Client.Query.

Laden Sie den vollständigen Code TableJoins.go aus den Beispielen hier herunter.

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)

Um eine Abfrage auszuführen, verwenden Sie die Methode query.

JavaScript: Laden Sie den vollständigen Code TableJoins.js aus den Beispielen hier herunter.

//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: Laden Sie den vollständigen Code TableJoins.ts aus den Beispielen hier herunter.

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

Um eine Abfrage auszuführen, können Sie die Methode QueryAsync aufrufen oder die Methode GetQueryAsyncEnumerable aufrufen und über die resultierende asynchrone aufzählbare Methode iterieren.

Laden Sie den vollständigen Code TableJoins.cs aus den Beispielen hier herunter.

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