prefix join

pj(leftTable, rightTable, matchingCols, [rightMatchingCols])

leftTable and rightTable are the tables to be joined.

matchingCols a string scalar indicating the matching column.

rightMatchingCols a string scalar indicating the matching column in rightTable . This optional argument must be specified if the matching column has different names in leftTable and rightTable . The joining column name in the result will be the joining column name from the left table.

Prefix join is similar to equi join with the following differences:

1. Prefix join returns the rows in the left table whose joining column value starts with the joining column value in the right table.

2. Prefix join can only have one joining column, and it must be of data type STRING or SYMBOL.

Note: When both the left and right tables are DFS tables, pj only matches data within the corresponding partitions of the DFS tables.

Example 1. Prefix join with the same joining column name.

t1=table(["DT_1","DT2","BC.1","GB7T","AC/8","ACA9","DEF"] as id, 20.5 12.3 26.8 15.2 24.7 56.8 33.6 as price)
t2=table(["DT","BC","GB","AC", "TD"] as id,12 45 78 26 89 as qty);
t1;

t2;

select * from pj(t1,t2,`id);

Example 2. Prefix join with different joining column names.

t1=table(["DT_1","DT2","BC.1","GB7T","AC/8","ACA9","DEF"] as id, 20.5 12.3 26.8 15.2 24.7 56.8 33.6 as price)
t2=table(["DT","BC","GB","AC", "TD"] as prefix,12 45 78 26 89 as qty);
select * from pj(t1,t2,`id,`prefix);