equi join

Syntax

equi join (ej) 

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

semantically equi join (sej) 

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

Arguments

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.

Details

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

Examples

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;
id value
1 7.8
2 4.6
3 5.1
3 0.1 
t2;
id qty
5 300
3 500
1 800 
select id, value, qty from ej(t1, t2,`id);
id value qty
1 7.8 800
3 5.1 500
3 0.1 500 
select id, value, qty from ej(t2, t1,`id);
id value qty
3 5.1 500
3 0.1 500
1 7.8 800 
select id, value, qty from ej(t2, t1,`id) where id=3;
id value qty
3 5.1 500
3 0.1 500

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;
id value x
1 7.8 4
2 4.6 3
3 5.1 2
3 0.1 1 
t2 = table(5 3 1 as id,  300 500 800 as qty, 44 66 88 as x) ;
t2;
id qty x
5 300 44
3 500 66
1 800 88 
select id, value, qty, x from ej(t1, t2, `id);
id value qty x
1 7.8 800 4
3 5.1 500 2
3 0.1 500 1

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);
id value qty x
1 7.8 800 88
3 5.1 500 66
3 0.1 500 66 
ej(t1, t2, `id);
id value x qty t2_x
1 7.8 4 800 88
3 5.1 2 500 66
3 0.1 1 500 66

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;
x y a
1 1 1
1 2 2
2 2 3
2 3 4
3 3 5
3 4 6 
t2;
x y b
0 1 11
1 2 12
1 3 13
2 3 14
2 4 15
3 5 16 
ej(t1, t2, `x`y);
x y a b
1 2 2 12
2 3 4 14 
t2.rename!(`x`y, `x2`y2);
x2 y2 b
0 1 11
1 2 12
1 3 13
2 3 14
2 4 15
3 5 16 
ej(t1, t2, `x`y, `x2`y2);
x y a b
1 2 2 12
2 3 4 14

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;
x y a c t2_a b
1 2 2 22 5 12

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;
id time x b_time b_x
A 6 6 3 2
A 9 3 6 6
B 12 4 9 9

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)
id1 value qty
3 5.1 500