Inner Join vs LOJ vs NESTED TABLES

Tables in a hierarchy can be joined using the following types of joins: Inner Join, Left Outer Join and NESTED TABLES.

Table 6-14 Inner Join vs LOJ vs NESTED TABLES

Inner Join Left Outer Join NESTED TABLES
Queries multiple tables in the same hierarchy Queries multiple tables in the same hierarchy Queries multiple tables in the same hierarchy
Not an ANSI-SQL Standard ANSI-SQL Standard Not an ANSI-SQL Standard
Supports joining of tables that are in an ancestor-descendant relationship as well as tables that are not in an ancestor-descendant relationship Supports only joining of tables that are in an ancestor-descendant relationship Supports joining of tables that are in an ancestor-descendant relationship. Performs a union of sibling tables.
Returns only the matching rows Preserves the unmatched rows from the first (left) table, joining them with a NULL row in the second (right) table Preserves the unmatched rows from the first (left) table, joining them with a NULL row in the second (right) table
In case of 'SELECT *' where all the fields are returned, the order of fields in the result-set depends on the order of tables specified in the FROM clause. In case of 'SELECT *' where all the fields are returned, the order of fields in the result-set is always in top-down order. That means the order of output in the result-set is always from the ancestor table first and then the descendant table. This is true irrespective of the order of tables specified in the FROM clause. In case of 'SELECT *' where all the fields are returned, the order of fields in the result-set is always in top-down order. That means the order of output in the result-set is always from the ancestor table first and then the descendant table. This is true irrespective of the order of tables specified in the FROM clause.
Join predicate must check for matching shard keys between the participating tables Join predicate must check for matching primary keys between the participating tables Join predicate need not explicitly check for matching primary keys. It is implicitly done while joining the tables.
In essence, tables having 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.
Let us understand the 3 types of joins with a simple example shown below. Here, Table A is the parent. Table B and G are children of table A, and siblings of each other. C is the child of table B, and a descendant of table A and B. ida, idb, idc, and idg are the primary keys of tables A, B, C, and G respectively. Since A is the root table and a shard key is not explicitly defined, its primary key (ida) is taken as the shard key.
CREATE TABLE IF NOT EXISTS A (
        ida INTEGER,
        a1 string,
        PRIMARY KEY(ida));

CREATE TABLE IF NOT EXISTS A.B (
        idb INTEGER,
        b1 string,
        PRIMARY KEY(idb));

CREATE TABLE IF NOT EXISTS A.B.C (
        idc INTEGER,
        c1 string,
        PRIMARY KEY(idc));

CREATE TABLE IF NOT EXISTS A.G (
        idg INTEGER,
        g1 string,
        PRIMARY KEY(idg));

Table 6-15 Table A

ida a1
1 a1
2 a2
3 a3

Table 6-16 Table B

(ida) idb b1
1 1 b1
2 2 b2
(ida) is the inherited shard key from table A.

Table 6-17 Table C

(ida) (idb) idc c1
1 1 1 c1
2 2 2 c2
3 1 3 c3
(ida) is the inherited shard key from table A. (idb) is the inherited primary key from table B.

Table 6-18 Table G

(ida) idg g1
1 1 g1
2 2 g2
(ida) is the inherited shard key from table A.

Joining Parent-Child Tables

Inner Join:

Parent table A is joined with descendant table B. Note that only the matching rows are returned. The unmatched row from table A with ida 3, is not returned in the output.
SELECT * FROM A a, A.B b WHERE a.ida = b.ida ORDER BY a.ida

Table 6-19 Combined Table AB (every row in table A is combined with every row in table B)

ida a1 (ida) idb b1
1 a1 1 1 b1
1 a1 2 2 b2
2 a2 1 1 b1
2 a2 2 2 b2
3 a3 1 1 b1
3 a3 2 2 b2

Table 6-20 Result after inner join (based on shard equality predicate)

ida a1 (ida) idb b1
1 a1 1 1 b1
2 a2 2 2 b2
Output: 
{"a":{"ida":1,"a1":"a1"},"b":{"ida":1,"idb":1,"b1":"b1"}}
{"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"}}
2 rows returned

Left Outer Join and NESTED TABLES:

Parent table A is joined with descendant table B. The unmatched row (ida 3) from table A is also returned with a null from table B. In this case, the behavior is the same for both left outer join and NESTED TABLES.

LOJ Query:
SELECT * FROM A a LEFT OUTER JOIN A.B b ON a.ida = b.ida ORDER BY a.ida
NESTED TABLES Query: 
SELECT * FROM NESTED TABLES (A a descendants (A.B b)) ORDER BY a.ida

Table 6-21 Result after LOJ/NESTED TABLES

