Sum of columns from two tables even if value doesn't match?-CodePudding

The following query that uses an inner join returns the sum where name matches in both tables.

;with cte1 as
(
    select 'a' as 'name', 1 as 'total'
    union
    select 'b', 2
    union
    select 'x', 6
    union
    select 'y', 7
    union
    select 'z', 8
    union
    select 'f', 30
),
cte2 as
(
    select 'a' as 'name', 11 as 'total'
    union
    select 'b', 22
    union
    select 'd', 6
    union
    select 'y', 7
    union
    select 'z', 8
)
select cte1.name, cte1.total   cte2.total as 'total'
from cte1 inner join cte2 on
cte1.name = cte2.name

The result is:

name    total   total   total
a       1       11      12
b       2       22      24
y       7       7       14
z       8       8       16

I need to also display the totals even if there's no match, so the result should look like below ("n/a" is just to display that a value does not exist in the cte):

name    cte1.total  cte2.total  total
a       1           11          12
b       2           22          24
y       7           7           14
z       8           8           16
f       30          n/a         30
x       6           n/a         6
d       n/a         6           6

Values 'x' and 'f' are not in cte2, but they're included in the total. 'd' is not in cte1, but we also see a total.

CodePudding user response：

You may try the following using a