Utiliser une jointure interne avec des tables parent-enfant

Une jointure est utilisée pour combiner des lignes de plusieurs tables, en fonction d'une colonne associée 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 de manière implicite, sans inclure les colonnes parent dans l'instruction CREATE TABLE de l'enfant. Toutes les tables de la hiérarchie ont les mêmes colonnes de clé de shard.

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.

Présentation de la jointure interne

Une jointure interne est une opération qui produit de nouvelles lignes en combinant des lignes provenant de plusieurs tables, en fonction des prédicats de jointure appliqués aux colonnes ou champs associés entre elles. L'ensemble 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. Les prédicats de jointure doivent inclure des prédicats d'égalité entre toutes les colonnes de clé de shard des tables jointes. Le dernier ensemble de résultats 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 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 shard des tables participantes.

Si vous utilisez un "*" avec la clause "SELECT", dans laquelle tous les champs des tables sont renvoyés, l'ordre des champs dans l'ensemble 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 l'ensemble de résultats est celui indiqué dans la clause SELECT.

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

Une jointure interne diffère des TABLES NESTÉES et de la 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 d'utilisation de jointure interne

Pensez à une application de suivi des bagages des compagnies aériennes. Pour chaque numéro de billet d'avion, il y a un passager et ses bagages associés. La table racine est ticket et elle comporte 2 tables enfant passengerInfo et baggageInfo. Le tableau passengerInfo contient les détails du passager et le tableau baggageInfo contient les détails des bagages enregistrés par le passager. Ces sacs sont suivis à travers leur transit par de multiples stations intermédiaires. Ces informations de suivi sont capturées 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.

Le fichier 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 de requêtes 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 où le ticket de table parent est joint à sa table enfant passagerInfo et où un filtre est appliqué pour restreindre le résultat. Notez que la clé de shard ici est ticketNo. Si la clé de shard n'est pas explicitement indiquée lors de la création de la table racine, la clé primaire de la table racine est considérée comme la clé de shard. Cette clé de shard 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 où 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 : il s'agit d'un 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érarchique sous une table (par exemple, flightLegs est l'enfant de bagInfo, qui est l'enfant de ticket, donc 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 houblons 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 de conteneur (à l'aide de GROUP BY) et obtenez le nombre de jambes de vol (à l'aide de count()) pour chaque conteneur. 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 - Récupérer le nom du passager dont le dernier poste vu est "MEL"

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

Explication :Ceci est un 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 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 NESTÉES.

Exemples d'API de requête

Pour exécuter la requête, 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 requête, 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 requête, 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 requête, 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 requête, vous pouvez appeler la méthode QueryAsync ou la méthode GetQueryAsyncEnumerable et itérer sur l'énumérable asynchrone obtenu.

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