Utiliser la jointure interne avec les tables parent-enfant

Une jointure est utilisée pour combiner des lignes de deux tables ou plus, en fonction d'une colonne connexe entre elles. Dans une table hiérarchique, la table enfant hérite des colonnes de clé primaire de sa table parent. Cette opération est effectuée implicitement, sans inclure les colonnes parents dans l'énoncé CREATE TABLE de l'enfant. Toutes les tables de la hiérarchie ont les mêmes colonnes de clé de partition.

Une jointure interne est l'un des types de jointure utilisés pour combiner des tables appartenant à la même hiérarchie de tables dans un service Oracle NoSQL Database Cloud Service.

Aperçu de la jointure interne

Une jointure interne est une opération qui génère de nouvelles lignes en combinant des lignes de deux tables ou plus, en fonction des prédicats de jointure appliqués aux colonnes ou champs connexes entre elles. Le jeu de résultats contient uniquement les lignes combinées qui satisfont aux prédicats de jointure.

Conceptuellement, une jointure interne fonctionne comme suit :

Considérez que vous devez effectuer une jointure interne de trois tables A, B et C. Les tables A et B sont d'abord jointes. Autrement dit, si la table A comporte N lignes et n colonnes, et que la table B comporte M lignes et m colonnes, chaque ligne de la table A est jointe à chaque ligne de la table B. La table résultante AB aurait donc (N * M) lignes et (n + m) colonnes. De même, la table AB est maintenant jointe à la table C pour former la table ABC. Les prédicats de jointure de la clause WHERE sont ensuite appliqués à la table ABC. Notez que les prédicats de jointure doivent inclure des prédicats d'égalité entre toutes les colonnes de clé de partition des tables jointes. Le jeu de résultats final contient uniquement les lignes correspondantes des tables participantes.

Vous indiquez les tables à joindre dans la clause FROM de l'instruction SELECT et les prédicats de jointure dans la clause WHERE. Un prédicat de jointure est un prédicat qui référence les colonnes ou les champs d'une ou de plusieurs tables à joindre et qui spécifie les conditions de filtre à appliquer. Dans le cas d'une jointure interne, la clause WHERE doit inclure le prédicat d'égalité sur toutes les clés de partition des tables participantes.

Si vous utilisez un '*' avec la clause 'SELECT', dans laquelle tous les champs des tables sont retournés, l'ordre des champs dans le jeu de résultats dépend de l'ordre dans lequel vous spécifiez les tables dans la clause FROM. Si vous fournissez une liste de champs dans la clause SELECT, l'ordre des champs dans le jeu de résultats est tel que spécifié dans la clause SELECT.

Lors de l'exécution d'une jointure interne, les éléments suivants s'appliquent :

Une jointure interne diffère de NESTED TABLES et de jointure externe gauche principalement dans les aspects suivants :

En substance, les tables ayant une relation ancêtre-descendant entre elles peuvent être jointes à l'aide de l'un des trois types de jointure. Vous pouvez choisir d'utiliser l'un d'entre eux en fonction de votre cas d'utilisation. Si les tables à joindre ne sont pas dans une relation ancêtre-descendant, la jointure interne doit être utilisée.

Exemples utilisant la jointure interne

Pensez à une application de suivi des bagages de la compagnie aérienne. Pour chaque numéro de billet d'avion, il y a un passager et ses bagages associés. La table racine est ticket et comporte 2 tables enfants passengerInfo et baggageInfo. Le tableau passengerInfo contient les détails du passager et le baggageInfo contient les détails des sacs enregistrés par le passager. Ces sacs sont suivis à travers leur transit par de multiples stations intermédiaires. Ces informations de suivi sont saisies dans une table nommée flightlegs, qui est l'enfant de la table baggageInfo.

Téléchargez le script parentchildtbls_loaddata.sql et exécutez-le comme indiqué ci-dessous. Ce script crée les tables utilisées dans l'exemple et charge les données dans les tables.

parentchildtbls_loaddata.sql contient les éléments suivants :

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

Les tables créées sont les suivantes :

