Utilizzo di un join interno con tabelle padre-figlio

Un JOIN viene utilizzato per combinare le righe di due o più tabelle in base a una colonna correlata tra di esse. In una tabella gerarchica, la tabella figlio eredita le colonne chiave primaria della relativa tabella padre. Questa operazione viene eseguita in modo implicito, senza includere le colonne padre nell'istruzione CREATE TABLE del figlio. Tutte le tabelle nella gerarchia hanno le stesse colonne chiave partizione.

Un inner join è uno dei tipi di join utilizzati per combinare tabelle appartenenti alla stessa gerarchia di tabelle in Oracle NoSQL Database Cloud Service.

Panoramica sul join interno

Un join interno è un'operazione che produce nuove righe combinando righe da due o più tabelle, in base ai predicati di join applicati a colonne o campi correlati tra loro. Il set di risultati contiene solo le righe combinate che soddisfano i predicati di join.

Concettualmente, un'unione interna funziona come segue:

Si consideri che è necessario eseguire un join interno di tre tabelle A, B e C. Le tabelle A e B vengono prima unite. Cioè, se la tabella A ha N righe e n colonne e la tabella B ha M righe e m colonne, ogni riga della tabella A è unita a ogni riga della tabella B. La tabella risultante AB avrebbe quindi righe (N * M) e colonne (n + m). Allo stesso modo, la tabella AB è ora unita alla tabella C per formare la tabella ABC. I predicati join nella clausola WHERE vengono quindi applicati alla tabella ABC. Tenere presente che i predicati join devono includere predicati di uguaglianza tra tutte le colonne chiave partizione delle tabelle unite tramite join. Il set di risultati finale contiene solo le righe corrispondenti delle tabelle partecipanti.

È possibile specificare le tabelle da unire tramite join nella clausola FROM dell'istruzione SELECT e i predicati join nella clausola WHERE. Un predicato join è un predicato che fa riferimento alle colonne o ai campi di una o più tabelle da unire tramite join e specifica le condizioni di filtro da applicare. Nel caso di inner join, la clausola WHERE deve includere il predicato di uguaglianza su tutte le chiavi partizione delle tabelle partecipanti.

Se si utilizza una clausola '*' con la clausola 'SELECT', in cui vengono restituiti tutti i campi nelle tabelle, l'ordine dei campi nel set di risultati dipende dall'ordine in cui vengono specificate le tabelle nella clausola FROM. Se si fornisce un elenco di campi nella clausola SELECT, l'ordine dei campi nel set di risultati è quello specificato nella clausola SELECT.

Durante l'esecuzione di un join interno, sono applicabili le operazioni riportate di seguito.

Un join interno è diverso da NESTED TABLES e left outer join principalmente nei seguenti aspetti:

In sostanza, le tabelle che hanno una relazione antenato-discendente tra loro possono essere unite utilizzando uno qualsiasi dei tre tipi di join. Puoi scegliere di utilizzarne uno in base al tuo caso d'uso. Se le tabelle da unire tramite join non sono in una relazione predecessore-scendente, è necessario utilizzare l'interternal join.

Esempi con join interno

Considera un'applicazione di tracciamento dei bagagli della compagnia aerea. Per ogni numero di biglietto aereo, c'è un passeggero e il loro bagaglio associato ad esso. La tabella radice è ticket e dispone di 2 tabelle figlio passengerInfo e baggageInfo. La tabella passengerInfo contiene i dettagli del passeggero e il baggageInfo contiene i dettagli dei bagagli registrati dal passeggero. Queste borse sono tracciate attraverso il loro transito attraverso più stazioni intermedie. Queste informazioni di tracciamento vengono acquisite in una tabella denominata flightlegs, ovvero il figlio della tabella baggageInfo.

Scaricare lo script parentchildtbls_loaddata.sql ed eseguirlo come mostrato di seguito. Questo script crea le tabelle utilizzate nell'esempio e carica i dati nelle tabelle.

parentchildtbls_loaddata.sql contiene quanto segue:

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

Di seguito sono riportate le tabelle create.

