MySQL 多表查询

1. 多表查询的类型

在 MySQL 中，常见的多表查询方式有以下几种：

• 内连接 (Inner Join)：只返回两个表中满足连接条件的行。
• 外连接 (Outer Join)：包括左外连接 (Left Join) 和右外连接 (Right Join)，可以返回一个表中所有记录，即使另一个表中没有匹配的记录。
• 自连接 (Self Join)：在同一个表内进行连接，用于比较表中不同行之间的关系。
• 全外连接 (Full Outer Join)：MySQL 不直接支持，但可以通过 union 来实现。
• 子查询 (Subquery)：在一个查询中嵌套另一个查询，通常用于复杂条件筛选。

2. 内连接 (Inner Join)

内连接是多表查询中最常用的方式之一，返回两个表中满足连接条件的行。可以使用显式和隐式两种写法。

-- 隐式内连接
select 字段列表 
from 表1, 表2 
where 表1.字段 = 表2.字段;

-- 显式内连接
select 字段列表 
from 表1 
inner join 表2 on 表1.字段 = 表2.字段;

示例：

select employees.name as 员工姓名, departments.name as 部门名称 
from employees 
inner join departments on employees.dept_id = departments.id;

输出结果：

employees.name	departments.name
张三	IT 部门
李四	IT 部门
王五	HR 部门

解释：

• 数据来源表：
  • employees：员工表，包含员工的姓名、部门 ID 等信息。
  • departments：部门表，包含部门的 ID 和名称。
• 连接条件：employees.dept_id = departments.id，即根据员工的部门 ID 连接两个表。
• 结果说明：内连接只返回两个表中都存在匹配关系的记录。因此，只有在 employees 表中的 dept_id 与 departments 表中的 id 匹配的记录才会出现在结果中。

3. 左外连接 (Left Join)

左外连接返回左表中的所有记录以及右表中与之匹配的记录。如果右表中没有匹配的记录，结果中会显示 NULL。

select 字段列表 
from 表1 
left [outer] join 表2 on 表1.字段 = 表2.字段;

示例：

select employees.name as 员工姓名, departments.name as 部门名称 
from employees 
left join departments on employees.dept_id = departments.id;

输出结果：

employees.name	departments.name
张三	IT 部门
李四	IT 部门
王五	HR 部门
赵六	NULL

解释：

• 数据来源表：
  • employees：员工表，包含员工的姓名、部门 ID 等信息。
  • departments：部门表，包含部门的 ID 和名称。
• 连接条件：employees.dept_id = departments.id，即根据员工的部门 ID 连接两个表。
• 结果说明：左外连接会返回左表（employees）的所有记录，即使右表（departments）中没有匹配的记录。在这种情况下，部门名称将显示为 NULL，如 赵六 所在的部门。

4. 右外连接 (Right Join)

右外连接与左外连接相似，区别在于它返回右表中的所有记录以及左表中与之匹配的记录。如果左表中没有匹配的记录，结果中会显示 NULL。

select 字段列表 
from 表1 
right [outer] join 表2 on 表1.字段 = 表2.字段;

示例：

select employees.name as 员工姓名, departments.name as 部门名称 
from employees 
right join departments on employees.dept_id = departments.id;

输出结果：

employees.name	departments.name
张三	IT 部门
李四	IT 部门
王五	HR 部门
NULL	财务部

解释：

• 数据来源表：
  • employees：员工表，包含员工的姓名、部门 ID 等信息。
  • departments：部门表，包含部门的 ID 和名称。
• 连接条件：employees.dept_id = departments.id，即根据员工的部门 ID 连接两个表。
• 结果说明：右外连接会返回右表（departments）的所有记录，即使左表（employees）中没有匹配的记录。在这种情况下，员工姓名将显示为 NULL，如 财务部 中没有员工的记录。

5. 全外连接 (Full Outer Join)

