当前查询对错误结果求和，因为它对来自此查询的值求和：

select t1.material , t1.sale, t2.sale
from table1 t1 
join table2 t2 on t1.material = t2.material

字符串
在dbfiddle中显示一些步骤，这是最终的查询：

SELECT material, SUM(table1_sale), SUM(table2_Sale)
from (
   select material, sum(sale) as table1_sale, 0 as table2_sale from table1 group by material
   union all
   select material, 0, sum(sale) from table2 group by material
  ) x
group by material
order by material;

型
请参阅：DBFIDDLE

一种可能的方法是在加入之前通过material聚合sale。

With A As
(Select material, Sum(sale) As table1_sale From table1 Group by material),
B As
(Select material, Sum(sale) As table2_sale From table2 Group by material)
Select Coalesce(t1.material,t2.material) As material, 
       Coalesce(t1.table1_sale,0) As table1_sale, 
       Coalesce(t2.table2_sale,0) As table2_sale
From A As t1 Full Join B As t2 On (t1.material=t2.material)
Order by material

字符串
输出量：
| 材料|table1_sale| table2_sale|
| --|--|--|
| M1| 13 | 45 |
| M2| 52 | 0 |
| M3| 40 | 25 |
db<>fiddle

是的，在加入之前。
我是用WITH子句做的，因为我更喜欢它的可读性。

WITH
-- your input, don't use in final query ..
table1(id,sale,material) AS (
          SELECT 1,10,'m1'
UNION ALL SELECT 2,2,'m2'
UNION ALL SELECT 3,3,'m1'
UNION ALL SELECT 4,40,'m3'
UNION ALL SELECT 5,50,'m2'
)
,
table2(id,sale,material) AS (
          SELECT 1,5,'m3'
UNION ALL SELECT 2,15,'m1'
UNION ALL SELECT 3,20,'m1'
UNION ALL SELECT 4,20,'m3'
UNION ALL SELECT 5,10,'m1'
)
-- end of your input, replace following comma with "WITH" 
,
t1grp AS (
  SELECT
    material
  , SUM(sale) AS t1sale
  FROM table1
  GROUP BY material
)
,
t2grp AS (
  SELECT
    material
  , SUM(sale) AS t2sale
  FROM table2
  GROUP BY material
)
SELECT
  t1grp.material
, t1sale
, NVL(t2sale,0)
FROM t1grp LEFT JOIN t2grp USING(material)
ORDER BY 1;
-- out  material | t1sale | NVL 
-- out ----------+--------+-----
-- out  m1       |     13 |  45
-- out  m2       |     52 |   0
-- out  m3       |     40 |  25

字符串