6、表联结 JOIN-尧图网络科技

1、表联结 JOIN

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

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

1）内连接（INNER JOIN）

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

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

例子

-- 示例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）‌：

返回左表所有记录 + 右表匹配记录
结果集：左表全集 + 右表匹配部分，右表无匹配时显示NULL。

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

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

例子

-- 示例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）‌：

返回右表所有记录 + 左表匹配记录
结果集：右表全集 + 左表匹配部分，左表无匹配时显示NULL。

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

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

例子

-- 示例：查询所有部门及其员工信息（包括没有员工的部门）
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）‌：

返回两个表的所有记录
结果集：左表全集 + 右表全集，匹配部分合并，不匹配部分用NULL填充
使用场景：需要同时查看两个表的完整信息及关联关系

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

例子

-- 示例：完整查看员工和项目的关联情况
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）：

返回两表的笛卡尔积，
结果集：左表每行与右表所有行组合，无关联条件，结果行数为两表行数乘积。

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

例子

-- 示例1：生成所有员工和所有部门的组合
SELECT e.first_name,e.last_name,d.department_name
FROM employees e
CROSS JOIN departments d;-- 结果分析：
-- 如果employees有100人，departments有10个部门
-- 结果将产生100 × 10 = 1000条记录

（5）自连接 (Self Join)

表与自身连接
核心思想：是‌为同一张表创建多个别名‌，然后通过连接条件将这些别名关联起来‌。

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

例子

-- 示例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确保没有经理的员工也能显示