SQL 中多表查询的常见连接方式详解

1. 内连接 (inner join)
   +-------------+    +-------------+
   |   table a   |    |   table b   |
   +-------------+    +-------------+
          | 交集部分  |
          +-----------+
2. 左连接 (left join)
   +-------------+    +-------------+
   |   table a   |    |   table b   |
   +-------------+    +-------------+
          | 全部保留   |
          | 右表匹配   |
3. 右连接 (right join)
   +-------------+    +-------------+
   |   table a   |    |   table b   |
   +-------------+    +-------------+
          | 左表匹配   |
          | 全部保留   |
4. 全外连接 (full outer join)
   +-------------+    +-------------+
   |   table a   |    |   table b   |
   +-------------+    +-------------+
          | 全部保留   |
          | 全部保留   |

-- 创建示例表
create table employees (
  id int primary key,
  name varchar(50),
  dept_id int
);
create table departments (
  id int primary key,
  dept_name varchar(50)
);
-- 插入测试数据
insert into employees values
(1, 'alice', 101),
(2, 'bob', 102),
(3, 'charlie', 103),
(4, 'david', null);
insert into departments values
(101, 'hr'),
(102, 'engineering'),
(104, 'marketing');

select 
  e.name as 员工姓名,   -- 从 employees 表中选择员工姓名
  d.dept_name as 部门名称  -- 从 departments 表中选择部门名称
from employees e         -- 主表：employees（别名为 e）
inner join departments d -- 连接表：departments（别名为 d）
  on e.dept_id = d.id;   -- 连接条件：员工的部门id = 部门的id

employees（e）               departments（d）
+----+---------+---------+    +-----+-------------+
| id | name    | dept_id |    | id  | dept_name   |
+----+---------+---------+    +-----+-------------+
| 1  | alice   | 101     |    | 101 | hr          |
| 2  | bob     | 102     |    | 102 | engineering |
| 3  | charlie | 103     |    | 104 | marketing   |
| 4  | david   | null    |    +-----+-------------+
+----+---------+---------+
通过 inner join 连接条件 e.dept_id = d.id：

select e.name, d.dept_name
from employees e
left join departments d on e.dept_id = d.id;

select e.name, d.dept_name
from employees e
right join departments d on e.dept_id = d.id;

-- mysql 不支持 full outer join，需用 union 模拟
select e.name, d.dept_name
from employees e
left join departments d on e.dept_id = d.id
union
select e.name, d.dept_name
from employees e
right join departments d on e.dept_id = d.id;

select e.name, d.dept_name
from employees e
cross join departments d;

自连接 (self join)
   +-------------+        +-------------+
   |   table     |        |   table     |
   |  (别名为a)   |        |  (别名为b)   |
   +-------------+        +-------------+
          | 关联自身的字段 |
          +----------------+

-- 创建带 manager_id 的员工表
create table employees (
  id int primary key,
  name varchar(50),
  manager_id int
);
-- 插入测试数据
insert into employees values
(1, 'alice', null),  -- 顶级管理者
(2, 'bob', 1),       -- 向 alice 汇报
(3, 'charlie', 1),   -- 向 alice 汇报
(4, 'david', 2);     -- 向 bob 汇报

select
  e.name as employee,
  m.name as manager
from employees e
left join employees m on e.manager_id = m.id;

select
  e.name as employee,
  m.name as manager
from employees e
inner join employees m on e.manager_id = m.id;

select 
  e.name,
  coalesce(m.name, '顶级管理者') as manager
from employees e
left join employees m on e.manager_id = m.id;