Related computing can be: Inner join or outer join. The outer join can be further divided into left join, right join, and full join. With esProc, such kinds of related computing can be easily implemented. In the discussion below, we will introduce the join operations with some examples using the table emp and table sOrder.
Table structure:
The table emp stores the employee data table, in which the employees whose EId equal to 1 are not listed in this table. The table sOrder stores the order data, in which the field SellerId corresponds to the field EId in the table emp, and the orders whose SellerId equal to 2 are not in this table. Part of data is as follows:
Table Emp:
Table sOrder:
Description: Read the data from database or TXT file, for example:
sOrder=esProc.query(“select * from sOrder”)
emp=file(“e:\\emp.txt”).import@t()
Example 1: Inner Join:
Inner join is also called natural join or normal join. The result to retrieve will be put to the results only if the records in two or more join tables all meet the condition for join.
For the table sOrder and table emp, the result will exclude the records whose EId equal to 1 or 2 after inner join.
Code:
result1=join(sOrder:s,SellerId; emp:e,EId)
Computing result:
In the above figure, the column s is populated with the records from table sOrder after join operation, and column e is populated with the records from table emp. Click the hyperlink to view the records in details:
Description
As can be seen, there is not any record whose EId equals to 1 or 2 in the computing result.
Once associated, the computing result can be used directly for computing, for example: What is the sales of each department? The code is as follows:
result1.groups(e.Dept;sum(s.Amount))
The result is as follows:
With join function, the multi-table associating can be performed by just using the semicolon to split these association tables. Suppose if there is a 3rd table performance whose field empID also corresponds to field EId of table emp, then the statement to associate the three tables is like this:
join(sOrder:s,SellerId; emp:e,EId; performance:p,empID)
Example 2: Left Join:
For the pending join query, the query result for the left table must be obtained and put to the result set even if there is no corresponding join condition of query on the right. Such join algorithm is called left join.
For the table sOrder and table emp, after left join, all records of table sOrder will all be listed out. Since there is no record whose Eld equal to 1 in the table emp, several data entries will be blank.
Code:
result2=join@1(sOrder:s,SellerId;emp:e,EId)
Computing result:
For the first four records in the s, their SellerId equal to 1, as shown below:
Description:
The join function performs the inner join by default. It is the left join when using the digit 1 as the function option, i.e. join@1(…)
What the right join indicates is that, for the pending join query, the query result for the right table must be obtained and put to the result set even if there is no corresponding join condition of query on the left. For table sOrder and table emp, all records in the table emp will be listed after right join. Since there is no record whose SellerID equal to 2 in the table sOrder, several data entries will be blank.
The right join can be replaced with the left join, and the relevant code is:
result2=join@1(emp:e,EId ;sOrder:s,SellerId)
Computing result:
Example 3: Full Join:
There is still a kind of outer join called full join. The full join is to associate records from all table. The blank records may exist on both left or right sides.
For the table sOrder and table emp, the records of these two tables will be all listed after full join. Still, there are several blank data entires on both sides, having not found the corresponding relations.
Code:
result3=join@f(sOrder:s,SellerId;emp:e,EId)
Computing result:
Description:When using the letter f in the function option, the join function will perform the full join, like join@f(…).