It's otnot entirely clear what you are after but I think a FULL
outer join would help:
SELECT
COALESCE(a.Component, b.Component) AS Component
, COALESCE(a.data1, b.data10) AS data1
, COALESCE(a.data2, b.data20) AS data2
, COALESCE(ab.data3, b.data30) AS data3
, COALESCE(ab.data4, b.data40) AS data4
FROM
table1 AS a
FULL JOIN
table2 AS b
ON b.Component = a.Component ;
If Component
is not UNIQUE
on one (or both) of the tables, then you could aggreagte and then join:
WITH a AS
( SELECT
Component
, COUNT(*) AS cnt
, SUM(data1) AS sum_data1
, SUM(data2) AS sum_data2
-- ...
, AVG(data1) AS avg_data1
-- ...
GROUP BY
Component
FROM
table1
)
, b AS
( SELECT
Component
, COUNT(*) AS cnt
, SUM(data3) AS sum_data3
-- ...
, AVG(data3) AS avg_data3
-- ...
GROUP BY
Component
FROM
table2
)
SELECT
COALESCE(a.Component, b.Component) AS Component
, COALESCE(a.sum_data1,0) AS sum_data1
-- ...
, COALESCE(b.sum_data3,0) AS sum_data3
-- ...
FROM
a FULL JOIN b
ON b.Component = a.Component ;