상위-하위 테이블과 Inner Join 사용

JOIN은 두 개 이상의 테이블에서 행 사이의 관련 열을 기반으로 행을 결합하는 데 사용됩니다. 계층형 테이블에서 하위 테이블은 상위 테이블의 기본 키 열을 상속합니다. 이 작업은 하위의 CREATE TABLE 문에 상위 열을 포함하지 않고 암시적으로 수행됩니다. 계층의 모든 테이블에는 동일한 샤드 키 열이 있습니다.

내부 조인은 Oracle NoSQL Database Cloud Service에서 동일한 테이블 계층에 속하는 테이블을 결합하는 데 사용되는 조인 유형 중 하나입니다.

내부 조인 개요

Inner Join은 관련 열 또는 그 사이의 필드에 적용된 조인 술어를 기반으로 두 개 이상의 테이블에서 행을 조합하여 새 행을 생성하는 작업입니다. 결과 집합에는 조인 술어를 만족하는 조합된 행만 포함됩니다.

개념적으로 Inner Join은 다음과 같이 작동합니다.

세 개의 테이블 A, B, C에 대한 Inner Join을 수행해야 한다고 가정합니다. 테이블 A와 B가 먼저 조인됩니다. 즉, 테이블 A에 N개 행과 n개 열이 있고 테이블 B에 M개 행과 m개 열이 있는 경우 테이블 A의 모든 행이 테이블 B의 모든 행과 조인됩니다. 따라서 결과 테이블 AB에는 (N * M) 행과 (n + m) 열이 있습니다. 마찬가지로 테이블 AB도 테이블 C와 조인되어 테이블 ABC를 형성합니다. 그런 다음 WHERE 절의 조인 술어가 ABC 테이블에 적용됩니다. 조인 술어는 조인된 테이블의 모든 샤드 키 열 사이에 등호 술어를 포함해야 합니다. 최종 결과 집합에는 참여하는 테이블의 일치하는 행만 포함됩니다.

SELECT 문의 FROM 절에 조인할 테이블과 WHERE 절의 조인 술어를 지정합니다. 조인 술어는 조인될 하나 이상의 테이블에서 열 또는 필드를 참조하고 해당 열에 적용되어야 하는 필터 조건을 지정하는 술어입니다. Inner join의 경우, WHERE 절은 참여하는 테이블의 모든 샤드 키에 대해 등호 술어를 포함해야 합니다.

테이블의 모든 필드가 반환되는 'SELECT' 절과 함께 '*'를 사용하는 경우 결과 집합의 필드 순서는 FROM 절에서 테이블을 지정하는 순서에 따라 달라집니다. SELECT 절에 필드 리스트를 제공할 경우 결과 집합의 필드 순서는 SELECT 절에 지정된 대로입니다.

Inner Join을 수행하는 동안 다음 사항을 적용할 수 있습니다.

내부 조인은 주로 다음과 같은 측면에서 NESTED TABLES 및 왼쪽 외부 조인과 다릅니다.

본질적으로 상위 종속 관계가 있는 테이블은 세 가지 유형의 조인을 사용하여 조인될 수 있습니다. 사용 사례에 따라 이 중 하나를 사용하도록 선택할 수 있습니다. 조인할 테이블이 상위 멤버-하위 멤버 관계에 없으면 내부 조인을 사용해야 합니다.

Inner Join을 사용하는 예제

항공사 수하물 추적 애플리케이션을 고려해 보십시오. 모든 항공권 번호에는 승객과 수하물이 연결되어 있습니다. 루트 테이블은 ticket이며 2개의 하위 테이블 passengerInfo 및 baggageInfo가 있습니다. passengerInfo 테이블에는 승객의 세부정보가 포함되어 있으며, baggageInfo에는 승객이 체크인한 가방의 세부정보가 포함되어 있습니다. 이 가방은 여러 중개 스테이션을 통해 이동을 통해 추적됩니다. 이 추적 정보는 baggageInfo 테이블의 하위인 flightlegs라는 테이블에 캡처됩니다.

parentchildtbls_loaddata.sql 스크립트를 다운로드하여 아래와 같이 실행합니다. 이 스크립트는 예제에 사용된 테이블을 생성하고 데이터를 테이블로 로드합니다.

