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