Limitations of LOJ

There are some limitations while using an LOJ in an Oracle NoSQL Database.

Joins with Siblings

LOJs in an Oracle NoSQL Database cannot be applied to siblings. LOJ can be used in tables that only have a direct hierarchical relationship.

Example 6-116 Error scenario: LOJ on siblings

When you try to join siblings, you get the following error:

SELECT * FROM A.B b LEFT OUTER JOIN A.G g ON b.ida=g.ida

Output:

Error handling command select * from A.B b LEFT OUTER JOIN A.G g on b.ida=g.ida:
Error: at (1, 40) Table A.G is neither ancestor nor descendant of the target table A.B

Order of tables in an LOJ

While using multiple joins in a single statement, the tables in an LOJ must appear in top-down order after the target table. A child table cannot be to the left of its parent in a multiple join operation.

Example 6-117 Multiple LOJ of the parent table with its ancestors in the wrong order

In this example, an error is thrown as the parent table A cannot be at the right of its descendant A.B.

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

Output:

Error handling command SELECT * FROM A.B.C c LEFT OUTER JOIN A.B b ON c.ida = b.ida
and c.idb = b.idb LEFT OUTER JOIN A a ON c.ida = a.ida:
Error: at (3, 22) Table A is not descendant of table A.B.
Tables in left-outer-joins must appear in top-down order after the target table

Join predicates

In an LOJ, the ON expression of the right table must contain all the required join predicates that link the parent and child tables. Missing any join predicate results in an exception.

Example 6-118 If the right table is an ancestor of the target table, the ON expression must contain join-predicates on all the primary key columns of the right table

In this example, one of the join predicates on the primary key column idb of the right table is missing which throws an error.

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

Output:

Error handling command 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:
Error: A join predicate is missing from ON clause of table A.B : c.idb = b.idb

Example 6-119 If the right table is a descendant of the target table, the ON expression must contain join-predicates on all the primary key columns of the left table

In this example, one of the join predicates on the primary key column idb of the left table is missing which throws an error.

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

Output:

Error handling command SELECT * FROM A.B b LEFT OUTER JOIN A.B.C c ON b.ida = c.ida:
Error: A join predicate is missing from ON clause of table A.B.C : b.idb = c.idb