Different scenarios of using an LOJ

To understand different scenarios of using an LOJ, 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 Descendants

You can join a target table with its descendant. All rows of the target table(left table) will be returned and for those rows where there is no match in the descendant's table( right table), NULL values are populated as shown in the examples below.

Example 1: A target table A is joined with its child table A.B

SELECT * FROM A a LEFT OUTER JOIN A.B b
ON a.ida = b.ida ORDER BY a.ida;

{"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}

Example 2: A target table A is joined with its descendant A.B.C

SELECT * FROM A a LEFT OUTER JOIN A.B.C c 
ON a.ida = c.ida ORDER BY a.ida;

{"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"}}

Join with Ancestors

You can join a target table with its ancestor. All rows of the target table(left table) will be returned and for those rows where there is no match in the ancestor table( right table), NULL values are populated as shown in the examples below.

Example 1: A target table A.B is joined with its parent table A

SELECT * FROM A.B b LEFT OUTER JOIN A a 
ON a.ida = b.ida ORDER BY a.ida;

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

Example 2: A target table A.B.C is joined with its ancestor A.B

SELECT * FROM A.B.C c LEFT OUTER JOIN A.B b 
ON c.ida = b.ida and c.idb = b.idb;

{"b":null,"c":{"ida":3,"idb":3,"idc":1,"c1":"c3"}}
{"b":{"ida":2,"idb":2,"b1":"b2"},"c":{"ida":2,"idb":2,"idc":1,"c1":"c2"}}
{"b":{"ida":1,"idb":1,"b1":"b1"},"c":{"ida":1,"idb":1,"idc":1,"c1":"c1"}}

Multiple LOJs in one SELECT statement

A target table(A) can be joined to its child(A.B) and the result of the join can be joined to the descendant (A.B.C)of the target table. If the right table is a descendant table, the ON expression should contain join predicates on all the primary key columns of the left table using an "AND" clause. In the example given below, the left table has two primary key columns ( ida and idb). An AND clause is used with these two join predicates (b.ida = c.ida AND b.idb = c.idb).

Example 1: Multiple LOJs of the parent table with its descendants

SELECT * FROM A a LEFT OUTER JOIN A.B b ON a.ida = b.ida
LEFT OUTER JOIN A.B.C c ON b.ida = c.ida AND b.idb = c.idb ORDER BY a.ida;

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

Example 2: Multiple LOJs of the parent table with its ancestors

SELECT * FROM A.B.C c LEFT OUTER JOIN A a ON c.ida = a.ida
LEFT OUTER JOIN A.B b ON c.ida = b.ida AND c.idb = b.idb ORDER BY c.ida, c.idb;

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

Join with an ancestor and a descendant

You can also join a target table to an ancestor and a descendant. First, join the target table to its ancestor and then join the result of this query with the descendant of the target table. Any number of such joins is possible in a single SQL statement. In all the joins, all rows of the left table will be returned and for those rows where there is no match in the right table, NULL values are populated. If the right table is a descendant table, the ON expression should contain join predicates on all the primary key columns of the left table using an "AND" clause.

Example: A target table A.B is joined with its ancestor A, the result of which is joined with its descendant A.B.C

SELECT * FROM A.B b LEFT OUTER JOIN A a ON b.ida = a.ida AND a.a1 = "abc"
LEFT OUTER JOIN A.B.C c ON b.ida = c.ida AND b.idb = c.idb ;

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

Non-join predicate restrictions in an LOJ

An LOJ can additionally have non-join predicates ( that is the columns that are not part of the join) as a restriction to filter data. The result set of an LOJ depends on whether you place the non-join predicate restriction in the ON clause or in the WHERE clause. The non-join predicate in an ON clause is just applied to the join operation, whereas the non-join predicate in the WHERE clause will apply to the entire results-set.

Example: Non-join predicate in the WHERE clause

If a non-join predicate is placed in the WHERE clause, the restriction is applied to the result of the outer join. That is, it removes all the rows for which the WHERE condition is not TRUE. In the example below, you get only one row that matches the WHERE condition as the result set.

SELECT * FROM A a LEFT OUTER JOIN A.B b ON a.ida = b.ida
WHERE b.ida > 1 ORDER BY a.ida;

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

Example: Non-join predicate in the ON clause

If you move the non-join predicate restriction to the ON clause, the result set includes all the rows that meet the ON clause condition. Rows from the right outer table that do not meet the ON condition are populated with NULL values as shown below.

SELECT * FROM A a LEFT OUTER JOIN A.B b ON a.ida = b.ida
AND b.ida > 1 ORDER BY a.ida;

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