prefix join

Syntax

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

Arguments

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.

Details

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

Prefix join returns the rows in the left table whose joining column value starts with the joining column value in the right table.
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.

Examples

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;

id	price
DT_1	20.5
DT2	12.3
BC.1	26.8
GB7T	15.2
AC/8	24.7
ACA9	56.8
DEF	33.6

t2;

id	qty
DT	12
BC	45
GB	78
AC	26
TD	89

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

id	price	t2_id	qty
DT_1	20.5	DT	12
DT2	12.3	DT	12
BC.1	26.8	BC	45
GB7T	15.2	GB	78
AC/8	24.7	AC	26
ACA9	56.8	AC	26

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

id	price	prefix	qty
DT_1	20.5	DT	12
DT2	12.3	DT	12
BC.1	26.8	BC	45
GB7T	15.2	GB	78
AC/8	24.7	AC	26
ACA9	56.8	AC	26