ida a1 (ida) idb b1
1 a1 1 1 b1
2 a2 2 2 b2
3 a3 null null null
Output: 
{"a":{"ida":1,"a1":"a1"},"b":{"ida":1,"idb":1,"b1":"b1"}}
{"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"}}
{"a":{"ida":3,"a1":"a3"},"b":null}

Joining Sibling Tables

Inner Join:

In the below query, an inner join of tables A, B and G is performed. A is the parent, B and G are sibling tables.
SELECT * FROM A a, A.B b, A.G g WHERE a.ida = b.ida AND b.ida = g.ida

Table 6-22 Combined table AB (every row in table A is combined with every row in table B)

ida a1 (ida) idb b1
1 a1 1 1 b1
1 a1 2 2 b2
2 a2 1 1 b1
2 a2 2 2 b2
3 a3 1 1 b1
3 a3 2 2 b2

Table 6-23 Combined table ABG (every row in table AB is combined with every row in table G)

ida a1 (ida) idb b1 (ida) idg g1
1 a1 1 1 b1 1 1 g1
1 a1 1 1 b1 2 2 g2
1 a1 2 2 b2 1 1 g1
1 a1 2 2 b2 2 2 g2
2 a2 1 1 b1 1 1 g1
2 a2 1 1 b1 2 2 g2
2 a2 2 2 b2 1 1 g1
2 a2 2 2 b2 2 2 g2
3 a3 1 1 b1 1 1 g1
3 a3 1 1 b1 2 2 g2
3 a3 2 2 b2 1 1 g1
3 a3 2 2 b2 2 2 g2

Table 6-24 Result after inner join (based on shard key equality predicate)

ida a1 (ida) idb b1 (ida) idg g1
1 a1 1 b1 1 1 1 g1
2 a2 2 b2 2 2 2 g2
Output: 
{"a":{"ida":1,"a1":"a1"},"b":{"ida":1,"idb":1,"b1":"b1"},"g":{"ida":1,"idg":1,"g1":"g1"}}
{"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"},"g":{"ida":2,"idg":2,"g1":"g2"}}

2 rows returned

Left Outer Join:

Sibling join is not possible.
SELECT * FROM A a LEFT OUTER JOIN A.B b ON a.ida=b.ida LEFT OUTER JOIN A.G g ON a.ida = g.ida
Output: 
Error handling command SELECT * FROM A a LEFT OUTER JOIN A.B b ON a.ida=b.ida LEFT OUTER JOIN A.G g ON a.ida = g.ida: Error: at (1, 75) Table A.G is not descendant of table A.B. Tables in left-outer-joins must appear in top-down order after the target table
NESTED TABLES: 
SELECT * FROM NESTED TABLES (A a descendants (A.b b, A.G g))
Union of sibling tables B and G is performed

Table 6-25 Union of sibling tables B and G

(ida) idb b1 (ida) idg g1
1 1 b1 null null null
2 2 b2 null null null
null null null 1 1 g1
null null null 2 2 g2

Table 6-26 Result after NESTED TABLES join (LOJ of A with union of B and G)

ida a1 (ida) idb b1 (ida) idg g1
1 a1 1 1 b1 null null null
1 a1 null null null 1 1 g1
2 a2 2 2 b2 null null null
2 a2 null null null 2 2 g2
3 a3 null null null null null null
Output: 
{"a":{"ida":1,"a1":"a1"},"b":{"ida":1,"idb":1,"b1":"b1"},"g":null}
{"a":{"ida":1,"a1":"a1"},"b":null,"g":{"ida":1,"idg":1,"g1":"g1"}}
{"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"},"g":null}
{"a":{"ida":2,"a1":"a2"},"b":null,"g":{"ida":2,"idg":2,"g1":"g2"}}
{"a":{"ida":3,"a1":"a3"},"b":null,"g":null}

5 rows returned

Joining Tables that are not in an Ancestor-Descendant Relationship

Inner Join:

Table G is joined with C. Parent of table G is A and parent of table C is B. Table G and C are not in an ancestor-descendant relationship.
SELECT * FROM A.G g, A.B.C c WHERE g.ida=c.ida

Table 6-27 Combined Table GC (every row in table G is combined with every row in table C)

(ida) idg g1 (ida) (idb) idc c1
1 1 g1 1 1 1 c1
1 1 g1 2 2 2 c2
1 1 g1 3 1 3 c3
2 2 g2 1 1 1 c1
2 2 g2 2 2 2 c2
2 2 g2 3 1 3 c3

Table 6-28 Result after inner join (based on shard key equality predicate)

(ida) idg g1 (ida) (idb) idc c1
1 1 g1 1 1 1 c1
2 2 g2 2 2 2 c2

NESTED TABLES and LOJ cannot be used to join tables that do not have an ancestor-descendant relationship between them.