MySQL中的关联查询(内连接，外连接，自连接)

 

目录

一、创建表结构SQL和数据

二、内连接查询

三、左外连接查询

四、右外连接查询

五、全外连接

六、自连接查询

 

---

MySql学习专栏

1. MySQL基础架构详解

2. MySQL索引底层数据结构与算法

3. MySQL5.7开启binlog日志，及数据恢复简单示例

4. MySQL日志模块

5. MySQL的MVCC及实现原理

6. MySQL索引优化

7. MySQL——通过EXPLAIN分析SQL的执行计划

8. MySQL执行语句性能优化

9. MySQL中的关联查询(内连接，外连接，自连接)

10. MySQL中复杂的增删改语句

11. 面试必问的 MySQL，你懂了吗？

 

在使用数据库查询语句时，单表的查询有时候不能满足项目的业务需求，在项目开发过程中，有很多需求都是要涉及到多表的连接查询，总结一下mysql中的多表关联查询。

一、创建表结构SQL和数据

CREATE TABLE "employee" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "user_name" varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '姓名',
  "age" int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  "gender" tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '性别 (0-男；1-女；2：未知)',
  "dept_id" int(11) DEFAULT NULL COMMENT '部门ID',
  "boss_id" int(11) DEFAULT NULL COMMENT 'bossId',
  "position" varchar(32) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '职位',
  "create_time" datetime DEFAULT NULL COMMENT '创建时间',
  "status" tinyint(1) DEFAULT NULL COMMENT '状态',
  PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=REDUNDANT COMMENT='员工表';


CREATE TABLE "dept" (
  "id" int(3) NOT NULL AUTO_INCREMENT,
  "dept_name" varchar(20) DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COMMENT='部门表';


INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (1, 'change', 28, 0, 1, NULL, 'java开发', '2021-02-04 13:21:37', 0);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (2, 'Lilei', 27, 0, 2, 2, 'payton开发', '2021-02-03 13:22:16', 1);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (3, 'Bill', 23, 0, 3, 3, 'PHP开发', '2021-01-06 13:22:49', 2);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (4, 'Liping.Zou', 28, 0, 4, 4, 'java开发', '2021-02-04 20:46:02', 2);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (5, 'sundy', 28, 0, 5, 5, 'java开发', '2021-02-04 20:46:02', 0);
INSERT INTO `employee`(`id`, `user_name`, `age`, `gender`, `dept_id`, `boss_id`, `position`, `create_time`, `status`) VALUES (6, 'smile', 30, 0, NULL, 6, 'java开发', '2021-02-04 20:46:02', 1);


INSERT INTO `dept`(`id`, `dept_name`) VALUES (1, '软件开发部门');
INSERT INTO `dept`(`id`, `dept_name`) VALUES (2, '市场部门');
INSERT INTO `dept`(`id`, `dept_name`) VALUES (3, '运营部门');
INSERT INTO `dept`(`id`, `dept_name`) VALUES (4, '人力资源部门');
INSERT INTO `dept`(`id`, `dept_name`) VALUES (6, '总裁部门');

二、内连接查询

关键字：inner join on

特征：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

以employee(员工表)和dept(部门表)为例：

employee表中的记录如下：dept_id 代表该员工所在的部门

select * from employee;

 

dept表中记录如下

select * from dept;

 

可以发现，其中总裁部门里没有员工（这里只是举例，可能与实际不符，但主要在于逻辑关系），而smile没有对应的部门，

现在想要查询出员工姓名以及其对应的部门名称：

关联查询sql编写的思路

1. 先确定所连接的表
2. 再确定所要查询的字段
3. 确定连接条件以及连接方式

SELECT
	e.user_name,
	d.dept_name 
FROM
	employee e
	INNER JOIN dept d ON e.dept_id = d.id;

查询的结果如下：

其中，没有部门的人员和部门没有员工的部门都没有被查询出来，这就是内连接的特点，只查询在连接的表中能够有对应的记录，其中e.dept_id = d.id是连接条件

三、左外连接查询

关键字：left join on / left outer join on

特征：是指以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为Null

查询所有员工姓名以及他所在的部门名称：在内连接中 sundy和smile 没有被查出来，因为他没有对应的部门，现在想要把 sundy和smile 也查出来，就要使用左外连接：

SELECT
	e.user_name,
	d.dept_name 
FROM
	employee e
	LEFT JOIN dept d ON e.dept_id = d.id;

在这里，employee 就是左表，也就是基准表，用基准表的数据去匹配右表的数据，所以左表的记录是全部会查询出来的，如果右表没有记录对应的话就显示Null

查询结果：

关键字是left outer join，等效于 left join，在关联查询中，做外连接查询就是左连接查询，两者是一个概念

四、右外连接查询

关键字：right join on / right outer join on

特征：right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(employee)只会显示符合搜索条件的记录，而右表(dept)的记录将会全部表示出来。左表记录不足的地方均为NULL。

比如：查询所有的部门和对应的员工：

SELECT
	e.user_name,
	d.dept_name 
FROM
	employee e
	RIGHT JOIN dept d ON e.dept_id = d.id;

这里只是把left修改成了right，但是基准表变化了，是以右表的数据去匹配左表，所以左外连接能做到的查询，右外连接也能做到
查询结果：

五、全外连接

关键字：union / union all

特征：顾名思义，把两张表的字段都查出来，没有对应的值就显示Null，但是注意：mysql是没有全外连接的(mysql中没有full outer join关键字)，想要达到全外连接的效果，可以使用union关键字连接左外连接和右外连接。

union语句注意事项

1.     通过union连接的SQL它们分别单独取出的列数必须相同；
2.    不要求合并的表列名称相同时，以第一个sql 表列名为准；
3.    使用union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；
4.    被union 连接的sql 子句，单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序；

union

SELECT
	e.user_name,
	d.dept_name 
FROM
	employee e
	INNER JOIN dept d ON e.dept_id = d.id
UNION 
SELECT
	e.user_name,
	d.dept_name 
FROM
	employee e
	RIGHT JOIN dept d ON e.dept_id = d.id;

 

查询结果：

union all

SELECT
	e.user_name,
	d.dept_name 
FROM
	employee e
	INNER JOIN dept d ON e.dept_id = d.id
UNION ALL
SELECT
	e.user_name,
	d.dept_name 
FROM
	employee e
	RIGHT JOIN dept d ON e.dept_id = d.id;
	

六、自连接查询

关键字：right join on / inner join  on / left join on

特征：自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名

例如：查询员工以及他的上司的名称，由于上司也是员工，所以这里虚拟化出一张上司表   

SELECT
    e.user_name,
    b.user_name as user_name_b
FROM
    employee e
    LEFT JOIN employee b  ON e.boss_id = b.id;

查询结果：

 

自连接查询一般用作表中的某个字段的值是引用另一个字段的值，比如权限表中，父权限也属于权限。

 

参考文档

1. Mysql—— 内连接、左连接、右连接以及全连接查询