英文:
where statement breaks full join in MySQL
问题
以下是您提供的翻译内容：
考虑以下表格：
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
英文:
Consider the following tables:
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);
The t1 table is missing 2022-02-01 date and t2 is missing 2022-03-01. I want to join these two tables so that it produces the following result:
| 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 |
The solution is a full join:
select *
from `t1`
left join `t2` on `t2`.`date` = `t1`.`date`
union
select *
from `t1`
right join `t2` on `t2`.`date` = `t1`.`date`;
Which produces exactly the result I want. But a where statement breaks everything:
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";
I expected this result:
| 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 |
But I got this:
| t1.date | t1.value | t2.date | t2.value |
| | | | |
| 2022-03-01 | 3 | null | null |
| 2022-04-01 | 4 | 2022-04-01 | 4 |
I think I know what's going on but I can't find a way around it. The problem is that t1.date > "whatever" filters all empty rows in the t1 table. I've already tried this but it doesn't work:
where `t1`.`date` > "2022-01-01" or `t1`.`date` = null
答案1
得分: 2
在右连接查询中，似乎应该使用 t2.date > "2022-01-01"。
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";
在 https://dbfiddle.uk/reo8UanD 上查看演示。
英文:
Seems like you should use t2.date > "2022-01-01" in the right join query.
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";
See demo at https://dbfiddle.uk/reo8UanD
答案2
得分: 1
我已经尝试过这个，但它不起作用：
应该使用：
其中 t1.date > "2022-01-01" 或 t1.date 为 null
"NULL = NULL" 的结果为 false，因为 NULL 没有值。因此，它不能与任何其他值相同（甚至是另一个 NULL）。正确的方式是使用 is null。
英文:
> I've already tried this but it doesn't work:
>
> where t1.date > "2022-01-01" or t1.date = null
You should use
where `t1`.`date` > "2022-01-01" or `t1`.`date` is null
"NULL = NULL" results false as the NULL does not have value. Therefore it cannot be the same as any other value (even another NULL). The correct way is to use is null
答案3
得分: 1
在MySQL中，要检查一个值是否为空，不要使用 value = null，而要使用 value IS NULL 或 value IS NOT NULL，如果你想检查该值何时被赋值。
因此，你会得到这样的结果：
WHERE `t1`.`date` > "2022-01-01" OR `t1`.`date` IS NULL
在MySQL中，将指令写成大写是正确的。
英文:
In MySQL to check if a value is null your not gonna use value = null but value IS NULL or value IS NOT NULL if you want to check when the value is assigned.
As the result, you gonna have that:
WHERE `t1`.`date` > "2022-01-01" OR `t1`.`date` IS NULL
In MySQL is proper to write the instructions in uppercase.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论