left join/left outer join

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

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

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.

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

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

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.

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.

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;

t2;

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

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

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

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;

t2;

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

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

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);

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;

t2;

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

t2.rename!(`x`y, `x2`y2);

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

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)

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