How to use three tables to perform FULL OUTER JOIN operation in MySQL?

Question

Consider the following table:

<code>create table `t1` (
  `date` date,
  `value` int
);

create table `t2` (
  `date` date,
  `value` int
);

create table `t3` (
  `date` date,
  `value` int
);

insert into `t1` (`date`, `value`)
values ​​("2022-01-01", 1),
       ("2022-03-01", 3),
       ("2022-04-01", 4);
       
insert into `t2` (`date`, `value`)
values ​​("2022-01-01", 1),
       ("2022-02-01", 2),
       ("2022-04-01", 4);

insert into `t3` (`date`, `value`)
values ​​("2022-01-01", 1),
       ("2022-02-01", 2),
       ("2022-03-01", 3);
</code>

t1The table is missing2022-02-01date,t2is missing2022-03-01< /code>, t3 is missing 2022-04-01. I want to join these three tables to produce the following result:

| t1.date | t1.value | t2.date | t2.value | t3.date | t3.value |
| | | | | | |
| 2022-01-01 | 1 | 2022-01-01 | 1 | 2022-01-01 | 1 |
| null | null | 2022-02-01 | 2 | 2022-02-01 | 2 |
| 2022-03-01 | 3 | null | null | 2022-03-01 | 3 |
| 2022-04-01 | 4 | 2022-04-01 | 4 | null | null |

I know how to do a full outer join between two tables, but between three or more tables it's more complicated. I tried a query like this but it didn't produce the results I wanted:

<code>select *
from `t1`
left join `t2` on `t2`.`date` = `t1`.`date`
left join `t3` on `t3`.`date` = `t2`.`date` or `t3`.`date` = `t1`.`date`

union

select *
from `t1`
right join `t2` on `t2`.`date` = `t1`.`date`
right join `t3` on `t3`.`date` = `t2`.`date` or `t3`.`date` = `t1`.`date`;
</code>

Answer 1

You are on the right track, but you have to consider all combinations - 3 tables - 4 union clauses.

-- t1与t2进行全连接
select *
from `t1`
left join `t2` on `t2`.`date` = `t1`.`date` 
union 
select *
from `t1`
right join `t2` on `t2`.`date` = `t1`.`date` 

-- t1与t3进行全连接
union 
select *
from `t1`
left join `t3` on `t3`.`date` = `t1`.`date` 

union 
select *
from `t1`
right join `t3` on `t3`.`date` = `t1`.`date`