Uso de la Unión Interna con Tablas Principal-Secundario

Un JOIN se utiliza para combinar filas de dos o más tablas, basándose en una columna relacionada entre ellas. En una tabla jerárquica, la tabla secundaria hereda las columnas de clave primaria de su tabla principal. Esto se realiza de forma implícita, sin incluir las columnas principales en la sentencia CREATE TABLE del secundario. Todas las tablas de la jerarquía tienen las mismas columnas de clave de partición horizontal.

Una unión interna es uno de los tipos de unión que se utilizan para combinar tablas que pertenecen a la misma jerarquía de tablas en Oracle NoSQL Database Cloud Service.

Visión General de Unión Interna

Una unión interna es una operación que produce nuevas filas combinando filas de dos o más tablas, según los predicados de unión aplicados a columnas o campos relacionados entre ellas. El juego de resultados sólo contiene las filas combinadas que cumplen los predicados de unión.

Conceptualmente, una unión interna funciona de la siguiente manera:

Tenga en cuenta que debe realizar una unión interna de tres tablas A, B y C. Las tablas A y B se unen en primer lugar. Es decir, si la tabla A tiene N filas y n columnas, y la tabla B tiene M filas y m columnas, cada fila de la tabla A se une con cada fila de la tabla B. Por lo tanto, la tabla resultante AB tendría (N * M) filas y (n + m) columnas. Del mismo modo, la tabla AB se une ahora a la tabla C para formar la tabla ABC. Los predicados de unión de la cláusula WHERE se aplican a la tabla ABC. Tenga en cuenta que los predicados de unión deben incluir predicados de igualdad entre todas las columnas de clave de partición horizontal de las tablas unidas. El conjunto de resultados final sólo contiene las filas coincidentes de las tablas participantes.

Especifique las tablas que se van a unir en la cláusula FROM de la sentencia SELECT y los predicados de unión en la cláusula WHERE. Un predicado de unión es un predicado que hace referencia a las columnas o campos de una o más tablas que se van a unir y especifica las condiciones de filtro que se deben aplicar a ellas. En el caso de la unión interna, la cláusula WHERE debe incluir el predicado de igualdad en todas las claves de partición horizontal de las tablas participantes.

Si utiliza un '*' con la cláusula 'SELECT', en la que se devuelven todos los campos de las tablas, el orden de los campos en el juego de resultados depende del orden en el que especifique las tablas en la cláusula FROM. Si proporciona una lista de campos en la cláusula SELECT, el orden de los campos en el juego de resultados es el especificado en la cláusula SELECT.

Al realizar una unión interna, se aplica lo siguiente:

Una unión interna difiere de NESTED TABLES y unión externa izquierda principalmente en los siguientes aspectos:

En esencia, las tablas que tienen una relación ascendiente-descendiente entre ellas se pueden unir mediante cualquiera de los tres tipos de unión. Puede elegir usar uno de ellos según su caso de uso. Si las tablas que se van a unir no están en una relación ascendiente-descendiente, se debe utilizar la unión interna.

Ejemplos que utilizan la unión interna

Considere una aplicación de seguimiento de equipaje de la aerolínea. Por cada número de billete de vuelo, hay un pasajero y su equipaje asociado. La tabla raíz es ticket y tiene 2 tablas secundarias passengerInfo y baggageInfo. La tabla passengerInfo contiene los detalles del pasajero y la baggageInfo contiene los detalles de las maletas facturadas por el pasajero. Estas bolsas se rastrean a través de su tránsito a través de múltiples estaciones intermedias. Esta información de seguimiento se captura en una tabla denominada flightlegs, que es el secundario de la tabla baggageInfo.

Descargue la secuencia de comandos parentchildtbls_loaddata.sql y ejecútela como se muestra a continuación. Este script crea las tablas que se utilizan en el ejemplo y carga los datos en las tablas.

parentchildtbls_loaddata.sql contiene lo siguiente:

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

A continuación se muestran las tablas creadas:

