多表查询进阶

一、连接查询基础

1.1 连接查询概念

1.1.1 什么是连接查询

连接查询：从多个表中查询数据，通过关联条件将表连接起来。

东巴文理解：

连接查询 = 桥梁连接
表A ----[连接条件]---- 表B
就像两座岛屿之间架起桥梁，可以自由往来

1.1.2 准备测试数据

-- 创建部门表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
);

-- 创建员工表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2),
    dept_id INT,
    manager_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- 插入部门数据
INSERT INTO departments (dept_name, location) VALUES
('技术部', '北京'),
('销售部', '上海'),
('财务部', '广州'),
('人事部', '深圳');

-- 插入员工数据
INSERT INTO employees (name, salary, dept_id, manager_id) VALUES
('张三', 15000.00, 1, NULL),
('李四', 12000.00, 1, 1),
('王五', 13000.00, 1, 1),
('赵六', 10000.00, 2, NULL),
('孙七', 9000.00, 2, 4),
('周八', 11000.00, 3, NULL),
('吴九', 8000.00, NULL, NULL);

1.2 内连接

1.2.1 INNER JOIN

语法格式：

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

示例：

-- 查询员工及其部门信息
SELECT 
    e.name AS employee_name,
    e.salary,
    d.dept_name,
    d.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

输出：

+---------------+----------+-----------+----------+
| employee_name | salary   | dept_name | location |
+---------------+----------+-----------+----------+
| 张三          | 15000.00 | 技术部    | 北京     |
| 李四          | 12000.00 | 技术部    | 北京     |
| 王五          | 13000.00 | 技术部    | 北京     |
| 赵六          | 10000.00 | 销售部    | 上海     |
| 孙七          |  9000.00 | 销售部    | 上海     |
| 周八          | 11000.00 | 财务部    | 广州     |
+---------------+----------+-----------+----------+

特点：

• 只返回匹配的行
• 不匹配的行不显示

1.2.2 等值连接

使用WHERE子句：

SELECT e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

1.3 左外连接

1.3.1 LEFT JOIN

语法格式：

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

示例：

-- 查询所有员工及其部门（包括没有部门的员工）
SELECT 
    e.name,
    d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

输出：

+--------+-----------+
| name   | dept_name |
+--------+-----------+
| 张三   | 技术部    |
| 李四   | 技术部    |
| 王五   | 技术部    |
| 赵六   | 销售部    |
| 孙七   | 销售部    |
| 周八   | 财务部    |
| 吴九   | NULL      |
+--------+-----------+

特点：

• 返回左表所有行
• 右表不匹配显示NULL

1.3.2 只查询左表独有数据

-- 查询没有部门的员工
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;

1.4 右外连接

1.4.1 RIGHT JOIN

语法格式：

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

示例：

-- 查询所有部门及其员工（包括没有员工的部门）
SELECT 
    e.name,
    d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

输出：

+--------+-----------+
| name   | dept_name |
+--------+-----------+
| 张三   | 技术部    |
| 李四   | 技术部    |
| �五   | 技术部    |
| 赵六   | 销售部    |
| 孙七   | 销售部    |
| 周八   | 财务部    |
| NULL   | 人事部    |
+--------+-----------+

1.5 全外连接

1.5.1 MySQL实现

MySQL不支持FULL 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;

1.6 自连接

1.6.1 同表连接

示例：

-- 查询员工及其经理
SELECT 
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

输出：

+----------+--------+
| employee | manager |
+----------+--------+
| 张三     | NULL   |
| 李四     | 张三   |
| 王五     | 张三   |
| 赵六     | NULL   |
| 孙七     | 赵六   |
| 周八     | NULL   |
| 吴九     | NULL   |
+----------+--------+

二、多表连接

2.1 三表连接

2.1.1 连接多个表

示例：

-- 创建项目表
CREATE TABLE projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100),
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

INSERT INTO projects (project_name, employee_id) VALUES
('电商平台', 1),
('CRM系统', 2),
('数据分析', 3);

-- 查询员工、部门和项目
SELECT 
    e.name,
    d.dept_name,
    p.project_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
LEFT JOIN projects p ON e.id = p.employee_id;

2.2 连接顺序

2.2.1 优化原则

原则：

1. 小表驱动大表
2. 过滤条件尽早应用
3. 减少中间结果集

示例：

-- 不推荐：大表驱动小表
SELECT *
FROM large_table l
INNER JOIN small_table s ON l.id = s.id;

-- 推荐：小表驱动大表
SELECT *
FROM small_table s
INNER JOIN large_table l ON s.id = l.id;

三、子查询

3.1 WHERE子查询

3.1.1 标量子查询

返回单行单列

-- 查询工资高于平均工资的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

3.1.2 列子查询

返回单列多行

使用IN：

-- 查询技术部的员工
SELECT name
FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE dept_name = '技术部');

使用ANY/SOME：

-- 查询工资高于销售部任意员工的员工
SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees
    WHERE dept_id = (SELECT id FROM departments WHERE dept_name = '销售部')
);

使用ALL：

-- 查询工资高于销售部所有员工的员工
SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees
    WHERE dept_id = (SELECT id FROM departments WHERE dept_name = '销售部')
);

3.1.3 行子查询

返回单行多列

-- 查询与张三同部门同工资的员工
SELECT name
FROM employees
WHERE (dept_id, salary) = (
    SELECT dept_id, salary
    FROM employees
    WHERE name = '张三'
);

