Join
Functionality:
Join multiple tables in alignment by their dimensions. The alignment join does not require that users take care of the association between tables and allows setting aggregate dimension for each table.
Syntax:
SELECT D.F, T1.f(...), T2.f(...)
ON D AS A
FROM T1 BY ...
UNION T2 BY ...
Example:
|
SELECT ReturnedPmt.sum(Amount) Pamount,Orders.sum(Amount) Oamount ON YearMonth FROM Orders BY ShipDate#YearMonth UNION ReturnedPmt BY Date#YearMonth |
Join multiple tables together and calculate total orders amount and payment in each month |
|
SELECT ReturnedPmt.sum(Amount) Pamount,Orders.sum(Amount) Oamount ON year,EMPLOYEE FROM Orders BY ShipDate#year,EmployeeID UNION ReturnedPmt BY Date#year ,SellerID |
Join tables by multiple fields and calculate total orders amount and payment in each year for each employee ID |
Tips:
1) DQL UNION is equivalent to SQL FULL JOIN.
2 ) Need to explicitly write the table a field belongs to and the field to which tables are aligned in BY clause for each table association.