6、表联结 JOIN

1、表联结 JOIN

用于关联多个表的核心操作，通过共同字段将数据组合，实现高效查询。

在一条 SELECT 语句中关联表,因此称为联结。使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。

1）内连接（INNER JOIN）

返回两表中‌完全匹配‌的行，仅保留关联字段值相等的记录。

SELECT 列名列表
FROM 表1
[INNER] JOIN 表2
  ON 表1.列 = 表2.列;

• ON 子句指定连接条件（通常是主键 = 外键）

例子

假设有两张表：

表 users（用户表）

id	name
1	Alice
2	Bob
3	Charlie

表 orders（订单表）

order_id	user_id	product
101	1	Laptop
102	1	Mouse
103	4	Keyboard

执行 INNER JOIN：

SELECT u.name, o.product
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

结果：

name	product
Alice	Laptop
Alice	Mouse

 

🔍 注意：

• Bob（id=2）没有订单 → 不出现
• Charlie（id=3）没有订单 → 不出现
• 订单 103（user_id=4）无对应用户 → 不出现

-- 示例1：基本的员工-部门信息查询
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 执行过程解析：
-- 1. 从employees表取出一条记录
-- 2. 根据department_id在departments表中查找匹配记录
-- 3. 如果找到匹配，将两条记录合并为一行输出
-- 4. 如果没有匹配，跳过该记录
-- 5. 重复以上过程直到处理完所有员工记录

2）外连接（OUTER JOIN）

（1）左连接（LEFT JOIN）‌：

• 返回左表（FROM 后的表）的所有行
• 如果右表有匹配行 → 正常显示
• 如果右表无匹配行 → 右表字段显示为 NULL

适用于需保留左表完整数据（如所有客户信息）的场景‌。

SELECT 列列表
FROM 表1
LEFT [OUTER] JOIN 表2 ON 连接条件;

例子

表 users（用户表）

id	name
1	Alice
2	Bob
3	Charlie

表 orders（订单表）

order_id	user_id	product
101	1	Laptop
102	1	Mouse
103	4	Keyboard

执行 LEFT JOIN：

SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

结果：

name	product
Alice	Laptop
Alice	Mouse
Bob	NULL
Charlie	NULL

🔍 关键点：

• Alice 有 2 个订单 → 出现 2 行
• Bob 和 Charlie 没有订单 → 仍出现在结果中，product 为 NULL
• 订单 103（user_id=4）被忽略（因为不在左表 users 中）

-- 示例1：查询所有员工及其部门信息（包括未分配部门的员工）
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    COALESCE(d.department_name, '未分配部门') AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 结果分析：
-- 有部门的员工：显示部门名称
-- 无部门的员工：department_name显示为NULL，我们用COALESCE转换为'未分配部门'

‌（2）右连接（RIGHT JOIN）‌：

• 返回右表（JOIN 后的表）的所有行
• 如果左表有匹配行 → 正常显示
• 如果左表无匹配行 → 左表字段显示为 NULL

使用场景：需要显示副表所有记录，即使主表没有对应数据

SELECT 列列表
FROM 表1
RIGHT [OUTER] JOIN 表2 ON 连接条件;

例子

表 users（用户表）

id	name
1	Alice
2	Bob

表 orders（订单表）

order_id	user_id	product
101	1	Laptop
102	3	Mouse

执行 RIGHT JOIN：

SELECT u.name, o.product
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

结果：

name	product
Alice	Laptop
NULL	Mouse

🔍 关键点：

• 订单 101 匹配到 Alice → 正常显示
• 订单 102 的 user_id=3 在 users 中不存在 → name 为 NULL
• Bob（id=2）没有订单 → 不会出现在结果中（因为右表 orders 没有对应行）

-- 示例：查询所有部门及其员工信息（包括没有员工的部门）
SELECT 
    d.department_name,
    e.employee_id,
    e.first_name,
    e.last_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- 结果分析：
-- 有员工的部门：显示员工信息
-- 无员工的部门：员工相关列显示为NULL

