MySQL中的where语句会 destruct 全外连接.

请考虑下表:

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&gt；"Whatwhere"过滤了t1个表中的所有空行.我已经试过了，但不起作用:

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