Using inner join with parent-child tables

A JOIN is used to combine rows from two or more tables, based on a related column between them. In a hierarchical table, the child table inherits the primary key columns of its parent table. This is done implicitly, without including the parent columns in the CREATE TABLE statement of the child. All tables in the hierarchy have the same shard key columns.

An inner join is one of the types of join used to combine tables that belong to the same table hierarchy in an Oracle NoSQL Database Cloud Service.

Overview of Inner Join

An inner join is an operation that produces new rows by combining rows from two or more tables, based on the join predicates applied to related columns or fields between them. The result-set contains only those combined rows that satisfy the join predicates.

Conceptually, an inner join works as follows:

Consider that you need to perform an inner join of three tables A, B and C. The tables A and B are first joined. That is, if table A has N rows and n columns, and table B has M rows and m columns, every row in table A is joined with every row in table B. The resultant table AB would thus have (N * M) rows and (n + m) columns. Similarly table AB is now joined with table C, to form table ABC. The join predicates in the WHERE clause are then applied to the table ABC. Note that the join predicates must include equality predicates between all the shard key columns of the joined tables. The final result-set contains only the matching rows from the participating tables.

You specify the tables to be joined in the FROM clause of the SELECT statement and the join predicates in the WHERE clause. A join predicate is a predicate that references the columns or fields from one or more tables that are to be joined and specifies the filter conditions that need to be applied on them. In the case of inner join, the WHERE clause must include the equality predicate on all shard keys of the participating tables.

If you use a ‘*’ with the ‘SELECT’ clause, wherein all the fields in the tables are returned, the order of fields in the result-set depends on the order in which you specify the tables in the FROM clause. If you provide a list of fields in the SELECT clause, then the order of the fields in the result-set is as specified in the SELECT clause.

While performing an inner join, the following are applicable:

An inner join differs from NESTED TABLES and left outer join primarily in the following aspects:

In essence, tables having an ancestor-descendant relationship between them can be joined using any of the three types of join. You can choose to use one of them based on your use case. If the tables to be joined are not in an ancestor-descendant relationship, then inner join must be used.

Examples using Inner Join

Consider an airline baggage tracking application. For every flight ticket number, there is a passenger and their baggage associated with it. The root table is ticket, and it has 2 child tables passengerInfo and baggageInfo. The passengerInfo table contains the details of the passenger and the baggageInfo contains details of the bags checked in by the passenger. These bags are tracked through their transit through multiple intermediary stations. This tracking information is captured in a table called flightlegs which is the child of the baggageInfo table.

Download the script parentchildtbls_loaddata.sql and run it as shown below. This script creates the tables used in the example and loads data into the tables.

The parentchildtbls_loaddata.sql contains the following:

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

Following are the tables created:

Let us now see a few example SQL queries for inner join:

Example 1 - Fetch the details of the passenger with ticket number 1762324912391.

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

Explanation:This is an example of an inner join where the parent table ticket is joined with its child table passengerInfo and a filter is applied to restrict the result. Note that the shard key here is ticketNo. If the shard key is not explicitly specified while creating the root table, the primary key of the root table is taken as the shard key. This shard key is inherited by all the descendant tables.

Output:

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

Example 2 - Fetch the bag details of all passengers who have been issued a ticket.

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

Explanation:This is an example of an inner join where the parent table ticket is joined with its child table 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

Example 3 - Fetch the flight leg details of the bags of the passenger with ticket number 1762344493810.

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

Explanation:: This is an example of an inner join where the parent table ticket is joined with its descendant flightlegs. A descendant table can be any level hierarchically below a table (For example flightLegs is the child of bagInfo which is the child of ticket, so flightLegs is a descendant of ticket). The result is then filtered for a particular ticket number.

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

Example 4 - Find the number of hops for all the bags of a passenger with ticket number 1762355527825 . If there are multiple bags checked in for a passenger, then the number of hops for all the bags are displayed.

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

Explanation:Here, you group the data based on the bag id (using GROUP BY) and get the count of flight legs (using count()) for every bag. Additionally, you filter the results for a particular ticket number.

Output:

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

Example 5 - Fetch the ticket number, passenger name, and bag details of all the passengers.

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

Explanation:This is an example of an inner join of three tables, that is, the parent table ticket, and the sibling tables passengerInfo and 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

Example 6 - Fetch the name of the passenger, the last seen station of whose bag is “MEL”

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

Explanation:This is an example of an inner join of the sibling tables passengerInfo and bagInfo. The name of the passenger whose bag was last seen at the “MEL” station is returned.

Output:

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

Example 7 - Fetch the name of the passenger whose flight route destination is “MEL”

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

Explanation:This is an inner join of two tables, passengerInfo and flightlegs, that are not in an ancestor-descendant relationship.

Output:

{"fullName":"Adam Phillips"}

Such a join between tables that are not in an ancestor-descendant relationship is not possible with Left Outer Join and NESTED TABLES.

Query API Examples

To execute your query, you use the NoSQLHandle.query() API.

Download the full code TableJoins.java from the examples here.

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

To execute your query use the borneo.NoSQLHandle.query() method.

Download the full code TableJoins.py from the examples here

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

To execute a query use the Client.Query function.

Download the full code TableJoins.go from the examples here.

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)

To execute a query use query method.

JavaScript: Download the full code TableJoins.js from the examples here.

//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: Download the full code TableJoins.ts from the examples here.

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

To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable.

Download the full code TableJoins.cs from the examples here.

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