本文共 4036 字，大约阅读时间需要 13 分钟。

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_idunion 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 unionselect * 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 |

转载地址：http://aecmb.baihongyu.com/