MySQL多表查询

五.多表查询

什么叫多表查询：

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

为什么要实现多表查询：避免冗余，IO次数多，不易维护

1.笛卡尔积错误

错误原因：缺少了多表连接的条件

• 笛卡尔积的错误会在下面条件下产生：
  • 省略多个表的连接条件（或关联条件）
  • 连接条件（或关联条件）无效
  • 所有表中的所有行互相连接
• 为了避免笛卡尔积， 可以在WHERE加入有效的连接条件。

2.多表查询的正确方式

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

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

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

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

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

这里从FROM开始执行，相当于一开始就已经把表起了别名，再用表的原名则会报错

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

#查询员工的employee_ id, last_ name , department_ name, city 
SELECT emp.employee_id,emp.last_name,dept.department_name,loca.city
FROM employees emp,departments dept,locations loca
#三表相连接
WHERE emp.department_id = dept.department_id 
AND dept.location_id = loca.location_id;

3.自连接举例

SELECT emp.employee_id,emp.last_name,mng.employee_id,mng.last_name
FROM employees emp,employees mng
WHERE emp.manager_id = mng.employee_id;

4.外连接和内连接

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

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

外连接的分类：左外连接，右外连接，满外连接

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

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

SQL92 实现外连接：使用 + ，如下所示（但是MySQL不支持SQL92版本，Oracle支持）

#查询  《所有的》  员工的last_name,departments_name信息
SELECT e.last_name,d.department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id(+)

SQL99实现外连接：SQL99语法中使用 JOIN…ON 的方式实现多表的查询。这种方式也能解决外连接的问题。MySQL是支持的

#3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name,d.department_name,d.location_id,l.city
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id
#左连接之后，跟locations连接时也需要补数据
LEFT JOIN locations l 
ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL;

5. 7种SQL JOINS的实现

#SQL99实现外连接
#查询  《所有的》  员工的last_name,departments_name信息
#左外连接
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

#右外连接
SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

#满外连接（左上图 + 右中图实现）
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id	
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

6.UNION和UNION ALL

UNION操作符

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

UNION ALL操作符

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

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

7.SQL99新特性

7.1 自然连接

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`;

# NATURAL JOIN : 它会帮你自动查询两张连接表中`所有相同的字段`，然后进行`等值连接`。（不够灵活，不建议使用）
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

7.2 USING连接

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

# 指定数据表里的同名字段进行等值连接，只能配合JOIN一起使用。（不适用于自连接）
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

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

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