Sample Code for Implementing Inner or Outer Join in esProc

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(...).

Using SQL in esProc (II)

5 Comparison between common SQL statements and esProc syntax

1)  Select * from

Query results are as follows:

2) Select … from 

Get designated fields from the table. Both A2 and A3 have the same query results as follows:

3) As

Compute FULLNAME according to NAME and SURNAME, and meanwhile, compute AGE according to BIRTHDAY. Basically, both A2 and A3 have the same query results as follows:

Note that AGE is computed in A3 by subtracting years and exact computations will be more complicated since SQL hasn't functions to directly compute age.

4) Where

Query employees who are younger than 30 years old, then compute in esProc using existed results. Query results of A3 are as follows:

Query the same results in A4 with SQL but the syntax is much more complicated. As the process of computing age is inexact, errors occur in the results.

5) Count, sum, avg, max and min 

Query the total number of employees who are younger than 30 years old, compute in esProc using existed results. Query result of A3 is as follows:

This time, A4 uses a more exact method to compute AGE and gets a query result that is consistent with that of A3. But it cannot use the existed results and statements are more complicated.

The usage of SQL functions, such as sum, avg, max and min, is similar to that of count.

6) Distinct

Query which departments does the employee information come from.Both A2 and A3 have the same results. Query results are as follows: 

7)  Order by 

Query employees who are younger than 30 years old, sort them by age in descending order; meanwhile, sort employees of the same age by full name in ascending order. Both A3 and A4 have the same query results as follows:

Since it is complicated to compute age with SQL and existed results cannot be used, this time A4 simplifies statements with nested query. However, the process is still complex.

8) And、or、not and<> 

Query employees who are younger than 30 years old and whose initials of full names are S. Results are as follows:

It can be seen that and is represented in esProc by the operator && and two equal signs == are used to judge whether things are equal or not. These are in line with the customs of many program languages. Similarly, in esProc, or is represented by the operator "||", not by"!", and<>by"!=".

9) Like 

Query full names of employees whose names are ended by a. Query results are as follows:

In using like function, different databases use different wildcard characters. In this example, for instance, percent "%" is used to represent zero or multiple arbitrary characters; while in some other databases, asterisk "*" is used to represent the same things. But with esProc, syntax of any database is the same.

10) Group 

If grouping by departments employees work for, group function can be used in esProcto group records as follows:

It can be seen that the result of grouping with esProc is that records are divided into multiple groups. These groups can be used to perform further computations as required in esProc.

A3 directly computes grouping and summarizing with esProc function while A4 does the job with SQL. They get the same results. SQL doesn’t have the real "group" concept, so it can only perform aggregate computations in query by groups. Results are as follows: 

Using SQL in esProc (I)

In esProc, we can use not only the SQL to retrieve data from databases, but also the preliminary database query results to perform further analyses and operations to solve some complicated problems which are difficult to deal with only with SQL. 

1. Database connection and disconnection
When using SQL to access databases, it should first connect to the designated database. Usually, there are two ways to connect to the database: direct connection in the data source manager or calling functions to connect in cellsets. 

In practice, the database accessed in the data source manager can be called by directly using data source name and are valid while the connection is on; for database accessed through functions, the connection objects it created will be stored as cell values,and it can be called by using cell names and are valid before the called connection objects close.

Similarly, there are two ways for database disconnection as well. Except for calling db.close() function shown in the above example,another way is to close the selected data source in data source manager:

2. Use of simple SQL

Using db.query() function can execute SQL orders in designated database.SQL statements may contain various query clauses and database functions.

A2 executes query on states whose abbreviations begin with N, and sort them by population in descending order. Results are as follows:

SQL statements can also use other data in cellsets as parameters:

A4 executes query on states whose abbreviations fall into designated sequences and sort them by area in ascending order. Results are as follows:

3. SQL that returns no results

