Joining two tables on multiple columns and subtracting the value field

I have two tables, Table_A and Table_B both having the same number of columns and structure, without having any unique key in any of them. They have one single value field. I would like to subtract the value in Table_B from Table_A, as if I was doing a lookup/match in SQL.

Table_A: | Foo | Bar | Baz | Units | | ——- | ——- | ——— | ———- | | Foob | Barz | Bazr | 6 | | Foor | Barr | Bazz | 5 | | Fooz | Barz | Bazz | 4 |

Table_B: | Foo | Bar | Baz | Units | | ——- | ——- | ——— | ———- | | Foob | Barz | Bazr | 3 | | Foor | Barr | Bazz | 2 | | Fooz | Barz | Bazz | 2 |

Result: | Foo | Bar | Baz | Units | | ——- | ——- | ——— | ———- | | Foob | Barz | Bazr | 2 | | Foor | Barr | Bazz | 4 | | Fooz | Barz | Bazz | 2 |

I would like to arrive at the Result table as seen above, as if I had a unique key that is the combination of Foo-Bar-Baz. The two tables are created in a way that there are no duplicate Foo-Bar-Baz combinations in either A or B. Although there are Foo-Bar-Baz combinations that either exist in only one table or the other.

This might not be the best way to use SQL but it would make my life easier if I could execute it in one script.

I was joining the two tables, such as below, but it gave me duplications:

SELECT A.Foo, A.Bar, A.Baz, A.Units-B.Units
FROM Table_A
FULL JOIN Table_B ON A.Foo=B.Foo AND A.Bar=B.Bar AND A.Baz=B.Baz;

Answer

I’d go for a GROUP BY instead of FULL OUTER JOIN.

UNION ALL the tables, use negative SUM values for TABLE_B. GROUP BY the result.

SELECT Foo, Bar, Baz, SUM(Units)
FROM
(
  SELECT Foo, Bar, Baz, Units
  FROM Table_A
  UNION ALL
  SELECT Foo, Bar, Baz, -Units
  FROM Table_B
) dt
GROUP BY Foo, Bar, Baz