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()
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)
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:
Computing
result:
For
the first four records in the s, their SellerId equal to 1, as shown below:
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.
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(...).
没有评论:
发表评论