support
buy cialis buy valium online buy viagra online

esProc Performs Inner and Outer Joins: Code Examples

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:

esProc_inmemory_inner_outerjoin_1

sOrder table:

esProc_inmemory_inner_outerjoin_2

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:

esProc_inmemory_inner_outerjoin_3

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:

esProc_inmemory_inner_outerjoin_4

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:

esProc_inmemory_inner_outerjoin_5

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:

esProc_inmemory_inner_outerjoin_6

For the first four records in the s, their SellerId is equal to 1, as shown below:

esProc_inmemory_inner_outerjoin_7

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:

esProc_inmemory_inner_outerjoin_8

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:

esProc_inmemory_inner_outerjoin_9

Tip:

When using the letter f in the function option, like join@f(…), there will a full join.

replica franck-muller watches  replica hublot watches  replica panerai watches  replica bvlgari watches  replica patek-philippe watches  replica montblanc watches  replica longines watches  replica hermes watches  replica audemars piguet watches  replica vacheron constantin watches