如何在MySQL中对三张表执行FULL OUTER JOIN？

现有三张结构相同的表t1、t2、t3，每张表都包含日期和对应数值，但各自缺失一个日期的数据：

• t1缺少2022-02-01
• t2缺少2022-03-01
• t3缺少2022-04-01

表结构与初始化数据 #

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

期望结果 #

需要得到包含所有存在的日期，对应表无该日期数据时显示null的结果：

尝试的SQL（未得到期望结果） #

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

正确解法 #

核心思路是先获取所有出现过的日期集合，再将这个日期集合分别与三张表做左连接，保证每个日期都被包含，对应表无数据时显示null：

-- 先获取所有唯一日期
with all_dates as (
    select `date` from t1
    union
    select `date` from t2
    union
    select `date` from t3
)
select 
    t1.`date` as t1_date, t1.`value` as t1_value,
    t2.`date` as t2_date, t2.`value` as t2_value,
    t3.`date` as t3_date, t3.`value` as t3_value
from all_dates
left join t1 on all_dates.`date` = t1.`date`
left join t2 on all_dates.`date` = t2.`date`
left join t3 on all_dates.`date` = t3.`date`
order by all_dates.`date`;

说明

• 使用WITH子句生成包含所有唯一日期的临时表all_dates，union自动去重，确保每个日期只出现一次。
• 以all_dates为基础表，分别左连接t1、t2、t3，匹配条件为日期相等，这样每个日期都会保留，对应表无该日期数据时字段值为null。
• 最后按日期排序，得到和期望一致的结果。

内容的提问来源于stack exchange，提问作者Arman