Related computing includes inner join and
outer join. The outer join can be further divided into left join, right join,
and full join. esProc performs these joins conveniently. In the discussion
below, we’ll learn how esProc does the job using the emp table and the sOrder table
along with several examples.
Table structure:
The emp
table stores the employee records, which does not include the record of the employee
whose EId is equal to 1. The sOrder
table stores the order records, in which SellerId field
corresponds to the EId field in emp table,
and the record whose SellerId is equal to 2 isn’t included. Below are the
selections from the two tables.
emp table:
sOrder table:
Tip: You can load data from the database or
the TXT file, for example:
sOrder=esProc.query("select * from
sOrder")
emp=file("e:\\emp.txt").import@t()
An inner join is also called a natural join
or a normal join. In the data retrieved from the two or more to-be-joined
tables, only that satisfying the join-predicate will be put to the result.
For
tables sOrder and emp, the result of inner join should not
include the records whose EId is equal to 1 or 2.
Code:
result1=join(sOrder:s,SellerId;
emp:e,EId)
Computing result:
In the above result table of the join, s column represents the records from the sOrder table, and e column represents the records from the emp table. Click the hyperlinks to view the records in detail:
Description
It can
be seen that there is not any record whose EId equals 1 or 2 in the computing
result.
The
result of join can be used directly for computation, for example: what is the
sales amount of each department? The code is as follows:
result1.groups(e.Dept;sum(s.Amount))
The result is this:
With join function, multiple tables can be associated
by just using the semicolon to separate these tables. Suppose there is a 3rd performance table whose empID field also
corresponds to EId field in emp table,
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 of the left table must be obtained and all put to the result
set even if there is no matching records in the query result of the right table. Such join is called left join.
For the sOrder table and emp table,
after
a left join, all records of sOrder table
will all be listed. Since there is no record whose Eld is equal to 1 in the emp table, several data entries will be null.
Code:
Computing result:
For the first four records in the s, their SellerId is equal to 1, as shown below:
The join function performs inner join by default. It will perform the
left join when using 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 of the right table must be obtained
and all put to the result set even if there is no corresponding records in the query
result of the left table. For the two tables,
all records in the emp table will be
listed after a right join. Since there is no record whose SellerID is equal to
2 in the sOrder table, several data
entries will be blank.
The right join
can be replaced with the left join. The code is:
result2=join@1(emp:e,EId
;sOrder:s,SellerId)
Computing result:
Example 3: Full Join:
There is another outer join often referred
as the full join. The full join is to associate all records from the tables.
The empty records may exist in both left and right sides.
In this case,
for tables sOrder and emp, all their records will be. However,
there are several null data entries on both sides, having no corresponding records.
result3=join@f(sOrder:s,SellerId;emp:e,EId)
Computing result:
Tip:
When using the letter f in the function
option, like join@f(...), there will a full join.
No comments:
Post a Comment