How to merge two tables based on a composite key in DolphinDB?

I have two factor tables, one containing numerical values and the other containing strings. I want to merge the two tables into a single table based on a composite key and return it to users.

I have tried the following script:

t2 = table(1 2 3 as A, `a`b`c as B, rand(1.0, 3) as C, rand(1.0, 3) as D, now() + 1..3 as F)
t3 = table(1..3 as A,  `a`b`d as B,  take(`TESTE`TESTD, 3) as E, now() + 11..13 as F)

tmp = table(1:0, `A`B`C`D`E`F, [INT, SYMBOL, DOUBLE, DOUBLE,  STRING, TIMESTAMP])

tmp.tableInsert(dict(t2.colNames(), t2.values()))
tmp.tableInsert(dict(t3.colNames(), t3.values()))

t1 = select A, B, firstNot(C,NULL) as C, firstNot(D, NULL) as D, firstNot(E,NULL) as E, max(F) as F from tmp group by A, B

But I’m wondering if there is a simpler method.

You can use fj (full join) in DolphinDB by using the “A” and “B” cloumns as the matching columns:

t2 = table(1 2 3 as A, `a`b`c as B, rand(1.0, 3) as C, rand(1.0, 3) as D, now() + 1..3 as F)
t3 = table(1..3 as A, `a`b`d as B, take(`TESTE`TESTD, 3) as E, now() + 11..13 as F)

t1=select nullFill(A, t3.A) as A, nullFill(B, t3.B) as B, C , D, E, nullFill(F, t3.F) from fj(t2, t3, `A`B)

Output: