【MySQL笔记】多表查询（JOIN ON）_join on多表的用法

多表查询，也称关联查询，指两个或更多个表一起完成查询操作

目录

多表查询的前提条件

多表查询的基本语法

多表查询的分类

等值连接与非等值连接

自连接与非自连接

内连接与外连接（JOIN ON）

---

现在我们假设有两个表，employees 和 departments，它们之中有一些重复的字段

多表查询的前提条件

这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如员工表与部门表之间依靠 “部门编号” 进行关联

多表查询的基本语法

有连接条件

如果有 n 个表实现多表的查询，则需要至少 n - 1 个连接条件

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

如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表

建议：从 SQL 优化的角度，建议多表查询时，每个字段前都指明其所在的表

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

可以给表起别名，在 SELECT 和 WHERE 中使用表的别名

注：如果给表起了别名，一旦在 SELECT 或 WHERE 中使用表名的话，则必须使用表的别名，而不能再使用表的原名

SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept 
WHERE emp.department_id = dept.department_id;

多表查询的分类

等值连接与非等值连接

上述基本语法中的例子都是等值连接

我们现在举一个非等值连接的例子

SELECT emp.employee_id,emp.salary,j.grade_level
FROM employees emp,job_grades j
#非等值连接
WHERE emp.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接与非自连接

上述基本语法中的例子都是非自连接

自连接顾名思义就是自己的字段连接自己的字段，我们举一个例子

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
#自连接
FROM employees emp,employees mgr 
WHERE emp.manager_id = mgr.employee_id;

内连接与外连接（JOIN ON）

因为 MySQL 中不支持 SQL 92 语法，我们在本文中就只介绍 MySQL 中支持的 SQL 99 语法，SQL 99 中使用 JOIN ON 实现多表的查询

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

SELECT last_name,department_name,city
FROM employees e INNER JOIN departments d # INNER 可以省略
ON e.department_id = d.department_id
JOIN locations
ON d.location_id = l.location_id;

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

             

左外连接：两个表在连接过程中除返回满足连接条件的行以外还返回左表中不满足条件的行

SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d # OUTER 可以省略
ON e.department_id = d.department_id;

右外连接：两个表在连接过程中除返回满足连接条件的行以外还返回右表中不满足条件的行

SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d # OUTER 可以省略
ON e.department_id = d.department_id;

满外连接：MySQL 不支持 FULL OUTER JOIN，但支持 UNION 与 UNION ALL

 

注：执行 UNION ALL 语句时所需要的资源比 UNION 语句少。如果明确知道合并后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用 UNION ALL 语句，以提高数据查询的效率

SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;

注意：我们要控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引