Overview of NESTED TABLES

The NESTED TABLES clause specifies the participating tables and separates them into 3 groups. First, the target table from where the data is fetched is specified. Then the ANCESTORS clause, if present, specifies the number of tables that must be ancestors of the target table in the table hierarchy. Finally, the DESCENDANTS clause, if present, specifies the number of tables that must be descendants of the target table in the table hierarchy.

Note:

Semantically, a NESTED TABLES clause is equivalent to a number of left-outer-join operations "centered" around the target table.

Characteristics of NESTED tables:

• Queries multiple tables in the same hierarchy
• It is not an ANSI-SQL Standard
• It supports sibling tables join

Table 4-1 Nested Tables Vs LOJ

Nested Tables	LOJ
Queries multiple tables in the same hierarchy	Queries multiple tables in the same hierarchy
Not an ANSI-SQL Standard	ANSI-SQL Standard
Supports sibling tables join	Does not support sibling table joins

If you want to follow along with the examples, download the script parentchildtbls_loaddata.sql and run it as shown below. This script creates the table used in the example and loads data into the table.

Start your KVSTORE or KVLite and open the SQL.shell.

 java -jar lib/kvstore.jar kvlite -secure-config disable
 java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

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

Using the load command, run the script.

load -file parentchildtbls_loaddata.sql