（八）多表查询

目录

多表查询

笛卡尔积的错误

连接条件

表的别名

多表查询分类

自连接  和  非自连接 

内连接  和  外连接

SQL92

SQL99

UNION的使用

UNION操作符

UNION ALL操作符

7种SQL JOINS的实现

QL99语法新特性

自然连接

USING连接

---

多表查询

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

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

目前，如果要多表查询，就只能一张一张表去找，没有形成关联，速度与效率较慢，代码如下所示：

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;  #查询出2889条记录

错误的原因：

• 省略多个表的连接条件（或关联条件）
• 连接条件（或关联条件）无效
• 所有表中的所有行互相连接

笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交 叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。

#错误的方式
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;#查询出2889条记录

连接条件

为了避免笛卡尔积， 应该在 WHERE 加入有效的连接条件，具体代码如下所示。

SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件
WHERE employees.`department_id` = departments.`department_id`;

---

 注：在表中有相同列时，需要在列名之前加上表名前缀，如下所示。

#如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。
SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;
#建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。

---

如果有n个表实现多表的查询，则需要至少n-1个连接条件，具体代码如下所示：

#练习：查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

表的别名

• 使用别名可以简化查询。
• 列名前使用表名前缀可以提高查询效率。

因为有些表名比较的长，所以可以考虑起别名的方法，但是要注意，根据代码的执行次序，表起了别名之后，只能使用别名来操作，具体代码如下所示：

#给表起别名，在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或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
#如下的操作是错误的：
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = departments.department_id;

多表查询分类

等值连接 和 非等值连接

我们之前用等号连接的，都是等值连接，接下来主要介绍非等值连接，也就是在一个范围内相等的条件，代码如下所示：

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`;    # 寻找在某一个范围的对应关系
#WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= 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`;

运行结果如下所示：

内连接  和  外连接

• 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
• 外连接: 两个表在连接过程中除了返回满足连接条件的行以外，还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
• 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。
• 右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。
• 满外连接，同时满足左外连接和右外连接。

 因为SQL92语法，与SQL99语法对于实现内连接  和  外连接有所不同，于是分别讨论两种语法的实现，如下所示。

SQL92

SQL92语法实现内连接：和我们之前的实现是一样的，就不再重复。

SQL92语法实现外连接：使用 +  ----------MySQL不支持SQL92语法中外连接的写法

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id(+);
# 用哪一部分缺少数据，就在那一部分加上一个加号，上面实现的是左外连接

SQL99

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

SQL99语法实现内连接：

SELECT last_name,department_name,city
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;

SQL99语法实现外连接：

左外连接：

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 OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

运行结果如下所示：

 满外连接：mysql不支持FULL OUTER JOIN，具体解决方案，在后面给出。

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语句，以提高数据查询的效 率。 

7种SQL JOINS的实现

 中图：内连接

SELECT employee_id,department_name
FROM employees e 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`;

右上图：右外连接

SELECT employee_id,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`
WHERE d.`department_id` IS NULL;

运行结果如下所示：

 右中图：

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;

运行结果如下所示：

 左下图：满外连接

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;
# 使用UNION ALL语句连接两个表
# 方式1：左上图 UNION ALL 右中图

运行结果如下所示：

---

另一种方式：

SELECT employee_id,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 employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
# 方式2：左中图 UNION ALL 右上图

 右下图：

SELECT employee_id,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 employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
# 右下图：左中图  UNION ALL 右中图

运行结果如下所示：

QL99语法新特性

自然连接

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

---

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

运行结果两者是相同的，还是建议前者，后者有局限性。

USING连接

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

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

---

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

这两种是相等的，但是有局限性，不能自连接，不太建议使用。