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

灰太狼 2021-09-19 01:52 517阅读 0赞

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

### 1. 内连接查询 ###

指所有查询出的结果都是能够在连接的表中有对应记录的。

#### 以t\_employee(员工表)和t\_dept(部门表)为例 ####

**t\_employee表中的记录如下：**  
dept代表该员工所在的部门  
![在这里插入图片描述][20190423193153492.png]  
**t\_dept表中记录如下：**  
![在这里插入图片描述][20190423193319855.png]

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

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

此时，就要使用内连接查询

关键字（inner join）

**查询sql:**

select 
    e.empName,d.deptName
    from t_employee e
    INNER JOIN t_dept d
    ON e.dept = d.id;

**在这里说一下关联查询sql编写的思路，**

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

**查询的结果如下：**  
![在这里插入图片描述][20190423193529289.png]

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

### 2. 左外连接查询 ###

以左边的表的数据为基准，
    去匹配右边的表的数据，
    如果匹配到就显示，
    匹配不到就显示为null。

例如,查询所有员工姓名以及他所在的部门名称：

在内连接中赵七没有被查出来，因为他没有对应的部门，
    	现在想要把赵七也查出来，就要使用左外连接

**查询sql**

SELECT e.empName,d.deptName
    from t_employee e
    LEFT OUTER JOIN t_dept d 
    on d.id = e.dept;

**理解:**

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

**查询结果：**  
![在这里插入图片描述][20190423194103811.png]

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

### 3. 右外连接是同理的，只是基准表的位置变化了而已 ###

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

SELECT e.empName,d.deptName
    from t_employee e
    RIGHT OUTER JOIN t_dept d 
    on d.id = e.dept;

**理解:**

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

**查询结果：**  
![在这里插入图片描述][20190423194256981.png]

### 4. 全外连接 ###

把两张表的字段都查出来，没有对应的值就显示null，
    但是注意：mysql是没有全外连接的(mysql中没有full outer join关键字)，
    想要达到全外连接的效果，可以使用

union关键字

连接左外连接和右外连接。

**例如：**

select e.empName,d.deptName
         FROM t_employee e 
         left JOIN t_dept d
         ON e.dept = d.id
    UNION
    select e.empName,d.deptName
         FROM t_employee e 
         RIGHT JOIN t_dept d
         ON e.dept = d.id;

**查询结果：**  
![在这里插入图片描述][20190423194903896.png]

如果在oracle中，直接就使用full outer join关键字连接两表就行了

### 5. 自连接查询 ###

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

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

SELECT e.empName,b.empName
         from t_employee e
         LEFT JOIN t_employee b
         ON e.bossId = b.id;

**查询结果：**  
![在这里插入图片描述][20190423195032961.png]  
**在这里，b表是虚拟化出的表，我们可以通过查询了解b表的记录：**

**查询 sql**

SELECT e.empName,b.empName,b.*
         from t_employee e
         LEFT JOIN t_employee b
         ON e.bossId = b.id;

**查询结果：**  
![在这里插入图片描述][20190423195118733.png]  
**后面的四个字段就是虚拟化出的b表的所有记录，但看这四个字段其实就是记录所有是上司的员工的信息**

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

原文：[https://blog.csdn.net/lu0422/article/details/78892497][https_blog.csdn.net_lu0422_article_details_78892497]

[20190423193153492.png]: /images/20210508/8120b2d16d5848948a56ae076818bff6.png
[20190423193319855.png]: /images/20210508/b89d902d618c4dbd98a3aa69a2dae44b.png
[20190423193529289.png]: /images/20210508/b24ddb8ab760453091950637c7ec346a.png
[20190423194103811.png]: /images/20210508/13035ff7900440b0b75f058e0cc3c5b0.png
[20190423194256981.png]: /images/20210508/39bb861735ba48b39a8b82d96012759b.png
[20190423194903896.png]: /images/20210508/9cf0b184d2ce4e05896840e621406978.png
[20190423195032961.png]: /images/20210508/fcba58f015e942c5b77654453c44ac0c.png
[20190423195118733.png]: /images/20210508/9fd8bf81a02341429eb79a9f35e49b0f.png
[https_blog.csdn.net_lu0422_article_details_78892497]: https://blog.csdn.net/lu0422/article/details/78892497