Join

The JOIN is one of the most important SQL operations. It is used in scenarios such as obtaining attributes through codes (like using the product codes to get the producing areas and the unit prices) and multiple table alignments (like aligning both the allowance table and attendance table with the employee table)。

Excel uses Lookup functions to associate tables. They are similar to the SQL left join. SQL also has inner join, right join and full join, among which the inner join is implemented through filtering after the left join and the right join is the opposite operation of the left join with joining direction changed. The full join, however, can’t be performed automatically in Excel.

The biggest problem of Lookup functions is their complicated usage. They need to specify the joining column, the joining scope and the referenced columns, with only one referenced column for each look-up, and multiple Lookup statements using the same query condition for referencing multiple columns. Not only is the writing troublesome, but also the method has a poor performance due to repeated operations. In fact as a traversal-style query method, Lookup function are very inefficient in searching associated data.

Based on SQL model, esCalc supports the whole set of join operations including inner join, left join and full join, with multiple columns referenced at once from the associated table by specifying the associated cells in the two worksheet to be joined. This is much simpler than using the Excel method. To join the performance table and the attendance table, for instance, set master cells (i.e. the joining cells) and copy the to-be-referenced cells in the attendance table and paste them on the employee performance table using the JOIN operation.

Here’s the performance table, in which A2 and its homo-cells are set as the master cells where the employee numbers are stored: 

Here’s the attendance table, which contains only the employees who have had absences, and in which A2 and its homo-cells are master cells holding the employee numbers: 

To perform a join operation, select B2 in the attendance table and press Ctrl+C to copy, and then select E2 in the employee performance table and press Ctrl+Alt+J to choose and execute the Left join: 

After that the resulting employee performance table is as follows:

The esCalc join operation also supports multi-level worksheet tables. For example, the employees are stored in groups according to their states, and the attendances are recorded in the same way. The multi-level join will first align tables according to the groups and then find the joining rows in each group. This way error won’t occur even there are employees with same names under different states and the result set will be obtained with detail group data kept neatly and completely.

Here’s the duty table, in which master cells hold state names and employee numbers:

Here’s the employee table, in which master cells also hold state names and employee numbers:

In this employee table, select B3 and C3 at the same time and copy the employee information, and then select C3 in the duty table and perform left join. Here’s the result: