MySQL中如何使用三个表进行FULL OUTER JOIN操作？

Question

考虑以下表：

<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>

t1表缺少2022-02-01日期，t2缺少2022-03-01，t3缺少2022-04-01。我想要将这三个表连接起来，产生以下结果：

| 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     |

我知道如何在两个表之间进行full outer join，但是在三个或更多表之间，情况会更复杂。我尝试了像这样的查询，但没有产生我想要的结果：

<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

你走在正确的轨道上，但你必须考虑所有的组合 - 3个表 - 4个联合子句。

-- 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`