Limitations of LOJ

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. When you try to join siblings, you get an error as shown below.

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

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: Multiple LOJ of the parent table with its ancestors in the wrong order

In the example below, 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;

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 should contain all the required join predicates that link the parent and child tables. Missing any join predicate results in an exception.

Example 1: If the right table is an ancestor of the target table, the ON expression should contain join-predicates on all the primary key columns of the right table.

In the example below, 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 ;

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 2: If the right table is a descendant of the target table, the ON expression should contain join-predicates on all the primary key columns of the left table.

In the example below, 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;

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