left join/left outer join

Left join

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

Left semi join

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

Arguments

leftTable and rightTable are the tables to be joined.

matchingCols a string scalar/vector indicating the matching column(s).

rightMatchingCols a string scalar/vector indicating the matching column(s) 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.

SQL-compatible left join

select column_name(s)
from leftTable left [outer] join rightTable
on leftTable.matchingCol=rightTable.rightMatchingCol and [filter]

SQL-compatible left semi join

select column_name(s)
from leftTable left semijoin rightTable
on leftTable.matchingCol=rightTable.rightMatchingCol

Arguments

filter is condition expression(s) used as filter condition(s) for the join. Use “and” to join multiple conditions.

Note:

1. You can use and to combine multiple conditions for a table join.

2. It cannot be used with the keyword update.

3. If the left table is not a DFS table, its right table cannot be a DFS table either.

Details

Left join (lj) return all records from the left table and the matched records from the right table. The result is NULL from the right table if there is no match. If there are more than one matched record in the right table, all the matched records in the right table are returned. Left join may return more rows than the left table.

The only difference between left semi join (lsj) and left join (lj) is that for left semi join, if there are more than one matched record in the right table, only the first record is returned. Left semi join returns the same number of rows as the left table.

Examples

Example 1. Left 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 lj(t1, t2, `id);
//equivalent to select id, value, qty from t1 left join t2 on t1.id=t2.id

id	value	qty
1	7.8	800
2	4.6
3	5.1	500
3	0.1	500

If you do not specify which table the columns “value” and “qty” come from, the system will first 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 from lj(t2, t1, `id);
//equivalent to select id, value, qty from t2 left join t1 on t2.id=t1.id

id	value	qty
5		300
3	5.1	500
3	0.1	500
1	7.8	800

$ select id, value, qty from lsj(t2, t1, `id);
//equivalent to select  id, value, qty from t2 left semijoin t1 on t2.id=t1.id

id	value	qty
5		300
3	5.1	500
1	7.8	800

The example above illustrates the difference between left join and left semi join. In left join, all the matched records from the right table for id=3 are returned. In left semi join, only 1 row for id=3 is returned with the first matched row in table t1.

Example 2. Left 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)
$ t2 = table(5 3 1 as id,  300 500 800 as qty, 44 66 88 as x);
$ t1;

id	value	x
1	7.8	4
2	4.6	3
3	5.1	2
3	0.1	1

$ t2;

id	qty	x
5	300	44
3	500	66
1	800	88

$ select id, value, qty, x from lj(t1, t2, `id);
//equivalent to select  id, value, qty, x from t1 left join t2 on t1.id=t2.id

id	value	qty	x
1	7.8	800	4
2	4.6		3
3	5.1	500	2
3	0.1	500	1

$ select id, value, qty, t2.x from lj(t1, t2, `id);
//equivalent to select  id, value, qty, t2.x from t1 left join t2 on t1.id=t2.id

id	value	qty	x
1	7.8	800	88
2	4.6
3	5.1	500	66
3	0.1	500	66

To select variable x from the right table (t2) when the left table (t1) has a variable with the same name, we need to specify t2.x.

$ lj(t1, t2, `id);

id	value	x	qty	t2_x
1	7.8	4	800	88
2	4.6	3
3	5.1	2	500	66
3	0.1	1	500	66

Here left join selects x from both t1 and t2, and renames x from t2 as t2_x.

Example 3. Multiple join 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

$ lj(t1, t2, `x`y);
// x and y are join columns

x	y	a	b
1	1	1
1	2	2	12
2	2	3
2	3	4	14
3	3	5
3	4	6

$ 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

$ lj(t1, t2, `x`y, `x2`y2);
// t1.x, t1.y t2.x2, t2.y2 are join columns

x	y	a	b
1	1	1
1	2	2	12
2	2	3
2	3	4	14
3	3	5
3	4	6

Example 4. 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 lj(t1, t2, `id1, `id2, t1.value>1 and t1.value<6, t2.qty>300)

id1	value	qty
1	7.8
2	4.6
3	5.1	500
3	0.1

$ t1= table(1 2 3 3 6 8 as id, 7.8 4.6 5.1 0.1 0.5 1.2 as value)
$ t2 = table(5 3 1 2 6 8 as id, 300 500 800 400 600 700 as qty);
$ select * from t1 left join t2 on t1.id=t2.id and t2.qty>=550

id	value	qty
1	7.8	800
2	4.6
3	5.1
3	0.1
6	0.5	600
8	1.2	700