Left Join/IS NULL如何消除一个表中存在而另一表中没有的记录? [英] How does Left Join / IS NULL eliminate records which are there in one table and not in the other?
问题描述
我很难理解LEFT JOIN
/IS NULL
为什么要消除在一个表中而不是另一个表中的记录.
这是一个例子
I am having a tough time to understand why does LEFT JOIN
/ IS NULL
eliminate records which are there in one table and not in the other.
Here is an example
SELECT l.id, l.value
FROM t_left l
LEFT JOIN t_right r
ON r.value = l.value
WHERE r.value IS NULL
为什么r.value = NULL
应该消除记录?我不理解 .我知道我缺少一些非常基本的东西,但目前我还无法弄清楚那个基本的东西.如果有人向我详细解释,我将不胜感激.
Why should r.value = NULL
eliminate records ? I am not understanding . I know I am missing something very basic but at present I cant figure out even that basic one. I would appreciate if someone explains it to me in detail .
我想要一个非常基本的解释.
I want a very basic explanation.
推荐答案
可以通过以下内容对此进行解释
This could be explained with the following
mysql> select * from table1 ;
+------+------+
| id | val |
+------+------+
| 1 | 10 |
| 2 | 30 |
| 3 | 40 |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from table2 ;
+------+------+
| id | t1id |
+------+------+
| 1 | 1 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)
此处table1.id <-> table2.t1id
现在,当我们使用连接键执行left join
时,如果左边的表是table1,那么它将从table1获取所有数据,并且在table2的不匹配记录中将被设置为null
Now when we do a left join
with the joining key and if the left table is table1 then it will get all the data from table1 and in non-matching record on table2 will be set to null
mysql> select t1.* , t2.t1id from table1 t1
left join table2 t2 on t2.t1id = t1.id ;
+------+------+------+
| id | val | t1id |
+------+------+------+
| 1 | 10 | 1 |
| 2 | 30 | 2 |
| 3 | 40 | NULL |
+------+------+------+
3 rows in set (0.00 sec)
看到table1.id = 3在table2中没有值,因此将其设置为null 当您应用where条件时,它将做进一步的过滤
See that table1.id = 3 does not have a value in table2 so its set as null When you apply the where condition it will do further filtering
mysql> select t1.* , t2.t1id from table1 t1
left join table2 t2 on t2.t1id = t1.id where t2.t1id is null;
+------+------+------+
| id | val | t1id |
+------+------+------+
| 3 | 40 | NULL |
+------+------+------+
1 row in set (0.00 sec)
这篇关于Left Join/IS NULL如何消除一个表中存在而另一表中没有的记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!