How to merge duplicate join columns after full join in DolphinDB?-CodePudding

I have the following 2 tables: table 1 and table 2.

table 1:

id value
-- -----
1  7.8  
2  4.6  
3  5.1  
3  0.1

table 2:

id qty
-- ---
5  300
3  500
1  800

For a full join of the tables on column id, it returns 2 join columns “id” and “t2_id”. Is any efficient way to merge these columns?

id value t2_id qty
-- ----- ----- ---
1  7.8   1     800
2  4.6            
3  5.1   3     500
3  0.1   3     500
         5     300

CodePudding user response：

You may use function nullFill in a SQL statement to replace all NULLs in column “id” with the corresponding value in column “t2_id”.

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

select nullFill(id,t2.id) as id,value,qty from fj(t1, t2, `id)

output

id value qty
-- ----- ---
1  7.8   800
2  4.6      
3  5.1   500
3  0.1   500
5        300