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