Mysql多表查询

第六章：多表查询

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

1.一个案例引发的多表连接

 例子：查询员工名为Abel的人在那个城市工作

#不用多表查询：
SELECT * FROM employees WHERE last_name = "Abel"
SELECT * FROM departments WHERE department_id = 80
SELECT * FROM locations WHERE location_id = 2500

 这样查询显得太麻烦，要三次访问数据库，所以要引入多表查询。

 为什么要将员工表分成三张表？

2.实现多表的查询

1.错误情况：

#案例：查询员工的姓名及其部门名称
#实现多表的查询
SELECT employee_id,department_name FROM employees,departments # 共2889条记录

 注：在 MySQL 中，DUAL 是一个特殊的表，它只有一行一列。通常在执行不需要从实际表中获取数据的查询时使用，比如进行一些简单的计算或测试。

2. 分析错误情况：

SELECT count(*) FROM employees; #107条记录
SELECT 2889/107 FROM DUAL # 结果为27
SELECT count(*) FROM departments #结果为27

 通过上面的结果和下图可以看出，每个员工和每个部门都匹配了一遍，这种现像又叫出现笛卡尔积的错误

对于笛卡尔积（或交叉连接）的理解不再赘述。

3.解决方法：

加入多表的连接条件，即：

SELECT employee_id,department_name FROM employees,departments
 WHERE employees.department_id = departments.department_id# 共106条记录

但是一共有107个员工，为什么只有106条结果？下面的外连接中会进行讲解。

4.sql优化：指定字段来自哪个表

#如果查询语句中出现了多张表都出现的字段，必须要指明字段来自哪张表，如department_id在两张表中都出现
#从sql优化的角度上来看，建议每个字段都加上表名
#错误写法SELECT employee_id,department_name,department_id FROM employees,departments WHERE 
#employees.department_id = departments.department_id
正确的写法：SELECT employees.employee_id,departments.department_name,departments.department_id FROM employees,departments WHERE 
employees.department_id = departments.department_id

省去了去数据库的每个表中去找对应的字段的时间，直接指定去哪个表中找

5.为表起别名，提高可读性

注意：一旦起了别名，在select和where语句中使用时就要用别名，不能用原来的名字

如下：为employee表和departments表分别起别名emp和dept

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

结论：如果有n个表要连接，那么至少要（n  -  1）个连接条件

3.多表查询的分类

1.角度一：等值连接和非等值连接

上面的例子都属于等值连接，下面看非等值连接

SELECT e.last_name,e.salary,j.grade_level 
FROM job_grades j,employees e 
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
#或者：
SELECT e.last_name,e.salary,j.grade_level 
FROM job_grades j,employees e 
WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal

2.角度二：自连结和非自连接

上面的都是非自连接，下面看自连接

案例：查询员工的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

3.角度三：内连接和外连接

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

外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的 行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。

如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

还有一种是满外连接

1.SQL92语法：使用(+)创建连接

在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。 Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。

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

而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

2. SQL99语法实现多表查询（使用join...on...）

实现内连接：

#查询员工的名字和对应部门的名字
SELECT e.last_name,d.department_name 
FROM employees e JOIN departments d 
ON e.department_id = d.department_id
#查询员工的名字和部门名字以及对应的城市 （多张表就join ... on ...）
SELECT e.last_name,d.department_name,l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l 
ON l.location_id = d.location_id

实现外连接：

注意是否是查询所有的员工，所有的  （要用外连接）

#左外连接（有些人没有部门107条记录）
#查询所有的员工的名字和对应的部门的信息
SELECT e.last_name,d.department_name 
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id
#右外连接（有些部门没有人，122条记录）
SELECT e.last_name,d.department_name 
FROM employees e RIGHT JOIN departments d 
ON e.department_id = d.department_id
#满外连接(但是MYSQL不支持)
SELECT e.last_name,d.department_name 
FROM employees e FULL OUTER JOIN departments d 
ON e.department_id = d.department_id

那么怎么实现满外连接？

4.UNION的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并 时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

UNION操作符 

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符（常用）

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

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

 5.七种JOIN的实现

#3.七种JOIN : 查询员工的id和对应的部门的名称
#1.中图（内连接106条记录）
SELECT e.employee_id,d.department_name 
FROM employees e JOIN departments d 
ON e.department_id = d.department_id
#2.左上图（左外连接107条记录）
SELECT e.employee_id,d.department_name 
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id
#3.右上图（右外连接122条记录）
SELECT e.employee_id,d.department_name 
FROM employees e RIGHT JOIN departments d 
ON e.department_id = d.department_id
#4.左中图（1条记录）
SELECT e.employee_id,d.department_name 
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
#5.右中图（16条记录）
SELECT e.employee_id,d.department_name 
FROM employees e RIGHT JOIN departments d 
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
#6.左下图（满外连接123条记录）
	#方式一：左上图加右中图 UNION ALL
SELECT e.employee_id,d.department_name 
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id
UNION ALL
SELECT e.employee_id,d.department_name 
FROM employees e RIGHT JOIN departments d 
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
	#方式二:左中图加右上图
SELECT e.employee_id,d.department_name 
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT e.employee_id,d.department_name 
FROM employees e RIGHT JOIN departments d 
ON e.department_id = d.department_id
#7.右下图（17条记录）
 #左中图加右中图
SELECT e.employee_id,d.department_name 
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT e.employee_id,d.department_name 
FROM employees e RIGHT JOIN departments d 
ON e.department_id = d.department_id
WHERE e.department_id IS NULL

6.SQL99语法新特性（了解）

1.自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把 自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值 连接 。

在SQL92标准中：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以写成：

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

6.2 USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配 合JOIN一起使用。比如：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下 面的 SQL 查询结果是相同的：

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

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

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

说明：即使双表 join 也要注意表索引、SQL 性能。