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 WHERE… BY ...
JOIN / LEFT JOIN / FULL JOIN T2 WHERE ... BY......
JOIN / LEFT JOIN / FULL JOIN Tn
or
UNION T2 WHERE ... BY......
Example:
|
SELECT ReturnedPmt.sum(Amount) Pamount,Orders.sum(Amount) Oamount ON YearMonth FROM Orders BY ShipDate#YearMonth UNION ReturnedPmt BY RDate#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 RDate#year ,SellerID |
Join tables by multiple fields and calculate total orders amount and payment in each year for each employee ID |
|
SELECT ReturnedPmt.sum(Amount) Pamount, Orders.sum(Amount) Oamount ON YearMonth FROM ReturnedPmt BY RDate#YearMonth Left Join Orders BY ShipDate#YearMonth |
Left join. |
Learning points:
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.