（3）‌全连接（FULL JOIN）‌：

是 SQL 中一种保留两张表所有行的连接方式：

• 左表独有的行 → 右表字段为 NULL
• 右表独有的行 → 左表字段为 NULL
• 两表都有的行 → 正常合并

使用场景：需要同时查看两个表的完整信息及关联关系

SELECT 列名列表
FROM 表A
FULL [OUTER] JOIN 表B
  ON 表A.列 = 表B.列;

例子

表 students（学生）

id	name
1	Alice
2	Bob
3	Charlie

表 courses（课程报名）

student_id	course
1	Math
4	Physics

执行 FULL OUTER JOIN：

SELECT s.name, c.course
FROM students s
FULL OUTER JOIN courses c ON s.id = c.student_id;

结果（PostgreSQL / SQL Server）：

name	course
Alice	Math
Bob	NULL
Charlie	NULL
NULL	Physics

🔍 解读：

• Alice 有课程 → 正常显示
• Bob、Charlie 无课程 → course 为 NULL
• Physics 的 student_id=4 无对应学生 → name 为 NULL

-- 示例：完整查看员工和项目的关联情况
SELECT 
    COALESCE(e.employee_name, '无对应员工') AS employee_name,
    COALESCE(p.project_name, '无对应项目') AS project_name
FROM employees e
FULL OUTER JOIN project_assignments pa ON e.employee_id = pa.employee_id
FULL OUTER JOIN projects p ON pa.project_id = p.project_id;

-- 结果可能包含：
-- 1. 有员工有项目：正常显示
-- 2. 有员工无项目：项目信息为NULL
-- 3. 无员工有项目：员工信息为NULL
-- 4. 孤立的分配记录：员工和项目都为NULL

（4）交叉连接（CROSS JOIN）：

• 返回两表的笛卡尔积，两个表所有可能的行组合
• 结果行数 = 左表行数 × 右表行数，左表每行与右表所有行组合，无关联条件，结果行数为两表行数乘积。
• 没有 ON 条件（即使写了也会被忽略或报错）

-- 显式语法
SELECT 列名列表
FROM 表1
CROSS JOIN 表2;

-- 隐式语法
SELECT 列名列表
FROM 表1, 表2;

例子

表 colors

color

Red

Blue

表 sizes

size

S

M

L

执行 CROSS JOIN：

SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;

结果（2 × 3 = 6 行）：

color	size
Red	S
Red	M
Red	L
Blue	S
Blue	M
Blue	L

 

（5）自连接 (Self Join)

是一种比较同一表内行的连接。自连接使用内连接、左连接或右连接将一个表连接到其自身。

• 表与自身连接
• 核心思想：是‌为同一张表创建多个别名‌，然后通过连接条件将这些别名关联起来‌。
• 必须为表起不同的别名（如 e1, e2）
• 连接条件通常是主键 = 外键（如 id = manager_id）

SELECT 列列表
FROM 表名 别名1
JOIN 表名 别名2 ON 连接条件;

例子

表结构：employees

id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2

• id：员工唯一标识（主键）
• manager_id：该员工的直接上级的 id（外键，引用自身 id）
• CEO 的 manager_id 为 NULL

查询每个员工及其经理的名字

SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;    --当前员工的 manager_id 等于某位员工的 id，即：找出谁是这个员工的上级

结果：

employee	manager
Alice	NULL
Bob	Alice
Charlie	Alice

 

-- 示例1：员工-经理层级关系查询
SELECT 
    emp.employee_id,
    emp.first_name AS employee_name,
    emp.job_title AS employee_title,
    mgr.employee_id AS manager_id,
    mgr.first_name AS manager_name,
    mgr.job_title AS manager_title
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.employee_id;

-- 执行过程解析：
-- 1. 将employees表视为两个独立的表：emp(员工表)和mgr(经理表)
-- 2. 通过emp.manager_id = mgr.employee_id建立关联
-- 3. 使用LEFT JOIN确保没有经理的员工也能显示