MySQL 不直接支持全外连接，但可以通过 union 来实现，将左外连接和右外连接的结果合并起来。

select 字段列表 
from 表1 
left join 表2 on 表1.字段 = 表2.字段
union 
select 字段列表 
from 表1 
right join 表2 on 表1.字段 = 表2.字段;

示例：

select employees.name as 员工姓名, departments.name as 部门名称 
from employees 
left join departments on employees.dept_id = departments.id
union
select employees.name as 员工姓名, departments.name as 部门名称 
from employees 
right join departments on employees.dept_id = departments.id;

输出结果：

employees.name	departments.name
张三	IT 部门
李四	IT 部门
王五	HR 部门
赵六	NULL
NULL	财务部

解释：

• 数据来源表：
  • employees：员工表，包含员工的姓名、部门 ID 等信息。
  • departments：部门表，包含部门的 ID 和名称。
• 连接条件：employees.dept_id = departments.id，即根据员工的部门 ID 连接两个表。
• 结果说明：全外连接的结果包含所有匹配和不匹配的记录，包括那些仅在一个表中存在的记录。通过 union 合并了左外连接和右外连接的结果，确保每个部门和员工的记录都包含在查询结果中。

6. 自连接 (Self Join)

自连接用于同一个表中比较不同行的数据，例如比较员工与其经理之间的关系。

select e1.name as 员工姓名, e2.name as 经理姓名 
from employees e1 
inner join employees e2 on e1.manager_id = e2.id;

输出结果：

employees.name	经理姓名
张三	李四
王五	张三

解释：

• 数据来源表：
  • employees：员工表，包含员工的姓名、经理 ID 等信息。
• 连接条件：e1.manager_id = e2.id，即通过自连接查询每个员工的经理。
• 结果说明：自连接在同一个表中进行，employees 表被分为两个别名，分别代表员工和经理。最终查询结果返回每个员工对应的经理姓名。

7. 子查询 (Subquery)

子查询是指一个查询语句嵌套在另一个查询语句中，常用于复杂条件的筛选。根据子查询的位置不同，子查询分为以下几类：

1. 在 where 子句中使用子查询：

   select name 
   from employees 
   where dept_id = (select id from departments where name = 'IT');
   

   输出结果：

   employees.name
   张三
   李四

   解释：该查询首先通过子查询找出 IT 部门的 id，然后根据这个 id 查找属于该部门的员工。

2. 在 select 子句中使用子查询：

   select name, (select name from departments where id = employees.dept_id) as 部门名称 
   from employees;
   

   输出结果：

   employees.name	departments.name
   张三	IT 部门
   李四	IT 部门
   王五	HR 部门

   解释：该查询在 select 子句中使用了子查询，通过部门 ID 返回部门名称，与员工信息一起显示。

3. 在 from 子句中使用子查询（派生表）：

   select t.name, t.salary 
   from (select name, salary from employees where salary > 5000) as t;
   

   输出结果：

t.name	t.salary
张三	8000
李四	9000

解释：该查询通过子查询筛选出工资大于 5000 的员工，作为一个派生表 t，然后在外层查询中进一步处理。

8. 多表关联查询

当需要同时查询多个表的数据时，可以通过多次 join 来实现。

两表查询

select t1.column1, t2.column2 
from table1 t1 
inner join table2 t2 on t1.key = t2.key;

三表查询

select t1.column1, t2.column2, t3.column3 
from table1 t1 
inner join table2 t2 on t1.key1 = t2.key1 
inner join table3 t3 on t2.key2 = t3.key2;

嵌套查询（子查询）

select column1, column2 
from table1 
where key in (select key from table2 where condition);

9. 使用 union 合并查询结果

union 和 union all 用于合并多个查询结果：

• union：去重合并，效率较低。
• union all：不去重合并，效率较高。

select 字段列表 from 表1 
union 
select 字段列表 from 表2;

输出结果（假设有重复记录）：

字段1	字段2
值1	值2
值3	值4