MySQL的三种连接查询 内连接 自连接 外连接语法介绍及举例解释

1.内连接

我们将准备两张表的数据：部门表 (dept3) 和员工表 (emp3)。

部门表 (dept3):

deptno	name
D01	人力资源
D02	IT 部门
D03	财务部
D04	销售部

员工表 (emp3):

eid	ename	age	dept_id
E001	张三	30	D01
E002	李四	28	D02
E003	王五	35	D03
E004	赵六	25	D05
E005	钱七	40	D04

隐式内连接

执行查询

select emp.*, dep.*
from dept3 dep, emp3 emp 
where dep.deptno = emp.dept_id;

查询解释

1. 选择所有列：

   • emp.* 表示从员工表中选择所有列。
   • dep.* 表示从部门表中选择所有列。

2. 数据来源：

   • 通过使用逗号（,）将两个表连接起来，使用 WHERE 子句来指定连接条件。

3. 连接条件：

   • 只有当 dept3 表中的 deptno 和 emp3 表中的 dept_id 匹配时，结果中的行才会被返回。

查询结果

eid	ename	age	dept_id	deptno	name
E001	张三	30	D01	D01	人力资源
E002	李四	28	D02	D02	IT 部门
E003	王五	35	D03	D03	财务部
E005	钱七	40	D04	D04	销售部

• 说明：E004（赵六）不会出现在结果中，因为他的 dept_id D05 在 dept3 中并不存在。

---

显式内连接

select d.*, e.* 
from dept3 d 
inner join emp3 e on d.deptno = e.dept_id;

查询解释

1. 选择所有列：

   • d.* 表示从部门表中选择所有列。
   • e.* 表示从员工表中选择所有列。

2. 连接方式：

   • 使用 INNER JOIN 明确指定要连接的表，连接条件在 ON 子句中进行定义。

3. 连接条件：

   • 只有当 dept3 表中的 deptno 和 emp3 表中的 dept_id 匹配时，结果中的行才会被返回。
   • 逻辑上和第一个查询是一样的，但是使用了更清晰的显式连接语法。

查询结果

deptno	name	eid	ename	age	dept_id
D01	人力资源	E001	张三	30	D01
D02	IT 部门	E002	李四	28	D02
D03	财务部	E003	王五	35	D03
D04	销售部	E005	钱七	40	D04

• 说明：和第一个查询结果相同，E004（赵六）仍然不会出现在结果中。

总结

• 结果一致性：
  两个查询的结果是相同的，因为它们都实现了内连接，返回的是在两个表中都有的匹配行。

• 可读性：
  第二个查询使用 INNER JOIN 语法，更加明确和易于理解，特别是对于复杂的查询来说，更加符合现代 SQL 的写法。

• 数据完整性：
  两个查询都有效地过滤了没有匹配的员工数据（例如 E004）。

2.外连接

好的，我们将详细解释这两个 SQL 查询，并提供相应的示例数据和查询结果。我们将考虑不同的情况，比如部门存在但没有员工、员工存在但没有对应部门等。

部门表 (dept3):

deptno	name
D01	人力资源
D02	IT 部门
D03	财务部
D04	销售部

员工表 (emp3):

eid	ename	age	dept_id
E001	张三	30	D01
E002	李四	28	D02
E003	王五	35	D03
E004	赵六	25	D05
E005	钱七	40	D04

如何区分左右连接

在数据库中，左连接（LEFT JOIN）和右连接（RIGHT JOIN）是两种常见的表连接方式。通过这两种连接方式可以从两个表中获取数据。在这两种连接中，左表和右表的区分是基于连接的顺序。

左连接（LEFT JOIN）：

语法示例：SELECT * FROM 表A LEFT JOIN 表B ON 表A.列名 = 表B.列名
在左连接中，表A被称为左表，表B被称为右表。左连接会返回左表（表A）中的所有行，即使在右表（表B）中没有匹配的行。在没有匹配时，右表的列会返回NULL。

右连接（RIGHT JOIN）：

语法示例：SELECT * FROM 表A RIGHT JOIN 表B ON 表A.列名 = 表B.列名
在右连接中，表B被称为右表，表A被称为左表。右连接会返回右表（表B）中的所有行，即使在左表（表A）中没有匹配的行。在没有匹配时，左表的列会返回NULL。
总结：左连接的“左”指的是在JOIN语句中左侧的表，右连接的“右”指的是在JOIN语句中右侧的表。通过这种方式可以区分哪个是左表，哪个是右表。

左连接

select d.*, e.*
from dept3 d
         left join emp3 e on d.deptno = e.dept_id;

查询解释

1. 选择所有列：

   • d.* 表示从部门表中选择所有列。
   • e.* 表示从员工表中选择所有列。

2. 左连接 (LEFT JOIN)：

   • 左连接会返回左表（dept3）中的所有行，即使在右表（emp3）中没有匹配的行。
   • 如果右表中没有匹配的行，则对应的结果列将显示为 NULL。