Voyons maintenant quelques exemples d'interrogations SQL pour la jointure interne :

Exemple 1 - Extraire les détails du passager avec le numéro de billet 1762324912391.

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

Explication : Il s'agit d'un exemple de jointure interne dans laquelle le ticket de table parent est joint à sa table enfant passagerInfo et un filtre est appliqué pour restreindre le résultat. Notez que la clé de partition ici est ticketNo. Si la clé de partition n'est pas explicitement spécifiée lors de la création de la table racine, la clé primaire de la table racine est prise comme clé de partition. Cette clé de partition est héritée par toutes les tables descendantes.

Sortie :

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

Exemple 2 - Extraire les détails du sac de tous les passagers qui ont reçu un billet.

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

Explication : Il s'agit d'un exemple de jointure interne dans laquelle la table parent ticket est jointe à sa table enfant bagInfo.

Sortie :

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

Exemple 3 - Extraire les détails de la jambe de vol des sacs du passager avec le numéro de billet 1762344493810.

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

Explication : Exemple de jointure interne dans laquelle la table parent ticket est jointe à son descendant flightlegs. Une table descendante peut être n'importe quel niveau hiérarchiquement sous une table (par exemple, flightLegs est l'enfant de bagInfo, qui est l'enfant de ticket, de sorte que flightLegs est un descendant de ticket). Le résultat est ensuite filtré pour un numéro de ticket particulier.

Sortie :

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

Exemple 4 - Trouver le nombre de sauts pour tous les sacs d'un passager avec le numéro de billet 1762355527825 . Si plusieurs sacs sont enregistrés pour un passager, le nombre de sauts pour tous les sacs est affiché.

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

Explication : Ici, vous regroupez les données en fonction de l'ID sac (à l'aide de GROUP BY) et obtenez le nombre de jambes de vol (à l'aide de count()) pour chaque sac. En outre, vous filtrez les résultats pour un numéro de ticket particulier.

Sortie :

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

Exemple 5 - Extraire le numéro du billet, le nom du passager et les détails du sac de tous les passagers.

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

Explication : Il s'agit d'un exemple de jointure interne de trois tables, à savoir la table parent ticket et les tables apparentées passengerInfo et bagInfo.

Sortie :

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

Exemple 6 - Extraire le nom du passager dont la dernière station vue est "MEL"

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

Explication : Exemple de jointure interne des tables apparentées passengerInfo et bagInfo. Le nom du passager dont le sac a été vu pour la dernière fois à la station "MEL" est renvoyé.

Sortie :

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

Exemple 7 - Extraire le nom du passager dont la destination de l'itinéraire de vol est "MEL"

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

Explication : Il s'agit d'une jointure interne de deux tables, passengerInfo et flightlegs, qui ne sont pas dans une relation ancêtre-descendant.

Sortie :

{"fullName":"Adam Phillips"}

Une telle jointure entre des TABLES qui ne sont pas dans une relation ancêtre-descendant n'est pas possible avec Jointure externe gauche et TABLES NESTED.

Exemples d'API d'interrogation

Pour exécuter une interrogation, utilisez l'API NoSQLHandle.query().

Téléchargez le code complet TableJoins.java à partir des exemples ici.

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

Pour exécuter votre interrogation, utilisez la méthode borneo.NoSQLHandle.query().

Téléchargez le code complet TableJoins.py à partir des exemples ici

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

Pour exécuter une interrogation, utilisez la fonction Client.Query.

Téléchargez le code complet TableJoins.go à partir des exemples ici.

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)

Pour exécuter une interrogation, utilisez la méthode query.

JavaScript : Téléchargez le code complet TableJoins.js à partir des exemples ici.

//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 : Téléchargez le code complet TableJoins.ts à partir des exemples ici.

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

Pour exécuter une interrogation, vous pouvez appeler la méthode QueryAsync ou appeler la méthode GetQueryAsyncEnumerable et effectuer une itération sur l'énumérable asynchrone résultant.

Téléchargez le code complet TableJoins.cs à partir des exemples ici.

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