Vediamo ora alcune query SQL di esempio per l'Internal Join:

Esempio 1 - Recupera i dettagli del passeggero con il numero di biglietto 1762324912391.

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

Spiegazione: si tratta di un esempio di un join interno in cui il ticket della tabella padre è unito al valore passeggeroInfo della tabella figlio e viene applicato un filtro per limitare il risultato. Si noti che la chiave della partizione qui è ticketNo. Se la chiave partizione non viene specificata in modo esplicito durante la creazione della tabella radice, la chiave primaria della tabella radice viene acquisita come chiave partizione. Questa chiave di partizione viene ereditata da tutte le tabelle discendenti.

Output:

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

Esempio 2 - Recupera i dettagli del bagaglio di tutti i passeggeri che hanno ricevuto un biglietto.

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

Spiegazione: si tratta di un esempio di un join interno in cui la tabella padre ticket è unita tramite join alla relativa tabella figlio bagInfo.

Output:

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

Esempio 3 - Recupera i dettagli della gamba del volo dei bagagli del passeggero con numero di biglietto 1762344493810.

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

Spiegazione: questo è un esempio di un join interno in cui la tabella padre ticket è unita tramite join al relativo discendente flightlegs. Una tabella discendente può essere qualsiasi livello gerarchicamente al di sotto di una tabella (ad esempio flightLegs è il figlio di bagInfo, ovvero il figlio di ticket, quindi flightLegs è un discendente di ticket). Il risultato viene quindi filtrato per un determinato numero di biglietto.

Output:

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

Esempio 4 - Trovare il numero di luppolo per tutti i bagagli di un passeggero con il numero di biglietto 1762355527825 . Se sono presenti più sacchetti archiviati per un passeggero, viene visualizzato il numero di luppoli per tutte le borse.

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

Spiegazione:qui, è possibile raggruppare i dati in base all'ID borsa (utilizzando GROUP BY) e ottenere il conteggio delle gambe di volo (utilizzando count()) per ogni borsa. Inoltre, si filtrano i risultati per un determinato numero di ticket.

Output:

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

Esempio 5 - Recupera il numero del biglietto, il nome del passeggero e i dettagli del bagaglio di tutti i passeggeri.

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

Spiegazione: si tratta di un esempio di join interno di tre tabelle, ovvero la tabella padre ticket, e le tabelle di pari livello passengerInfo e bagInfo.

Output:

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

Esempio 6 - Recupera il nome del passeggero, l'ultima stazione vista della cui borsa è "MEL"

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

Spiegazione: si tratta di un esempio di un join interno delle tabelle di pari livello passengerInfo e bagInfo. Viene restituito il nome del passeggero la cui borsa è stata vista l'ultima volta alla stazione "MEL".

Output:

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

Esempio 7 - Recupera il nome del passeggero la cui destinazione della rotta di volo è "MEL"

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

Spiegazione: si tratta di un join interno di due tabelle, passengerInfo e flightlegs, che non si trovano in una relazione predecessore-scendente.

Output:

{"fullName":"Adam Phillips"}

Un join di questo tipo tra tabelle che non si trovano in una relazione predecessore-scendente non è possibile con Left Outer Join e NESTED TABLES.

Esempi di API query

Per eseguire la query, utilizzare l'API NoSQLHandle.query().

Scarica il codice completo TableJoins.java dagli esempi qui.

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

Per eseguire la query, utilizzare il metodo borneo.NoSQLHandle.query().

Scarica il codice completo TableJoins.py dagli esempi qui

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

Per eseguire una query, utilizzare la funzione Client.Query.

Scarica il codice completo TableJoins.go dagli esempi qui.

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)

Per eseguire una query, utilizzare il metodo query.

JavaScript: scarica il codice completo TableJoins.js dagli esempi qui.

//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: scarica il codice completo TableJoins.ts dagli esempi qui.

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

Per eseguire una query, è possibile chiamare il metodo QueryAsync o chiamare il metodo GetQueryAsyncEnumerable e ripetere l'enumerazione asincrona risultante.

Scarica il codice completo TableJoins.cs dagli esempi qui.

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