考虑以下表格：

create table `t1` ( `date` date, `value` int ); create table `t2` ( `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);

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

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

解决方案是使用全连接：

select * from `t1` left join `t2` on `t2`.`date` = `t1`.`date` union select * from `t1` right join `t2` on `t2`.`date` = `t1`.`date`;

这样可以得到我想要的结果。但是使用where语句会破坏一切：

select * from `t1` left join `t2` on `t2`.`date` = `t1`.`date` where `t1`.`date` > "2022-01-01" union select * from `t1` right join `t2` on `t2`.`date` = `t1`.`date` where `t1`.`date` > "2022-01-01";

我期望得到这个结果：

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

但我得到了这个结果：

| t1.date | t1.value | t2.date | t2.value | | | | | | | 2022-03-01 | 3 | null | null | | 2022-04-01 | 4 | 2022-04-01 | 4 |

我知道出了什么问题，但找不到解决方法。问题在于t1.date> "whatever"过滤了t1表中的所有空行。我已经尝试过这个方法，但不起作用：

where `t1`.`date` > "2022-01-01" or `t1`.`date` = null

where `t1`.`date` > "2022-01-01" or `t1`.`date` is null

select * from `t1` left join `t2` on `t2`.`date` = `t1`.`date` where `t1`.`date` > "2022-01-01" union select * from `t1` right join `t2` on `t2`.`date` = `t1`.`date` where `t2`.`date` > "2022-01-01";