MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Table1 (
 id int primary key not null,
 weight decimal(10,2) not null default 0
);
create table Table2 (
 id int primary key not null,
 weight decimal(10,2) not null default 0
);
insert into Table1 (id, weight) values
 (1, 0.10)
,(2, 0.15)
,(3, 0.12)
,(6, 0.21)
;
insert into Table2 (id, weight) values
 (3,  0.09)
,(6,  0.20)
,(8,  0.10)
,(11, 0.13)
;
select 
id12.id as id,
t1.id as id1,
t1.weight as weight1,
t2.id as id2,
t2.weight as weight2
from (select id from Table1 union select id from Table2) id12
left join Table1 t1 on t1.id = id12.id
left join Table2 t2 on t2.id = id12.id
;


id
id1
weight1
id2
weight2


1
1
0.10
null
null

2
2
0.15
null
null

3
3
0.12
3
0.09

6
6
0.21
6
0.20

8
null
null
8
0.10

11
null
null
11
0.13


select 
id12.id as id,
coalesce(t1.weight,0) + coalesce(t2.weight,0) as relevance
from (select id from Table1 union select id from Table2) id12
left join Table1 t1 on t1.id = id12.id
left join Table2 t2 on t2.id = id12.id
order by id12.id;


id
relevance


1
0.10

2
0.15

3
0.21

6
0.41

8
0.10

11
0.13


select id, sum(weight) as relevance
from
(
 select id, weight from Table1
 union all 
 select id, weight from Table2
) q
group by id
order by id;


id
relevance


1
0.10

2
0.15

3
0.21

6
0.41

8
0.10

11
0.13

id	id1	weight1	id2	weight2
1	1	0.10	null	null
2	2	0.15	null	null
3	3	0.12	3	0.09
6	6	0.21	6	0.20
8	null	null	8	0.10
11	null	null	11	0.13