Usando junção interna com tabelas pai e filho

Uma JOIN é usada para combinar linhas de duas ou mais tabelas, com base em uma coluna relacionada entre elas. Em uma tabela hierárquica, a tabela filho herda as colunas de chave primária de sua tabela pai. Isso é feito implicitamente, sem incluir as colunas pai na instrução CREATE TABLE do filho. Todas as tabelas na hierarquia têm as mesmas colunas de chave de partição.

Uma junção interna é um dos tipos de junção usados para combinar tabelas que pertencem à mesma hierarquia em um Oracle NoSQL Database Cloud Service.

Visão Geral da Junção Interna

Uma junção interna é uma operação que produz novas linhas, combinando linhas de duas ou mais tabelas, com base nos predicados de junção aplicados a colunas ou campos relacionados entre elas. O conjunto de resultados contém apenas as linhas combinadas que satisfazem aos predicados de junção.

Conceitualmente, uma junção interna funciona da seguinte forma:

Considere que você precise realizar uma junção interna de três tabelas A, B e C. As tabelas A e B são unidas primeiro. Ou seja, se a tabela A tiver N linhas e n colunas e a tabela B tiver linhas M e m colunas, cada linha da tabela A será juntada a cada linha da tabela B. A tabela resultante AB teria, portanto, linhas (N * M) e colunas (n + m). Da mesma forma, a tabela AB agora é unida à tabela C, para formar a tabela ABC. Os predicados de junção na cláusula WHERE são então aplicados à tabela ABC. Observe que os predicados de junção devem incluir predicados de igualdade entre todas as colunas de chave de partição das tabelas unidas. O conjunto de resultados final contém apenas as linhas correspondentes das tabelas participantes.

Você especifica as tabelas a serem unidas na cláusula FROM da instrução SELECT e os predicados de junção na cláusula WHERE. Um predicado de junção é um predicado que faz referência às colunas ou aos campos de uma ou mais tabelas a serem unidas e especifica as condições de filtro que precisam ser aplicadas a elas. No caso de junção interna, a cláusula WHERE deve incluir o predicado de igualdade em todas as chaves de partição das tabelas participantes.

Se você usar um '*' com a cláusula 'SELECT', em que todos os campos das tabelas são retornados, a ordem dos campos no conjunto de resultados dependerá da ordem em que você especificar as tabelas na cláusula FROM. Se você fornecer uma lista de campos na cláusula SELECT, a ordem dos campos no conjunto de resultados será a especificada na cláusula SELECT.

Ao realizar uma junção interna, aplica-se o seguinte:

Uma junção interna difere das TABLES NESTADAS e da junção externa esquerda principalmente nos seguintes aspectos:

Basicamente, as tabelas que têm uma relação ancestral-descendente entre elas podem ser unidas usando qualquer um dos três tipos de junção. Você pode optar por usar um deles com base no seu caso de uso. Se as tabelas a serem unidas não estiverem em um relacionamento ancestral-descendente, a junção interna deverá ser usada.

Exemplos de uso da Junção Interna

Considere um aplicativo de rastreamento de bagagem de companhia aérea. Para cada número de bilhete de voo, há um passageiro e sua bagagem associada a ele. A tabela-raiz é ticket e tem 2 tabelas filhas passengerInfo e baggageInfo. A tabela passengerInfo contém os detalhes do passageiro e o baggageInfo contém os detalhes das malas que o passageiro fez check-in. Estes sacos são rastreados através do seu trânsito através de várias estações intermediárias. Essas informações de rastreamento são capturadas em uma tabela chamada flightlegs, que é a filha da tabela baggageInfo.

Faça download do script parentchildtbls_loaddata.sql e execute-o conforme mostrado abaixo. Este script cria as tabelas usadas no exemplo e carrega dados nas tabelas.

O parentchildtbls_loaddata.sql contém o seguinte:

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

Estas são as tabelas criadas:

Vamos ver agora alguns exemplos de consultas SQL para junção interna:

Exemplo 1 - Busque os detalhes do passageiro com o número do bilhete 1762324912391.

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

Explicação:Este é um exemplo de uma junção interna em que o tíquete da tabela pai é unido a sua tabela filho PassageInfo e um filtro é aplicado para restringir o resultado. Observe que a chave de partição aqui é ticketNo. Se a chave de partição não for especificada explicitamente ao criar a tabela raiz, a chave primária da tabela raiz será considerada como a chave de partição. Essa chave de partição é herdada por todas as tabelas descendentes.

Saída:

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

Exemplo 2 - Busque os detalhes da mala de todos os passageiros que receberam um bilhete.

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

Explicação:Este é um exemplo de junção interna em que a tabela pai ticket é unida à sua tabela filha bagInfo.

Saída:

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

Exemplo 3 - Busque os detalhes da perna de voo das malas do passageiro com o número de bilhete 1762344493810.

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

Explicação: Este é um exemplo de junção interna em que a tabela pai ticket é unida ao seu descendente flightlegs. Uma tabela descendente pode ser qualquer nível hierarquicamente abaixo de uma tabela (Por exemplo, flightLegs é o filho de bagInfo, que é o filho de ticket, portanto, flightLegs é um descendente de ticket). O resultado é então filtrado para um número de ticket específico.

Saída:

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

Exemplo 4 - Encontre o número de lúpulo para todas as malas de um passageiro com o número do bilhete 1762355527825 . Se houver várias malas submetidas a check-in para um passageiro, o número de saltos de todas as malas será exibido.

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

Explicação:Aqui, você agrupa os dados com base no id do repositório (usando GROUP BY) e obtém a contagem de trechos de voo (usando count()) para cada bolsa. Além disso, você filtra os resultados de um número de ticket específico.

Saída:

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

Exemplo 5 - Busque o número do bilhete, o nome do passageiro e os detalhes da bolsa de todos os passageiros.

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

Explicação:Este é um exemplo de uma junção interna de três tabelas, ou seja, a tabela pai ticket e as tabelas irmãos passengerInfo e bagInfo.

Saída:

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

Exemplo 6 - Busque o nome do passageiro, a última estação vista de cuja mala é "MEL"

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

Explicação:Este é um exemplo de uma junção interna das tabelas irmãs passengerInfo e bagInfo. O nome do passageiro cuja mala foi vista pela última vez na estação "MEL" é devolvido.

Saída:

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

Exemplo 7 - Buscar o nome do passageiro cujo destino da rota de voo seja "MEL"

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

Explicação:Esta é uma junção interna de duas tabelas, passengerInfo e flightlegs, que não estão em um relacionamento ancestral-descendente.

Saída:

{"fullName":"Adam Phillips"}

Essa junção entre tabelas que não estão em um relacionamento ancestral-descendente não é possível com Unidade Externa Esquerda e ABLAS NESTADAS.

Exemplos de API de Consulta

Para executar sua consulta, use a API NoSQLHandle.query().

Faça download do código completo TableJoins.java dos exemplos aqui.

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

Para executar sua consulta, use o método borneo.NoSQLHandle.query().

Faça download do código completo TableJoins.py nos exemplos aqui

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

Para executar uma consulta, use a função Client.Query.

Faça download do código completo TableJoins.go dos exemplos aqui.

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)

Para executar uma consulta, use o método query.

JavaScript: Faça download do código completo TableJoins.js dos exemplos aqui.

//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: Faça download do código completo TableJoins.ts dos exemplos aqui.

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

Para executar uma consulta, você pode chamar o método QueryAsync ou chamar o método GetQueryAsyncEnumerable e iterar sobre o enumerável assíncrono resultante.

Faça download do código completo TableJoins.cs nos exemplos aqui.

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