3.2 FROM子查询

3.2.1 派生表

语法格式：

SELECT 列名
FROM (SELECT语句) AS 别名;

示例：

-- 查询每个部门的平均工资，并筛选高于公司平均工资的部门
SELECT dept_name, avg_salary
FROM (
    SELECT 
        d.dept_name,
        AVG(e.salary) AS avg_salary
    FROM employees e
    INNER JOIN departments d ON e.dept_id = d.id
    GROUP BY d.dept_name
) AS dept_avg
WHERE avg_salary > (SELECT AVG(salary) FROM employees);

3.3 SELECT子查询

3.3.1 相关子查询

示例：

-- 查询每个部门的员工数量
SELECT 
    d.dept_name,
    (SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.id) AS emp_count
FROM departments d;

3.4 EXISTS子查询

3.4.1 EXISTS用法

语法格式：

SELECT 列名
FROM 表1
WHERE EXISTS (SELECT 1 FROM 表2 WHERE 条件);

示例：

-- 查询有员工的部门
SELECT d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

3.4.2 NOT EXISTS用法

-- 查询没有员工的部门
SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

3.5 子查询优化

3.5.1 使用JOIN替代子查询

子查询版本：

SELECT name
FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE dept_name = '技术部');

JOIN版本：

SELECT e.name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE d.dept_name = '技术部';

性能对比：

• JOIN通常性能更好
• 子查询更易读
• 根据实际情况选择

四、联合查询

4.1 UNION

4.1.1 基本语法

语法格式：

SELECT语句1
UNION
SELECT语句2;

示例：

-- 查询技术部和销售部的员工
SELECT name, '技术部' AS dept
FROM employees
WHERE dept_id = 1

UNION

SELECT name, '销售部' AS dept
FROM employees
WHERE dept_id = 2;

特点：

• 合并结果集
• 去除重复行
• 列数必须相同
• 列类型必须兼容

4.2 UNION ALL

4.2.1 保留重复行

示例：

-- 查询所有员工姓名（包含重复）
SELECT name FROM employees
UNION ALL
SELECT name FROM employees;

性能对比：

• UNION ALL更快
• 不需要去重
• 根据需求选择

4.3 INTERSECT和EXCEPT

4.3.1 MySQL实现

MySQL不支持INTERSECT和EXCEPT，使用其他方式模拟：

INTERSECT（交集）：

-- 查询同时出现在两个表中的员工
SELECT name FROM employees WHERE dept_id = 1
AND name IN (SELECT name FROM employees WHERE salary > 10000);

EXCEPT（差集）：

-- 查询在表A但不在表B的员工
SELECT name FROM employees WHERE dept_id = 1
AND name NOT IN (SELECT name FROM employees WHERE salary > 12000);

五、连接查询优化

5.1 索引优化

5.1.1 连接字段索引

原则：

• 连接字段建立索引
• 外键字段建立索引
• 频繁查询字段建立索引

示例：

-- 为连接字段创建索引
CREATE INDEX idx_dept_id ON employees(dept_id);
CREATE INDEX idx_manager_id ON employees(manager_id);

5.2 连接类型选择

5.2.1 连接类型对比

5.3 查询优化技巧

5.3.1 使用EXPLAIN分析

EXPLAIN SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

关注指标：

• type：连接类型
• key：使用的索引
• rows：扫描行数
• Extra：额外信息

5.3.2 减少返回列

-- 不推荐：SELECT *
SELECT * FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- 推荐：只查询需要的列
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

5.3.3 使用表别名

-- 使用表别名简化SQL
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

六、实战案例

6.1 查询员工层级结构

-- 查询员工及其所有上级
WITH RECURSIVE emp_hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN emp_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM emp_hierarchy;

6.2 查询部门统计信息

-- 查询每个部门的员工数量、平均工资、最高工资、最低工资
SELECT 
    d.dept_name,
    COUNT(e.id) AS emp_count,
    IFNULL(AVG(e.salary), 0) AS avg_salary,
    IFNULL(MAX(e.salary), 0) AS max_salary,
    IFNULL(MIN(e.salary), 0) AS min_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.dept_id
GROUP BY d.id, d.dept_name;

6.3 查询工资排名

-- 查询每个部门的员工工资排名
SELECT 
    d.dept_name,
    e.name,
    e.salary,
    RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS salary_rank
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

6.4 查询跨部门项目

-- 查询涉及多个部门的项目
SELECT 
    p.project_name,
    GROUP_CONCAT(DISTINCT d.dept_name) AS departments
FROM projects p
INNER JOIN employees e ON p.employee_id = e.id
INNER JOIN departments d ON e.dept_id = d.id
GROUP BY p.id, p.project_name
HAVING COUNT(DISTINCT d.id) > 1;

七、本章小结

7.1 核心要点

✅ 内连接返回匹配行，外连接返回所有行 ✅ 子查询可以出现在WHERE、FROM、SELECT中 ✅ UNION合并结果集，UNION ALL保留重复 ✅ 索引优化提高连接查询性能 ✅ EXPLAIN分析查询执行计划

7.2 验证清单

完成本章学习后，请确认您能够：

• 使用INNER/LEFT/RIGHT JOIN进行连接查询
• 使用子查询解决复杂问题
• 使用UNION合并结果集
• 优化多表查询性能
• 使用EXPLAIN分析查询