The main actions with tables include:
- UNION. In case there are two or more tables of the same structure. It is necessary to compose one result table from them (Union into one table).
- JOIN. In the case when the source tables are of different structure, and it is necessary to supplement the source table with data that is not in it, table Joins are used.
Actions with tables are based on relational algebra (see more Wiki > Relational algebra )
We’ll try to explain the essence of these operations in easy and understandable terms.
Table UNION
We have two or more tables of the same structure (same columns), and we need to make one result table from them (combine into one table).
For example, we store information about the balances of goods in a warehouse in the Register Goods, and about the reserves of goods in the Register Reserves. And we need to get information about the availability of goods in total: how many goods we have in stock.
To solve this task, we get the balances from the Reserves Register, then we get the balances from the Goods Register. And then to the resulting table obtained from the Register Goods, add all the records received from the Register Reserves. See Fig. 1.
HubCloud Syntax:
Goods | GroupBy ( Product, Quantity ) ; Reserve | GroupBy ( Product, Quantity ) | UNION ($result_1, )
Table JOIN
We have source tables of different structures, and it is necessary to supplement the source table with data that is not in it, table JOIN are used.
For example. We have a table with the quantity of the goods in stock and a price list table. We need to find how much is the total of balances.
To do this, we join the tables and then perform the calculations. See Fig.2.
HubCloud Syntax:
ProductBalance | GroupBy ( Product, Quantity ) ; PriceList | Select ( Product, Price ) | LeftJoinAuto ( $result_1, result_1.Product = Product ) | Compute ( Total, Quantity * Price ) | Sum ( Total )