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 |