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 1 - 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 2 - Table A

ida	a1
1	a1
2	a2
3	a3

Table 3 - Table B

(ida)	idb	b1
1	1	b1
2	2	b2

(ida) is the inherited shard key from table A.

Table 4 - 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 5 - 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 - 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 7 - 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 8 - 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 9 - 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 10 - 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 11 - 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 12 - 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 13 - 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 14 - 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 15 - 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.