parentchildtbls_loaddata.sql에는 다음이 포함됩니다.

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

다음은 생성된 테이블입니다.

이제 내부 조인에 대한 몇 가지 예제 SQL 질의를 살펴보겠습니다.

예 1 - 티켓 번호가 1762324912391인 승객의 세부정보를 인출합니다.

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

설명:상위 테이블 티켓이 하위 테이블 passengerInfo와 조인되고 결과를 제한하기 위해 필터가 적용되는 내부 조인의 예입니다. 여기서 샤드 키는 ticketNo입니다. 루트 테이블을 만드는 동안 샤드 키가 명시적으로 지정되지 않은 경우 루트 테이블의 기본 키가 샤드 키로 사용됩니다. 이 샤드 키는 모든 종속 테이블에서 상속됩니다.

출력:

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

예 2 - 티켓을 발행한 모든 승객의 가방 세부 정보를 가져옵니다.

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

설명:상위 테이블 ticket이 해당 하위 테이블 bagInfo과 조인되는 내부 조인의 예입니다.

출력:

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

예 3 - 티켓 번호가 1762344493810인 승객 가방의 항공편 다리 세부정보를 가져옵니다.

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

설명: 상위 테이블 ticket이 종속 항목 flightlegs과 조인되는 내부 조인의 예입니다. 하위 테이블은 테이블 아래에 계층적으로 모든 레벨이 될 수 있습니다. 예를 들어, flightLegs는 ticket의 하위인 bagInfo의 하위이므로 flightLegs는 ticket의 하위입니다. 그런 다음 특정 티켓 번호에 대한 결과가 필터링됩니다.

출력:

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

예 4 - 티켓 번호가 1762355527825인 승객의 모든 가방에 대한 홉 수를 찾습니다. 승객에 대해 체크인된 가방이 여러 개 있는 경우 모든 가방에 대한 홉 수가 표시됩니다.

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

설명:여기서 GROUP BY를 사용하여 가방 ID를 기반으로 데이터를 그룹화하고 모든 가방에 대해 count()를 사용하여 비행 다리 수를 가져옵니다. 또한 특정 티켓 번호에 대한 결과를 필터링합니다.

출력:

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

예 5 - 모든 승객의 티켓 번호, 승객 이름 및 가방 세부 정보를 가져옵니다.

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

설명:이 예제는 세 개의 테이블, 즉 상위 테이블 ticket과 형제 테이블 passengerInfo 및 bagInfo의 내부 조인의 예입니다.

출력:

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

예 6 - 가방이 "MEL"인 마지막 본 역인 승객의 이름을 가져옵니다.

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

설명:동위 테이블 passengerInfo 및 bagInfo의 내부 조인의 예입니다. "MEL"역에서 마지막으로 본 가방의 승객 이름이 반환됩니다.

출력:

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

예 7 - 항공편 경로 목적지가 "MEL"인 승객의 이름 가져오기

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

설명:상위 종속 관계에 없는 두 테이블 passengerInfo 및 flightlegs의 내부 조인입니다.

출력:

{"fullName":"Adam Phillips"}

Left Outer Join 및 NESTED TABLES에서는 상위 종속 관계에 없는 테이블 간의 이러한 조인을 사용할 수 없습니다.

Query API 예제

질의를 실행하려면 NoSQLHandle.query() API를 사용합니다.

TableJoins.java 예제를 통해 여기에서 전체 코드를 다운로드하십시오.

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

query를 실행하려면 borneo.NoSQLHandle.query() 메소드를 사용합니다.

TableJoins.py 예제를 통해 여기에서 전체 코드를 다운로드하십시오.

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

질의를 실행하려면 Client.Query 함수를 사용합니다.

TableJoins.go의 전체 코드를 여기에서 다운로드하십시오.

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)

질의를 실행하려면 query 메소드를 사용합니다.

JavaScript: TableJoins.js 예제에서 전체 코드를 다운로드합니다.

//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: TableJoins.ts의 전체 코드를 여기에서 다운로드하세요.

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

질의를 실행하기 위해 QueryAsync 메소드를 호출하거나 GetQueryAsyncEnumerable 메소드를 호출하여 결과 비동기 열거형을 반복할 수 있습니다.

TableJoins.cs 예제를 통해 여기에서 전체 코드를 다운로드하십시오.

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