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 |