Different Scenarios of Using an Inner Join

To understand the different scenarios of using an inner join, let us create a few hierarchical tables and populate them with some data.

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

INSERT INTO A VALUES(1, 'a1')
INSERT INTO A VALUES(2, 'a2')
INSERT INTO A VALUES(3, 'a3')
INSERT INTO A.B VALUES(1, 1, 'b1')
INSERT INTO A.B VALUES(2, 2, 'b2')
INSERT INTO A.B.C VALUES(1, 1, 1, 'c1')
INSERT INTO A.B.C VALUES(2, 2, 1, 'c2')
INSERT INTO A.B.C VALUES(3, 3, 1, 'c3')
INSERT INTO A.G VALUES(1, 1, 'g1')
INSERT INTO A.G VALUES(2, 2, 'g2')

Join with Descendant Table:

Example 1a:

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

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

Example 1b:

Parent table A is joined with descendant table C. A descendant table can be any level hierarchically below a table (For example C is the child of B which is the child of A, so C is a descendant of A).

SELECT * FROM A a, A.B.C c WHERE a.ida = c.ida ORDER BY a.ida

Output:

{"a":{"ida":1,"a1":"a1"},"c":{"ida":1,"idb":1,"idc":1,"c1":"c1"}}
{"a":{"ida":2,"a1":"a2"},"c":{"ida":2,"idb":2,"idc":1,"c1":"c2"}}
{"a":{"ida":3,"a1":"a3"},"c":{"ida":3,"idb":3,"idc":1,"c1":"c3"}}

 3 rows returned

Join with Ancestor Table:

Example 1a:

Table B is joined with ancestor table A.

SELECT * FROM A.B b, A a WHERE a.ida = b.ida ORDER BY a.ida

Output:

{"b":{"ida":1,"idb":1,"b1":"b1"},"a":{"ida":1,"a1":"a1"}}
{"b":{"ida":2,"idb":2,"b1":"b2"},"a":{"ida":2,"a1":"a2"}}

2 rows returned

Multiple Table Join:

Example 1:

Table C is joined with ancestor tables A and B.

SELECT * FROM A.B.C c, A a ,A.B b WHERE c.ida = b.ida AND c.ida = a.ida ORDER BY c.ida, c.idb

Output:

{"c":{"ida":1,"idb":1,"idc":1,"c1":"c1"},"a":{"ida":1,"a1":"a1"},"b":{"ida":1,"idb":1,"b1":"b1"}}
{"c":{"ida":2,"idb":2,"idc":1,"c1":"c2"},"a":{"ida":2,"a1":"a2"},"b":{"ida":2,"idb":2,"b1":"b2"}}

2 rows returned

Sibling Join:

Example 1:

Table B is joined with sibling table G. Both the tables are children of table A.

SELECT * FROM A.B b,A.G g WHERE b.ida=g.ida

Output:

{"b":{"ida":2,"idb":2,"b1":"b2"},"g":{"ida":2,"idg":2,"g1":"g2"}}
{"b":{"ida":1,"idb":1,"b1":"b1"},"g":{"ida":1,"idg":1,"g1":"g1"}}

2 rows returned

Join with Ancestor and Descendant Tables:

Example 1:

Table B is joined with ancestor A and descendant C.

SELECT * FROM A.B b ,A a ,A.B.C c WHERE c.ida = a.ida AND b.ida = a.ida

Output:

{"b":{"ida":1,"idb":1,"b1":"b1"},"a":{"ida":1,"a1":"a1"},"c":{"ida":1,"idb":1,"idc":1,"c1":"c1"}}
{"b":{"ida":2,"idb":2,"b1":"b2"},"a":{"ida":2,"a1":"a2"},"c":{"ida":2,"idb":2,"idc":1,"c1":"c2"}}

2 rows returned

Join with Table that is neither an Ancestor nor a Descendant:

Example 1:

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

Output:

{"g":{"ida":1,"idg":1,"g1":"g1"},"c":{"ida":1,"idb":1,"idc":1,"c1":"c1"}}
{"g":{"ida":2,"idg":2,"g1":"g2"},"c":{"ida":2,"idb":2,"idc":1,"c1":"c2"}}

Join Predicates:

The WHERE clause must contain join predicates on all the shard key columns of all the participating tables, else the query returns an error. In the query below, the shard key of table A is not matched in the join predicate, hence the query returns an error.

SELECT * FROM A.B b, A a ,A.B.C c WHERE c.ida = b.ida

Output:

Error handling command SELECT * FROM A.B b ,A a ,A.B.C c WHERE c.ida = b.ida: Error: at (1, 0) Tables are not joined on their common shard key