Veamos ahora algunos ejemplos de consultas SQL para la unión interna:

Ejemplo 1 - Recupere los detalles del pasajero con el número de billete 1762324912391.

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

Explicación:este es un ejemplo de una unión interna en la que el ticket de la tabla principal se une con su información de pasajeros de la tabla secundaria y se aplica un filtro para restringir el resultado. Tenga en cuenta que la clave de partición horizontal aquí es ticketNo. Si la clave de partición horizontal no se especifica explícitamente al crear la tabla raíz, la clave primaria de la tabla raíz se toma como clave de partición horizontal. Esta clave de partición horizontal la heredan todas las tablas descendientes.

Salida:

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

Ejemplo 2 - Recupere los detalles de la bolsa de todos los pasajeros que han recibido un boleto.

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

Explicación:este es un ejemplo de una unión interna en la que la tabla principal ticket se une con su tabla secundaria bagInfo.

Salida:

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

Ejemplo 3 - Recupere los detalles del tramo de vuelo de las bolsas del pasajero con el número de billete 1762344493810.

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

Explicación: ejemplo de una unión interna en la que la tabla principal ticket está unida con su descendiente flightlegs. Una tabla descendente puede ser cualquier nivel jerárquicamente debajo de una tabla (por ejemplo, flightLegs es el secundario de bagInfo, que es el secundario de ticket, por lo que flightLegs es un descendiente de ticket). El resultado se filtra para un número de ticket determinado.

Salida:

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

Ejemplo 4: busque el número de saltos para todas las bolsas de un pasajero con el número de billete 1762355527825 . Si hay varias bolsas admitidas para un pasajero, se muestra el número de saltos de todas las bolsas.

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

Explicación: aquí, se agrupan los datos según el ID de bolsa (mediante GROUP BY) y se obtiene el recuento de tramos de vuelo (mediante count()) para cada bolsa. Además, puede filtrar los resultados para un número de ticket determinado.

Salida:

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

Ejemplo 5 - Recuperar el número de billete, el nombre del pasajero y los detalles de la bolsa de todos los pasajeros.

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

Explicación:este es un ejemplo de una unión interna de tres tablas, es decir, la tabla principal ticket y las tablas hermanas passengerInfo y bagInfo.

Salida:

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

Ejemplo 6 - Recuperar el nombre del pasajero, la última estación vista de cuya bolsa es "MEL"

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

Explicación:este es un ejemplo de una unión interna de las tablas hermanas passengerInfo y bagInfo. Se devuelve el nombre del pasajero cuya bolsa fue vista por última vez en la estación "MEL".

Salida:

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

Ejemplo 7 - Recuperar el nombre del pasajero cuyo destino de la ruta de vuelo es "MEL"

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

Explicación: se trata de una unión interna de dos tablas, passengerInfo y flightlegs, que no están en una relación ascendiente-descendente.

Salida:

{"fullName":"Adam Phillips"}

Esta unión entre tablas que no están en una relación ascendiente-descendiente no es posible con Unión externa izquierda y Tablas anidadas.

Ejemplos de API de consulta

Para ejecutar la consulta, utilice la API NoSQLHandle.query().

Descargue el código completo TableJoins.java de los ejemplos aquí.

/* 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 ejecutar la consulta, utilice el método borneo.NoSQLHandle.query().

Descargue el código completo TableJoins.py de los ejemplos aquí

# 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 ejecutar una consulta, utilice la función Client.Query.

Descargue el código completo TableJoins.go de los ejemplos aquí.

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 ejecutar una consulta, utilice el método query.

JavaScript: descargue el código completo TableJoins.js de los ejemplos aquí.

//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: descargue el código completo TableJoins.ts de los ejemplos aquí.

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 ejecutar una consulta, puede llamar al método QueryAsync o llamar al método GetQueryAsyncEnumerable e iterar sobre el enumerable asíncrono resultante.

Descargue el código completo TableJoins.cs de los ejemplos aquí.

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