一个简单例子例子说明Mysql的七种join连接

先放一张文氏图震贴

---

现有数据如下

mysql> select * from user;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  4 | zhaoliu  |
+----+----------+

mysql> select * from user_info;
+----+------+
| id | age  |
+----+------+
|  1 |   20 |
|  2 |   21 |
|  3 |   23 |
+----+------+

1. 首先谈一种笛卡尔积，即没有连接条件

mysql> select * from user, user_info;
+----+----------+----+------+
| id | name     | id | age  |
+----+----------+----+------+
|  1 | zhangsan |  1 |   20 |
|  2 | lisi     |  1 |   20 |
|  4 | zhaoliu  |  1 |   20 |
|  1 | zhangsan |  2 |   21 |
|  2 | lisi     |  2 |   21 |
|  4 | zhaoliu  |  2 |   21 |
|  1 | zhangsan |  3 |   23 |
|  2 | lisi     |  3 |   23 |
|  4 | zhaoliu  |  3 |   23 |
+----+----------+----+------+
# 可以看出两张表每一行都各自与另一张表的每一行组合

2. 内连接，即取交集，可以写inner join 或 直接写join。即默认的Join就是inner join

mysql> select * from user join user_info on user.id = user_info.id;
+----+----------+----+------+
| id | name     | id | age  |
+----+----------+----+------+
|  1 | zhangsan |  1 |   20 |
|  2 | lisi     |  2 |   21 |
+----+----------+----+------+

3. 左外连接，这应该是关系型数据库用的最多的情况，即外键一对多，对左表数据进行扩充

mysql> select * from user left join user_info on user.id = user_info.id;
+----+----------+------+------+
| id | name     | id   | age  |
+----+----------+------+------+
|  1 | zhangsan |    1 |   20 |
|  2 | lisi     |    2 |   21 |
|  4 | zhaoliu  | NULL | NULL |
+----+----------+------+------+
# 可以看出，当赵六没有年龄，就被置空了。

4. 左内连接，去掉交集的部分，在左外连接的情况下，不要右表的任何数据

select * from user left join user_info on user.id = user_info.id where user_info.id is null;
+----+---------+------+------+
| id | name    | id   | age  |
+----+---------+------+------+
|  4 | zhaoliu | NULL | NULL |
+----+---------+------+------+
# 可以看出，我要的就是右表为空的数据

5. 右外连接，以右表为标准，即扩充右表

mysql> select * from user right join user_info on user.id = user_info.id;
+------+----------+----+------+
| id   | name     | id | age  |
+------+----------+----+------+
|    1 | zhangsan |  1 |   20 |
|    2 | lisi     |  2 |   21 |
| NULL | NULL     |  3 |   23 |
+------+----------+----+------+
# 结合左外就很容易理解

6. 右内连接，同理就是不要左表的数据

mysql> select * from user right join user_info on user.id = user_info.id where user.id is null;
+------+------+----+------+
| id   | name | id | age  |
+------+------+----+------+
| NULL | NULL |  3 |   23 |
+------+------+----+------+

7. 全外连接，full outer join，即选择所有数据，但要注意交集的部分去重

select * from user left join user_info on user.id = user_info.id
    -> union
    -> select * from user right join user_info on user.id = user_info.id;
+------+----------+------+------+
| id   | name     | id   | age  |
+------+----------+------+------+
|    1 | zhangsan |    1 |   20 |
|    2 | lisi     |    2 |   21 |
|    4 | zhaoliu  | NULL | NULL |
| NULL | NULL     |    3 |   23 |
+------+----------+------+------+
# 可以看出的是使用了union进行了去重

8. 全内连接(名字不准确…)， 即只要差集的部分

select * from user left join user_info on user.id = user_info.id where user_info.id is null
    -> union
    -> select * from user right join user_info on user.id = user_info.id where user.id is null;
+------+---------+------+------+
| id   | name    | id   | age  |
+------+---------+------+------+
|    4 | zhaoliu | NULL | NULL |
| NULL | NULL    |    3 |   23 |
+------+---------+------+------+