Full join two tables on a nonidentical column

Assume T1 is

id    val1
1     10
2     20

and T2 is

id    val2
1     100
3     300

I’d like to make

id    val1    val2
1     10      100
2     20      null
3     null    300

The command that I am using is

select T1.id, T1.val1, T2.val2 from T1 full join T2 on T1.id=T2.id

It results in

id    val1    val2
1     10      100
2     20      null
null  null    300

It is missing id=3. What should I use instead of T1.id in the select command to get all the ids and not just those from T1?

Answer

You want a full join. You can emulate it with this method:

select t1.id, t1.val1, t2.val2
from t1 left join
     t2
     on t1.id = t2.id
union all
select t2.id, null, t2.val2
from t2 left join
     t1
     on t1.id = t2.id
where t1.id is null;

If your database supports full join, you would use either:

select coalesce(t1.id, t2.id) as id, t1.val1, t2.val2
from t1 full join
     t2
     on t1.id = t2.id;

or:

select id, t1.val1, t2.val2
from t1 full join
     t2
     using (id)