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()
Example 1: Inner Join:
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)
Example 2: Left Join:
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:
result2=join@1(sOrder:s,SellerId;emp:e,EId)
Computing result:
For the first four records in the s, their SellerId is equal to 1, as shown below:
Tip:
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.
Code:
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.