equi join

• equi join (ej)

  ej(leftTable, rightTable, matchingCols, [rightMatchingCols], [leftFilter], [rightFilter])

  SQL-compatible syntax

  select * 
  from leftTable 
  [inner] join rightTable on t1.matchingCol=t2.rightMatchingCol 
  [where leftFilter and rightFilter]

• semantically equi join (sej)

  sej(leftTable, rightTable, matchingCols, [rightMatchingCols], [leftFilter], [rightFilter])

  SQL-compatible syntax

  select * 
  from leftTable 
  [inner] join rightTable on leftTable.matchingCol=rightTable.rightMatchingCol 
  [where leftFilter and rightFilter] 
  order by matchingCol 

leftTable and rightTable are the tables to be joined.

matchingCols a string scalar/vector indicating matching columns.

rightMatchingCols a string scalar/vector indicating all the matching columns in rightTable . This optional argument must be specified if at least one of the matching columns has different names in leftTable and rightTable . The joining column names in the result will be the joining column names from the left table.

leftFilter and rightFilter are condition expressions used as filter conditions for the columns in the left and right tables. Use "and" or "or" to join multiple conditions.

Note: If parameter leftTable / rightTable is specified as a dimension table or partitioned table, parameters leftFilter and rightFilter must not be specified.

Return only the rows that have equivalent values for the matching columns.

Example 1. Equi join two tables with no common column names except the joining column(s)

t1 = table(1 2 3 3 as id, 7.8 4.6 5.1 0.1 as value)
t2 = table(5 3 1 as id, 300 500 800 as qty);
t1;

t2;

ej(t1, t2,`id);

Or

select * from t1 inner join t2 on t1.id=t2.id 

 ej(t2, t1,`id);

Or

select * from t2 inner join t1 on t2.id=t1.id

ej(t1, t2,`id,, t1.id==3);

Or

select * from t1 inner join t2 on t1.id=t2.id  where t1.id=3

Example 2. Equi join two tables with common variables that are not the joining column(s)

t1 = table(1 2 3 3 as id, 7.8 4.6 5.1 0.1 as value, 4 3 2 1 as x);
t1;

t2 = table(5 3 1 as id,  300 500 800 as qty, 44 66 88 as x) ;
t2;

select id, value, qty, x from ej(t1, t2, `id);

Or

select id, value, qty, x from t1 inner join t2 on t1.id=t2.id

Note that we don't need to specify which table the columns "value" and "qty" come from. The system tries to locate a column in the left table. If it is not in the left table, then the system will locate it in the right table.

select id, value, qty, t2.x from ej(t1, t2, `id);

Or

select id, value, qty, t2.x from t1 inner join t2 on t1.id=t2.id

ej(t1, t2, `id);

Or

select * from t1 inner join t2 on t1.id=t2.id

Note here ej selects x from both t1 and t2, and renames x from t2 as t2_x.

Example 3. Multiple joining columns

t1=table(1 1 2 2 3 3 as x, 1 2 2 3 3 4 as y, 1..6 as a);
t2=table(0 1 1 2 2 3 as x, 1 2 3 3 4 5 as y, 11..16 as b);
t1;

t2;

ej(t1, t2, `x`y);

Or

select * from t1 inner join t2 on t1.x=t2.x and t1.y=t2.y

t2.rename!(`x`y, `x2`y2);

ej(t1, t2, `x`y, `x2`y2);

Or

select * from t1 inner join t2 on t1.x=t2.x2 and t1.y=t2.y2 

Example 4. table aliases in table joins

table1=table(1 1 2 2 3 3 as x, 1 2 2 3 3 4 as y, 1..6 as a, 21..26 as c)
table2=table(0 1 1 2 2 3 as x, 1 2 3 3 4 5 as y, 4..9 as a, 11..16 as b);
select * from ej(table1 as t1, table2 as t2, `x`y) where t2.a<7;

We must use table aliases in a self join:

t = table(`A`A`A`A`B`B`B`B as id, 1 3 6 9 1 9 12 17 as time, 1 2 6 3 5 9 4 0 as x)
select * from ej(t as a, t as b, `id) where a.time=b.time+3;

Or

select * from t as a inner join t as b on a.id=b.id where a.time=b.time+3

Example 5. Specify filter conditions

t1= table(1 2 3 3 as id1, 7.8 4.6 5.1 0.1 as value)
t2 = table(5 3 1 as id2, 300 500 800 as qty);
select * from ej(t1, t2, `id1, `id2, t1.value>1 and t1.value<6, t2.qty>300)

Or

select * from t1 inner join t2 on t1.id1=t2.id2 where t1.value>1 and t1.value<6 and t2.qty>300

This example demonstrates the following two points:

• The join column names in the left and right tables can be different.

• The left and right tables can include filter conditions. In this example, only records that satisfy t1.id1=t2.id2, t1.value>1 and t1.value<6, t2.qty>300 at the same time will appear in the result.

Example 6. Specify only leftFilter

t1 = table(1 2 3 3 as id, 7.8 4.6 5.1 0.1 as value)
t2 = table(1 3 4 as id, 800 500 900 as qty)
select id, value, qty from ej(t1, t2, `id,, t1.value>=5)

Or

select * from t1 inner join t2 on t1.id=t2.id and t1.value>=5

In this example, only a filter condition for the left table is set, so all rows in the right table can participate in the join, but in the left table only records with value>=5 are retained first, and then matching rows are sought in the right table.

Example 7. Specify only rightFilter

t1 = table(1 2 3 3 as id, 7.8 4.6 5.1 0.1 as value)
t2 = table(1 3 4 as id, 800 500 900 as qty)
select id, value, qty from ej(t1, t2, `id,,, t2.qty>=600)

Or

select * from t1 inner join t2 on t1.id=t2.id and t2.qty>=600

In this example, only a filter condition for the right table is set, so all rows in the left table first participate in the join, but in the right table only records with qty>=600 can be matched. Only the left-table rows that successfully join with these right-table records are retained in the result.

Example 8. Use sej, and the result is sorted by the join columns

t1 = table(3 1 2 3 as id, 5.1 7.8 4.6 0.1 as value)
t2 = table(5 3 1 as id, 300 500 800 as qty)
​
ej(t1, t2, `id)

ej returns ordinary equi join results, and the order of the result rows is usually related to the matching order of the input data, with no guarantee of being sorted by the join columns.

sej(t1, t2, `id);

Or

select * from t1 inner join t2 on t1.id=t2.id order by id

sej returns results sorted by the join columns. When subsequent analysis depends on ordered results, sej is more convenient than ej.