Join in BigObject

BigObject supports LEFT JOIN, INNER JOIN, OUTER JOIN, and simple range join. In addition, BigObject supports implicit LEFT JOIN and INNER JOIN in a simpler syntax if star schema is explicitly represented in table column names, as described in section Table Object and Data Type.

Implicit LEFT JOIN

The user can select columns in dimension tables directly in a SELECT without explicit LEFT JOIN clause if star schema is explicitly related in tables. For example, the following statement can retrieve each customer's gender, which is contained in Customer table, for each record in sales table.

SELECT Customer.gender FROM sales

The LEFT JOIN operation is performed implicitly in BigObject. In other words, the above SELECT statement is equivalent to the following SELECT with LEFT JOIN clause.

SELECT Customer.gender FROM sales LEFT JOIN Customer ON sales.`Customer.id` = Customer.`id`

Implicit INNER JOIN

The user can select columns in dimension tables directly in a SELECT without explicit INNER JOIN clause if star schema is explicitly related in tables. For example, the following statement can retrieve each customer's gender for those records in sales table whose customer id is matched in Customer table.

SELECT Customer.gender FROM sales, Customer

The INNER JOIN operation is performed implicitly in BigObject. In other words, the above SELECT statement is equivalent to the following SELECT with INNER JOIN clause.

SELECT Customer.gender FROM sales INNER JOIN Customer ON sales.`Customer.id` = Customer.`id`

FULL LEFT/INNER JOIN

Either (implicit) LEFT JOIN or (implicit) INNER JOIN mentioned above supports only many to one relationship. If the key used to join is not unique in the dimension table, i.e., a many-to-many relationship, use FULL LEFT/INNER JOIN instead in the SELECT statement.

SELECT Customer.gender FROM sales FULL INNER JOIN Customer ON sales.`Customer.id` = Customer.`id`

OUTER JOIN

The outer join combined the result from FULL LEFT JOIN and those rows are not matched in the joined dimension table.

SELECT order_id, Customer.id FROM sales OUTER JOIN Customer ON sales.`Customer.id` = Customer.`id`

RANGE JOIN

BigObject supports a simple range join using BETWEEN predicate, such as T1.c BETWEEN T2.c1 and T2.c2. Each row in T1 is joined with rows in T2 if c1 <= c <= c2.

SELECT * FROM T1 INNER JOIN T2 ON T1.value BETWEEN T2.v1 and T2.v2