查询结果

deptno	name	eid	ename	age	dept_id
D01	人力资源	E001	张三	30	D01
D02	IT 部门	E002	李四	28	D02
D03	财务部	E003	王五	35	D03
D04	销售部	E005	钱七	40	D04
D03	财务部	NULL	NULL	NULL	NULL

• 注意：
  • 在结果中，D05 对应的员工 E004 不存在于 dept3，因此 E004 相关的列值显示为 NULL。
  • 所有部门都会被包括，即使没有员工与之对应。

---

右连接

select d.*, e.*
from dept3 d
         right join emp3 e on d.deptno = e.dept_id;

查询解释

1. 选择所有列：

   • d.* 表示从部门表中选择所有列。
   • e.* 表示从员工表中选择所有列。

2. 右连接 (RIGHT JOIN)：

   • 右连接会返回右表（emp3）中的所有行，即使在左表（dept3）中没有匹配的行。
   • 如果左表中没有匹配的行，则对应的结果列将显示为 NULL。

查询结果

deptno	name	eid	ename	age	dept_id
D01	人力资源	E001	张三	30	D01
D02	IT 部门	E002	李四	28	D02
D03	财务部	E003	王五	35	D03
NULL	NULL	E004	赵六	25	D05
D04	销售部	E005	钱七	40	D04

• 注意：
  • 在结果中，E004 的 dept_id 为 D05，而 D05 在 dept3 中并不存在，因此 deptno 和 name 列显示为 NULL。
  • 所有员工都会被包括，即使没有对应的部门。

总结

• 左连接 (LEFT JOIN)：

  • 返回所有部门，即使没有员工与之对应。如果部门没有对应员工，员工相关的列将是 NULL。

• 右连接 (RIGHT JOIN)：

  • 返回所有员工，即使没有对应的部门。如果员工的部门 ID 在部门表中不存在，部门相关的列将是 NULL。

• 例外情况：

  • 在这两个查询中，E004（赵六）和 D05（不存在的部门）展示了如何处理没有匹配的情况。左连接会显示所有部门，而右连接会显示所有员工。

自连接（Self Join）是指在同一张表中进行连接操作。它可以用于查找表中的相关数据。自连接通常使用别名来区分表的两个实例，便于理解。

3.自连接示例 SQL

假设我们有一个员工表 emp3，其中有一个 manager_id 列，用于表示员工的直接上级（经理）的员工编号。

示例数据

我们可以插入以下数据到 emp3 表中：

eid	ename	age	dept_id	manager_id
E001	张三	30	D01	NULL
E002	李四	28	D02	E001
E003	王五	35	D03	E001
E004	赵六	25	D01	E002
E005	钱七	40	D02	E002
E006	孙八	29	D03	E003

自连接查询

我们想要查询每个员工及其直接上级的信息，可以使用以下自连接查询：

select e1.eid as employee_id,
       e1.ename as employee_name,
       e1.age as employee_age,
       e1.dept_id as department_id,
       e2.eid as manager_id,
       e2.ename as manager_name
from emp3 e1
left join emp3 e2 on e1.manager_id = e2.eid;

查询解释

1. 选择列：

   • e1.eid、e1.ename、e1.age、e1.dept_id：从员工表的第一个实例（员工）中选择列。
   • e2.eid、e2.ename：从员工表的第二个实例（经理）中选择列。

2. 左连接：

   • left join 表示返回所有员工（左表），即使某些员工没有经理（manager_id 为 NULL）。

3. 连接条件：

   • on e1.manager_id = e2.eid：连接条件是员工的 manager_id 必须匹配经理的 eid。

查询结果

执行上述查询后，结果将如下：

employee_id	employee_name	employee_age	department_id	manager_id	manager_name
E001	张三	30	D01	NULL	NULL
E002	李四	28	D02	E001	张三
E003	王五	35	D03	E001	张三
E004	赵六	25	D01	E002	李四
E005	钱七	40	D02	E002	李四
E006	孙八	29	D03	E003	王五

结果说明

• 张三（E001）：

  • 作为根节点，没有上级，所以 manager_id 和 manager_name 均为 NULL。

• 李四（E002） 和 王五（E003）：

  • 他们的 manager_id 是 E001（张三），因此 manager_name 显示为张三。

• 赵六（E004） 和 钱七（E005）：

  • 他们的 manager_id 是 E002（李四），因此 manager_name 显示为李四。

• 孙八（E006）：

  • 他的 manager_id 是 E003（王五），因此 manager_name 显示为王五。

其他情况考虑

• 在员工表中，如果某个员工的 manager_id 指向了一个不存在的 eid，那么 manager_id 和 manager_name 将会显示为 NULL。
• 如果某个员工没有上级（例如根节点），那么 manager_id 和 manager_name 也会显示为 NULL。

通过这种自连接查询，我们能够方便地获取层级结构中员工与其上级之间的关系。