SQL: full outer join with three tables

Create the tables

create table tab_1 (id integer, txt varchar(30));
create table tab_2 (id integer, txt varchar(30));
create table tab_3 (id integer, txt varchar(30));

Insert some values

insert into tab_2 values (1, 'Only in tab_2'     );

insert into tab_1 values (2, 'In tab_1 and tab_3');
insert into tab_3 values (2, 'In tab_1 and tab_3');

insert into tab_2 values (3, 'In tab_2 and tab_3');
insert into tab_3 values (3, 'In tab_2 and tab_3');

insert into tab_1 values (4, 'Only in tab_1'     );

insert into tab_1 values (5, 'In all tables'     );
insert into tab_2 values (5, 'In all tables'     );
insert into tab_3 values (5, 'In all tables'     );

insert into tab_3 values (6, 'Only in tab_3'     );

insert into tab_1 values (7, 'In tab_1 and tab_2');
insert into tab_2 values (7, 'In tab_1 and tab_2');

(Probably) wrong select statement

select
-- t1.id,
-- t2.id,
-- t3.id,
   --
   t1.txt,
   t2.txt,
   t3.txt
from
   tab_1 t1                       full outer join
   tab_2 t2 on t1.id = t2.id      full outer join
   tab_3 t3 on t1.id = t3.id
;

txt                            txt                            txt                            
------------------------------ ------------------------------ ------------------------------ 
In tab_1 and tab_3                                            In tab_1 and tab_3             
Only in tab_1                                                                                
In all tables                  In all tables                  In all tables                  
In tab_1 and tab_2             In tab_1 and tab_2                                            
                               Only in tab_2                                                 
                               In tab_2 and tab_3                                            
                                                              In tab_2 and tab_3             
                                                              Only in tab_3                  

A (possibly) better approach

select
-- t1.id,
-- t2.id,
-- t3.id,
   --
   t1.txt,
   t2.txt,
   t3.txt
from
   tab_1 t1                                   full outer join
   tab_2 t2 on          t1.id         = t2.id full outer join
   tab_3 t3 on coalesce(t1.id, t2.id) = t3.id
;

txt                            txt                            txt                            
------------------------------ ------------------------------ ------------------------------ 
In tab_1 and tab_3                                            In tab_1 and tab_3             
Only in tab_1                                                                                
In all tables                  In all tables                  In all tables                  
In tab_1 and tab_2             In tab_1 and tab_2                                            
                               Only in tab_2                                                 
                               In tab_2 and tab_3             In tab_2 and tab_3             
                                                              Only in tab_3                  

Drop the tables

drop table tab_1;
drop table tab_2;
drop table tab_3;

See also