sql - How to use FULL JOIN and compare variance in Ms.Access? -

table_actual +----------+--------+ | product  | actual | +----------+--------+ | aaa      |   100  | | bbb      |   200  | +----------+--------+  table_plan +----------+--------+ | product  |  plan  | +----------+--------+ | aaa      |   150  | | ccc      |   250  | +----------+--------+ 

i need following result:

+----------+--------+--------+----------+ | product  | actual |  plan  | variance | +----------+--------+--------+----------+ | aaa      |   100  |   150  |      50  | | bbb      |   200  |     0  |    -200  | | ccc      |     0  |   250  |     250  | +----------+--------+--------+----------+ 

my query below:

select table_actual.product product, actual, plan, plan - actual variance     table_actual         left join table_plan on table_actual.product = table_plan.product union select table_plan.product product, actual, plan, plan - actual variance     table_actual         right join table_plan on table_actual.product = table_plan.product; 

result: variance value of product aaa , bbb null value.

any assistance great. thanks. tom

you need use function isnull()...

select table_actual.product product, isnull(actual,0), isnull(plan1,0),isnull(plan1,0)- isnull(actual,0)  variance     table_actual         left join table_plan on table_actual.product = table_plan.product union select table_plan.product product, isnull(actual,0), isnull(plan1,0), isnull(plan1,0)- isnull(actual,0) variance     table_actual         right join table_plan on table_actual.product = table_plan.product; 

fiddle