Question with the result of fj (full join) in DolphinDB

Question

Part 1:

test_fj = fj(t_test_821, t_test, `trade_date `strategy_id `secu_code)
select * from test_fj where trade_date=2019.08.27 and strategy_id=5026

Part 2:

ttt1 = select * from t_test_821 where trade_date=2019.08.27 and strategy_id=5026
ttt2 = select * from t_test where trade_date=2019.08.27 and strategy_id=5026
fj(ttt1, ttt2, `trade_date `strategy_id `secu_code)

The scripts above perform full join on two tables. “trade_date”, ”strategy_id” and ”secu_code” are the matching columns in the full join operation. Why does the join result preserve each matching column as separate columns from the left and right tables, instead of merging them into a single common column?

Answer 1

In DolphinDB, full join does not merge matching columns into a single common column. To merge multiple columns into one, you can use the coalesce statement as follows:

t = table(`a`a`b`c`d as sym, 1..5 as val)
t1 = table(`a`b`b`e`e as sym, 1..5 as val1)
re = fj(t, t1, `sym)
select val, val1, coalesce(sym, t1_sym) as sym from re