Previous Next JavaScript must be enabled to correctly display this content

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