equal join

Syntax

equal join (ej)

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

semantically equal 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.

Details

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

Examples

Example 1. Equal 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. Equal 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