If SQL that returns no results is used to access a database, like the use of SQL statements:create, update, delete and so on, then db.execute() function is needed in execution. Meanwhile, since it is not necessary to assign value to cells, expressions begin with ">" instead of "=". For example:

After statements in A2 modifies records in database table STATES, query results of A3 are as follows：

SQL that returns no results can use parameters, too: 

Statements in A4 restore modified records in database STATES to their original values, and query results of A5 are as follows:

4. Use of query results of SQL

Query results of SQL can be used in esProc to perform operations, like filtering, sorting and combination, etc.in order to increase query efficiency or deal with some complicated problems.

In the following examples, executions are performed by connecting to data source demo in data source manager and based on query results of cell A1:

For example, filtering data and searching data of states of designated abbreviations:

Also, aggregation computations can be performed on data. For example, count up the number of states whose abbreviations begin with C:

More significantly, we can group data in databases according to certain requirements, for example, group according to initials of abbreviations:

A2 executes grouping according to the initial of each state's abbreviation. Double-click and see details of each group’s data.

It is thus clear that, different from the SQL grouping method that doesn't provide real grouping and summarizing function, grouping with esProc is the real one, on which further computations can be performed. For example, select groups that contain three or more states, compute the total number and population of the states in these groups:

The final results of A4 are:

Several Methods to Compute Fibonacci Sequence with esProc

Fibonacci Sequence is also called "Rabbit Sequence", because it can be described by a problem related to rabbits. After the second month of their life, a pair of rabbits can give birth to two little ones every month. If all the rabbits could live forever, then how many pair of rabbits are there by the nth month?

During the first and the second month, there is only one pair of rabbits. Then in the third month, the first pair of little ones will be born. Now there are two pairs of rabbits... By the nth month, the increased number of rabbits, compared with the previous month, equals to the total number of rabbits two months ago ( i.e. in the n-2th month). In this way, the total number of rabbits can be expressed by a series: 1, 1, 2, 3, 5, 8, 13... From the third number, each number is the sum of previous two ones.

The principle of this loop computation is that, from the first two terms 1,1, the value of a new term is created by cyclically calculating the sum of the last two terms.

With esProc, the loop computation will be operated in the following manner:

This method uses for n loop to complete looping execution of designated times. Since the values of the first and second term have been decided during the initial setting, the loop computation should be reduced by two times. A3 stores Fibonacci Sequence, B4 computes the sum of last two numbers, statement in C4 is used to add results from B4 into the series.

Computed results in A3 will be:

Or, for x is used in esProc to execute loop computation. If the value of x is true,looping execution of statement block will continue all through, as shown in this figure:

A6 stores Fibonacci Sequence. According to the statement in A7, looping execution begins when the number of terms is less than designated total numbers. Statement in B7, which directly works out new term  to add to the series, is the combination of that in B4 and C4. When computations are finished, results in A6 and A3 are the same.

Or, for looping statement can be replaced in esProc by loop function to realize loop computation:

During initial setting in A9, values of both the zero and first term are determined. With loop function in A10, each time when the value of next term is computed, it will be assigned to b;and the original value of b, i.e., the value of current term, will be assigned to a; at the same time, the current term of the series is reset to a. With loop parameter, the amount of code can be effectively reduced. Result in A10 is the same as that in A3.

Also, subprogram of esProc is employed to make computations:

The subprogram in the latter part of A13 is responsible for computing Fibonacci Sequence of designated number of terms. if statement is used in the subprogram to make judgment, classifying two cases in producing Fibonacci Sequence: if the number of terms is less than or equal to 2, 1 is filled in each term; if it is more than 2, call function with recursion in B14 to get the first n-1 terms, compute the sum of the last two terms and add result to the series. Results of A12 and A3 are the same. 

By comparing the above several computing methods, we find that for loop is simple and easy to understand; loop function boasts the least amount of code; and for subprogram, calling is convenient and reusability of the code is high, so it’s suitable for cases required to make the same repeated computations. In real-world applications, we can make different choices as needed.