知识库

多表查询

也称关联查询，指两个或更多个表一起完成查询操作。前提：这些一起查询的表之间是由关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键，如员工表和部门表，这两个表依靠部门编号进行关联。

sql

# 查询员工 Abel 在哪个城市工作
SELECT *
FROM employees
WHERE last_name = 'Abel';

SELECT *
FROM departments
WHERE department_id = 80;

SELECT *
FROM locations
WHERE location_id = 2500;

# 错误的实现，每个员工都与每个部门匹配了一遍，出现了笛卡尔积的错误。
# 错误的原因是缺少了多表的连接条件。
SELECT employee_id, department_name
FROM employees, departments;

# 和上面一样
SELECT employee_id, department_name
FROM employees CROSS JOIN departments;

# 加入连接条件
SELECT employee_id, department_name
FROM employees, departments
# 连接条件
WHERE employees.department_id = departments.department_id;

# 报错 Column 'department_id' in field list is ambiguous
# 因为 employees, departments 都有 department_id，不知道去哪张表找。
# 如果查询语句中出现了多个表中都存在的字段，必须指明该字段所在的表。
SELECT employee_id, department_name, department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

# 或写 departments.department_id
SELECT employee_id, department_name, employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

# 从 SQL 优化的角度，建议多表查询时每个字段前都指明其所在的表
SELECT employees.employee_id, departments.department_name, employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

# 可以给 FROM 中给表取别名，在 SELECT 和 WHERE 中使用，提高可读性。
# 如果给表取了别名，在 SELECT 和 WHERE 中如果使用表名，就必须使用别名，不能使用原名。
SELECT emp.employee_id, dep.department_name, emp.department_id
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id;

# 查询员工的 employee_id, last_name, department_name, city
SELECT emp.employee_id, emp.last_name, dep.department_name, loc.city
FROM employees emp, departments dep, locations loc
# n 个表实现多表查询，则至少需要 n - 1 个连接条件。
WHERE emp.department_id = dep.department_id
AND dep.location_id = loc.location_id;

从三个角度对多表查询进行分类

等值连接和非等值连接

非等值连接

sql

# 根据工资查询出员工的级别
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接和非自连接

自连接

sql

# 查询员工 id 和姓名及其管理者 id 和姓名
SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;

内连接和外连接

内连接：合并具有同一列的两个及以上的表的行，结果集中不包括一个表与另一个表不匹配的行。

sql

# SQL92 实现内连接
SELECT emp.employee_id, dep.department_name
FROM employees emp, departments dep
WHERE emp.department_id = dep.department_id;

# SQL99 实现内连接
SELECT emp.employee_id, dep.department_name
FROM employees emp
JOIN departments dep
ON emp.department_id = dep.department_id;

# 再连一个
SELECT emp.employee_id, dep.department_name
FROM employees emp
JOIN departments dep
ON emp.department_id = dep.department_id;
# INNER 可以省略
INNER JOIN locations loc
ON dep.location_id = loc.location_id;

# 也可以写成
SELECT emp.employee_id, dep.department_name
FROM employees emp
JOIN departments dep
JOIN locations loc
ON emp.department_id = dep.department_id;
AND dep.location_id = loc.location_id;

外连接：合并具有同一列的两个及以上的表的行，结果集中除了包括一个表与另一个表匹配的行外，还查询到了左表或右表中不匹配的行。

外连接的分类：

左外连接：合并具有同一列的两个及以上的表的行，结果集中除了包括一个表与另一个表匹配的行外，还查询到了左表中不匹配的行。
右外连接：合并具有同一列的两个及以上的表的行，结果集中除了包括一个表与另一个表匹配的行外，还查询到了右表中不匹配的行。
满外连接：合并具有同一列的两个及以上的表的行，结果集中除了包括一个表与另一个表匹配的行外，还查询到了左表和右表中不匹配的行。

sql

# 查询所有的员工的 last_name, department_name 信息

# 只能查询到 106 条，有个员工 department_name 为 NULL。
SELECT emp.last_name, dep.department_name
FROM employees emp, departments dep
# 右边（dep）缺一条数据，需要使用左外连接。
WHERE emp.department_id = dep.department_id;

# SQL92 语法实现外连接，使用 +，但是 MySQL 不支持 SQL92 语法中外连接的写法。
SELECT emp.last_name, dep.department_name
FROM employees emp, departments dep
# 左外连接
WHERE emp.department_id = dep.department_id(+);

SELECT emp.last_name, dep.department_name
FROM employees emp, departments dep
# 右外连接
WHERE dep.department_id(+) = emp.department_id;

# SQL99 中使用 JOIN...ON 的方式实现多表的查询，这种方式也能实现外连接，MySQL 支持。
SELECT emp.last_name, dep.department_name
FROM employees emp
# OUTER 可以省略
LEFT OUTER JOIN departments dep
ON emp.department_id = dep.department_id;

SELECT emp.last_name, dep.department_name
FROM departments dep
RIGHT OUTER JOIN employees emp
ON dep.department_id = emp.department_id;

# 满外连接，MySQL 不支持 FULL。
SELECT emp.last_name, dep.department_name
FROM departments dep
FULL OUTER JOIN employees emp
ON dep.department_id = emp.department_id;

为什么要分表

不进行分表会有很多冗余数据，增加内存开销。
读取、查询数据时要花费更多事件
当一个人查询一张表时，被锁了，其他人不能查询。

笛卡尔积（交叉连接）

两个集合 x 和 y，x 和 y 的笛卡尔积就是 x 和 y 的所有可能组合，组合个数为两个集合中元素个数的乘积。

笛卡尔积的错误产生条件

省略多表的连接条件
连接条件无效
所有表中的所有行互相连接

多表查询 ​

从三个角度对多表查询进行分类 ​

为什么要分表 ​

笛卡尔积（交叉连接） ​

笛卡尔积的错误产生条件 ​

多表查询

从三个角度对多表查询进行分类

为什么要分表

笛卡尔积（交叉连接）

笛卡尔积的错误产生条件