mysql 的那些常见连接方式

Join介绍

Join是SQL语句中非常重要的一个运算操作，常见的Join操作如下：

mysql 官方只提供了内连接，左外连接，右外连接三种方式。通过一定的方法也可以实现其它的连接。

数据准备

a.创建两张表

create table  `person` (
  `id` int(11),
  `name` varchar(255),
  `city_id` int(11) 
) ;

create table `city` (
  `city_id` int(11) ,
  `city_name` varchar(255)
) ;

b.插入数据

# 向person表中插入数据
insert into person values (1, 'name1', 1);
insert into person values (2, 'name2', 2);
insert into person values (3, 'name3', 3);
insert into person values (4, 'name4', 5);

#向city表中插入数据
insert into city values (1, 'city1');
insert into city values (2, 'city2');
insert into city values (3, 'city3');
insert into city values (4, 'city4');

1.左外连接

左外连接返回左表的所有行，如果右表中没有匹配行，则返回NULL。

select * from city left join person on city.city_id = person.city_id;

运行结果：

+---------+-----------+------+-------+---------+
| city_id | city_name | id   | name  | city_id |
+---------+-----------+------+-------+---------+
|       1 | city1     |    1 | name1 |       1 |
|       2 | city2     |    2 | name2 |       2 |
|       3 | city3     |    3 | name3 |       3 |
|       4 | city4     | NULL | NULL  |    NULL |
+---------+-----------+------+-------+---------+

2.内连接

内连接返回的是两张表共有的数据。

select * from person inner join city on person.city_id = city.city_id

运行结果：

+------+-------+---------+---------+-----------+
| id   | name  | city_id | city_id | city_name |
+------+-------+---------+---------+-----------+
|    1 | name1 |       1 |       1 | city1     |
|    2 | name2 |       2 |       2 | city2     |
|    3 | name3 |       3 |       3 | city3     |
+------+-------+---------+---------+-----------+

3.右外连接

右外连接返回右表的所有行，如果左表中没有匹配行，则返回NULL。

select * from city right join person on person.city_id = city.city_id;

运行结果：

+---------+-----------+------+-------+---------+
| city_id | city_name | id   | name  | city_id |
+---------+-----------+------+-------+---------+
|       1 | city1     |    1 | name1 |       1 |
|       2 | city2     |    2 | name2 |       2 |
|       3 | city3     |    3 | name3 |       3 |
|    NULL | NULL      |    4 | name4 |       5 |
+---------+-----------+------+-------+---------+

4.左连接

左连接是得到A表中去除B表内容的剩下的部分，也就是A表独有的一部分。可以看做是在左外连接的结果中将双方共有的部分去掉得到的。

select * from city  left join person  on city.city_id = person.city_id where person.city_id is  null;

运行结果：

+---------+-----------+------+------+---------+
| city_id | city_name | id   | name | city_id |
+---------+-----------+------+------+---------+
|       4 | city4     | NULL | NULL |    NULL |
+---------+-----------+------+------+---------+

5.右连接

右连接是得到B表中去除A表内容的剩下的部分，也就是B表独有的一部分。可以看做是在右外连接的结果中将双方共有的部分去掉得到的。

select * from city  right join person  on city.city_id = person.city_id where city.city_id is null;

运行结果：

+---------+-----------+------+-------+---------+
| city_id | city_name | id   | name  | city_id |
+---------+-----------+------+-------+---------+
|    NULL | NULL      |    4 | name4 |       5 |
+---------+-----------+------+-------+---------+

6.全连接

全连接返回AB两表全部的数据，mysql没有提供full join关键字，不过可以使用使用union来实现，全连接等于左外连接与右外连接的并集。

select * from city left join person on person.city_id = city.city_id
union 
select * from city right join person on person.city_id = city.city_id;

运行结果：

+---------+-----------+------+-------+---------+
| city_id | city_name | id   | name  | city_id |
+---------+-----------+------+-------+---------+
|       1 | city1     |    1 | name1 |       1 |
|       2 | city2     |    2 | name2 |       2 |
|       3 | city3     |    3 | name3 |       3 |
|       4 | city4     | NULL | NULL  |    NULL |
|    NULL | NULL      |    4 | name4 |       5 |
+---------+-----------+------+-------+---------+

7.差集

差集就是两张表都没有同时出现的数据集，其实也就是左连接与右连接的并集。

select * from city  left join person  on city.city_id = person.city_id where person.city_id is  null 
union
select * from city  right join person  on city.city_id = person.city_id where city.city_id is null;

运行结果：

+---------+-----------+------+-------+---------+
| city_id | city_name | id   | name  | city_id |
+---------+-----------+------+-------+---------+
|       4 | city4     | NULL | NULL  |    NULL |
|    NULL | NULL      |    4 | name4 |       5 |