What join to get all instances of a key shared by both tables?

Question

What is the most efficient way to get the result with these two starting tables using T-SQL?

Table1

id	value1
1	A
2	B

Table2

id	value2
1	E
3	F

Desired result:

id	value1	value2
1	A	E
2	B	null
3	null	F

drop table if exists dbo.test1;

create table dbo.test1
(id int,
value1 varchar(50)
);

drop table if exists dbo.test2;

create table dbo.test2
(id int,
value2 varchar(50)
);

insert into dbo.test1
values
(1,'A'),
(2,'B');

insert into dbo.test2
values
(1,'E'),
(3,'F');

this works but seems very inefficient Union all keys first, then join in the values from the other 2 tables

select p.id, a.value1, b.value2
from
(select id from test1
union
select id from test2) p

left join dbo.test1 a
on p.id = a.id

left join dbo.test2 b
on p.id = b.id

Answer 1

You can use FULL JOIN and COALESCE(), for SQL Server.

Example:

 SELECT
  COALESCE(a.id,b.id) AS id
  ,a.value1
  ,b.value2
FROM test1 AS a
FULL JOIN test2 AS b ON a.id = b.id

Fiddle https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d1f62122bff5ca91fa31c25818cc58f7