MySQL 表连接查询 详情

前言#

我在写sql查询的时候，用的最多的就是where条件查询，这种查询也叫内连查询inner join，当然还有外连查询outer join，左外连接，右外连接查询，常用在多对多关系中，那他们区别和联系是什么呢？

内连接inner join#

内连接最常用定义：

1. 连接结果仅包含符合连接条件的行组合起来作为结果集，参与连接的两个表都应该符合连接条件使用关键词：INNER JOIN 连接多张表

也就是返回两个表的交集（阴影）部分，如下图所示：

如下sql查询语句

查询所有分配了部门信息的用户信息

也就是部门id在用户表，和部门表都存在行符合条件数据才展示

select u.USERNAME, u.MOBILE, u.EMAIL, d.DEPT_NAME
from t_user u inner join
     t_dept d
on u.DEPT_ID = d.DEPT_ID

内连接还有一种隐式的写法，即不需要显示的指定 INNER JOIN 关键字

等价于

select u.USERNAME, u.MOBILE, u.EMAIL, d.DEPT_NAME
from t_user u,
     t_dept d
where u.DEPT_ID = d.DEPT_ID

一般我们常用直接使用where关键词查询连接条件这样更方便简单

外连接outer join#

左（外）连接 left join#

定义：

1. 左(外)连接，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL，如下图所示：

1. 语法

LEFT JOIN ON
LEFT OUTER JOIN ON

left join 是 left outer join 的简写，它的全称是左外连接，是外连接中的一种。

如下sql查询语句

查询所有员工信息（包含部门信息）

select u.USERNAME, u.MOBILE, u.EMAIL,u.DEPT_ID, d.DEPT_NAME
from t_user u left outer join
     t_dept d
on u.DEPT_ID = d.DEPT_ID

等价于

select u.USERNAME, u.MOBILE, u.EMAIL,u.DEPT_ID, d.DEPT_NAME
from t_user u left  join
     t_dept d
on u.DEPT_ID = d.DEPT_ID

如下图我们可以看到，左外连接 t_user 表里面 USERNAME 为 kenx 的记录，其 DEPT_ID 为 15，但 DEPT_ID 为 15 的记录在 t_dept 表里面是不存在的，此时，我们用的是左外连接，因此，可以查出该条记录，但 t_dept 表里面的字段的值都是 NULL。

右（外）连接 right join#

定义：

1. 右(外)连接，右表的记录将会全部表示出来，而左表只会显示符合搜索条件的记录。左表记录不足的地方均为NULL，如下图所示：

1. 语法

RIGHT JOIN ON
RIGHT OUTER JOIN ON

right join 是 right outer join 的简写，它的全称是右外连接，是外连接中的一种。

如下查询sql 语句

select u.USERNAME, u.MOBILE, u.EMAIL,u.DEPT_ID, d.DEPT_NAME
from t_user u right join
     t_dept d
on u.DEPT_ID = d.DEPT_ID

等价于

select u.USERNAME, u.MOBILE, u.EMAIL,u.DEPT_ID, d.DEPT_NAME
from t_user u right outer join
     t_dept d
on u.DEPT_ID = d.DEPT_ID

如下图 与左外连接刚好相反 查询出了所有部门信息，不符合条件的用户信息字段都为NULL

总结#

1. 内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。

2. 外连接：连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。

3. 左外连接：左边表数据行全部保留，右边表保留符合连接条件的行。

4. 右外连接：右边表数据行全部保留，左边表保留符合连接条件的行。

5. 全外连接：左外连接 union 右外连接，Mysql 中暂不支持。