MySQL连接查询 inner join、left join、right join、full join

一直没弄懂各种join的关系，今天偶然在大神的博客看到这张图，重新梳理了一下连接查询的关系。

看到这张图有没有似曾相识的感觉呢？是不是和数学中的集合很相似？不妨回忆一下集合交集并集。
先创建两张表
结构如下：

#A表
mysql> desc A;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> select * from A;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 王二   |
+----+--------+
#-------------------------------------------------------------------
#B表
mysql> desc B;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
| A_id    | int(11)     | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
mysql> select * from B;
+----+---------+------+
| id | address | A_id |
+----+---------+------+
|  1 | 长沙    |    1 |
|  2 | 武汉    |    3 |
|  3 | 深圳    |    4 |
+----+---------+------+

1.左连

mysql> SELECT A.*, B.*
    -> FROM A
    -> LEFT JOIN B ON A.id = B.A_id;
+----+--------+------+---------+------+
| id | name   | id   | address | A_id |
+----+--------+------+---------+------+
|  1 | 张三   |    1 | 长沙    |    1 |
|  3 | 王二   |    2 | 武汉    |    3 |
|  2 | 李四   | NULL | NULL    | NULL |
+----+--------+------+---------+------+

我们可以看到，结果集中，并不是所有的记录都满足A.id = B.A_id条件【name=李四】的记录不满足。这是因为left join的连接方式会保留A中的所有记录。
集合中体现为取A和B的并集中属于A的部分

2.右连

mysql> SELECT A.*, B.*
    -> FROM A
    -> RIGHT JOIN B ON A.id = B.A_id;
+------+--------+----+---------+------+
| id   | name   | id | address | A_id |
+------+--------+----+---------+------+
|    1 | 张三   |  1 | 长沙    |    1 |
|    3 | 王二   |  2 | 武汉    |    3 |
| NULL | NULL   |  3 | 深圳    |    4 |
+------+--------+----+---------+------+

与左连相反，右连right join则保留了B的所有数据
集合中体现为取A和B的并集中属于B的部分

3.内连

mysql> SELECT A.*, b.*
    -> FROM A
    -> INNER JOIN B
    -> ON A.id = B.A_id;
+----+--------+----+---------+------+
| id | name   | id | address | A_id |
+----+--------+----+---------+------+
|  1 | 张三   |  1 | 长沙    |    1 |
|  3 | 王二   |  2 | 武汉    |    3 |
+----+--------+----+---------+------+
#---------------------------------------------------------------
mysql> SELECT A.*, B.*
    -> FROM A, B
    -> WHERE A.id = B.A_id;
+----+--------+----+---------+------+
| id | name   | id | address | A_id |
+----+--------+----+---------+------+
|  1 | 张三   |  1 | 长沙    |    1 |
|  3 | 王二   |  2 | 武汉    |    3 |
+----+--------+----+---------+------+

上述的两条语句查询的结果都是完全符合A.id = B.A_id这个查询条件的记录。
在集合的关系中就像是去集合A和B的交集

4.不知道叫啥了

mysql> SELECT A.*,B.*
    -> FROM A
    -> LEFT JOIN B
    -> ON A.id = B.A_id
    -> WHERE B.id IS NULL;
+----+--------+------+---------+------+
| id | name   | id   | address | A_id |
+----+--------+------+---------+------+
|  2 | 李四   | NULL | NULL    | NULL |
+----+--------+------+---------+------+

查询结果只有一条记录，这条记录A.id在B中没有对应记录
这个关系体现在集合中，就是取A和B中不属于B的部分。

5.不知道叫啥了

mysql> SELECT A.*,B.*
    -> FROM A
    -> RIGHT JOIN B
    -> ON A.id = B.A_id
    -> WHERE A.id IS NULL;
+------+------+----+---------+------+
| id   | name | id | address | A_id |
+------+------+----+---------+------+
| NULL | NULL |  3 | 深圳    |    4 |
+------+------+----+---------+------+

依然只有一条记录，这条记录的A_id字段的值在A表中不存在
这样的关系，体现在集合中就是，去A和B的并集中不属于A的部分

6A并B

因为mysql不支持full join，因此分别进行连接左连和右连，然后采用union去除重复项，得到结果集。具体语句如下：

mysql> SELECT A.*,B.*
    -> FROM A
    -> LEFT JOIN B ON A.id = B.A_id
    -> UNION
    -> SELECT A.*,B.*
    -> FROM A
    -> RIGHT JOIN B ON A.id = B.A_id;
+------+--------+------+---------+------+
| id   | name   | id   | address | A_id |
+------+--------+------+---------+------+
|    1 | 张三   |    1 | 长沙    |    1 |
|    3 | 王二   |    2 | 武汉    |    3 |
|    2 | 李四   | NULL | NULL    | NULL |
| NULL | NULL   |    3 | 深圳    |    4 |
+------+--------+------+---------+------+

7emmmm……取名真难

直接上代码可好？

mysql> SELECT A.*,B.*
    -> FROM A
    -> LEFT JOIN B ON A.id = B.A_id
    -> WHERE A.id IS NULL
    -> OR B.id IS NULL
    -> UNION
    -> SELECT A.*,B.*
    -> FROM A
    -> RIGHT JOIN B ON A.id = B.A_id
    -> WHERE A.id IS NULL
    -> OR B.id IS NULL;
+------+--------+------+---------+------+
| id   | name   | id   | address | A_id |
+------+--------+------+---------+------+
|    2 | 李四   | NULL | NULL    | NULL |
| NULL | NULL   |    3 | 深圳    |    4 |
+------+--------+------+---------+------+

在6的基础上分别给外连和内连增加了筛选条件，选出了AB并集中，不同时属于A和B的部分即AB并集去掉AB的交集啦。

8.总结

1-7每一点都对应了第一张图中的一个集合情况，相信以大家的聪明才智应该很容易看明白的。
个人认为用集合的方式来记忆这些连接查询的关系比较清晰，希望对大家有帮助。
如果有不正确的地方，欢迎大家指正。