How to compare the values of specified columns from two tables in DolphinDB?

Question

I want to compare the values of specified columns from two tables and output the difference. For example, table A and table A1 share the same column “val1“ but have different column values, how to output their differences in DolphinDB?

Answer 1

Do a FULL OUTER JOIN. Return only the rows where either a.val1 doesn't exist, or a1.val1 doesn't exist.

select coalesce(a.val1, a1.val1)
from a
full join a1 on a.val1 = a1.val1
where a.val1 is null or a1.val1 is null

You can also do select a.val1, a1.val1 ..., if you want do know which table each value comes from.