MySQL left join 用法与实例

mysql left join 会取得左表的全部数据，即使右表并无完全匹配的结果
d表：

[yoon]> select * from d;
+----+----------+------+
| id | number | name |
+----+----------+------+
| 1 | 12345 | aa |
| 3 | 123456 | aa |
| 5 | 1234567 | aa |
| 7 | 12345678 | aa |
+----+----------+------+

 

hank表：

[yoon]> select * from hank;
+----+------+------+-----+
| id | name | cc2 | cc3 |
+----+------+------+-----+
| 1 | YOON | NULL | 0 |
| 3 | CEV | NULL | 0 |
| 4 | AAA | NULL | 0 |
| 5 | CCC | NULL | 0 |
+----+------+------+-----+

left join

[yoon]> select d.id,d.number,hank.cc3 from d left join hank on d.id = hank.id;
+----+----------+------+
| id | number | cc3 |
+----+----------+------+
| 1 | 12345 | 0 |
| 3 | 123456 | 0 |
| 5 | 1234567 | 0 |
| 7 | 12345678 | NULL |
+----+----------+------+

Using 相当于 on，如下：
[yoon]> select d.id,d.number,hank.cc3 from d left join hank using(id);
+----+----------+------+
| id | number | cc3 |
+----+----------+------+
| 1 | 12345 | 0 |
| 3 | 123456 | 0 |
| 5 | 1234567 | 0 |
| 7 | 12345678 | NULL |
+----+----------+------+

 

IS NULL：
在上面语句中，右表中没有对应匹配的记录，其所有列都被置为 NULL ，因此要查询这部分数据，可以通过 where is null 来查询

[yoon]> select d.id,d.number,hank.cc3 from d left join hank using(id) where hank.id is null;
+----+----------+------+
| id | number | cc3 |
+----+----------+------+
| 7 | 12345678 | NULL |
+----+----------+------+
或
[yoon]> select d.id,d.number,hank.cc3 from d left join hank on d.id = hank.id where hank.id is null;
+----+----------+------+
| id | number | cc3 |
+----+----------+------+
| 7 | 12345678 | NULL |
+----+----------+------+

 

说明： hank.id is null 查询的是不匹配的值， hank.id is not null 查询的就是匹配到的值，如下：

[yoon]> select d.id,d.number,hank.cc3 from d left join hank on d.id = hank.id where hank.id is not null;
+----+---------+------+
| id | number | cc3 |
+----+---------+------+
| 1 | 12345 | 0 |
| 3 | 123456 | 0 |
| 5 | 1234567 | 0 |
+----+---------+------+