第10章 多表查询

一、什么是多表查询

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

二、为什么需要多表查询

1、是否可以将多个表合并为一张表？

        答：可以，不过会有弊端。

2、弊端1：会增加很多冗余数据。

        比如：如果有员工表和部门表。在员工表中有100个人在10号部门，在部门表中只需要有一条数据记录该部门即可。但是合成一张表后就会有大量的数据冗余。

2、弊端2：在进行查询数据的时候数据需要从磁盘加载到内存中，当一张表的字段更多，加载相同数量的记录就需要更多的IO，影响查询性能。

3、弊端3：在事务中，因为表会锁定。如果是一张表，那么多人共用并不方便，效率也会更低。

三、熟悉需要使用的表

四、错误的多表查询（笛卡尔积）

1、案例

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;

分析：107 * 27 = 2889。错误条数的查询是因为员工表中的每一条记录都匹配了一遍部门表中的每一条记录，这种错误被称为笛卡尔积错误。错误的原因就是因为缺少了多表连接的条件。

2、笛卡尔积（或交叉连接）的理解

        笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能 组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

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

#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;# 交叉连接
SELECT last_name,department_name FROM employees INNER JOIN departments;# 内连接
SELECT last_name,department_name FROM employees JOIN departments;

3、案例分析与问题解决

笛卡尔积的错误会在下面条件下产生：

为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。 加入连接条件后，查询语法：

上述案例的正确写法：

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

注意：在查询的多个表中有相同列（字段）时，必须在列名之前加上表名前缀。

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

优化：如果查询的字段很多，那么就会写得很长，可以对查询的表起别名。但是如果起了别名，需要用表名就必须使用别名，而不能再使用原名。

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

4、n个表实现多表连接

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

例如：

五、多表查询的分类

1、分类

角度一：针对连接条件而言，分为等值连接与非等值连接。

角度二：针对连接的表间关系来讲，分为自连接与非自连接。

角度三：内连接与外连接。（见第11章）

2、示例

等值连接示例：

连接条件使用等号限制。

SELECT
	e.last_name,e.salary,e.department_id,d.department_name
FROM
	employees e,departments d
WHERE e.department_id = d.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;

自连接示例：

连接相同的两张表称为自连接。（将一张表看成两张相同的表。当t1和t2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行多表连接查询。）

SELECT t1.employee_id, t1.last_name, t2.employee_id manager_id, t2.last_name manager_name
FROM employees t1,employees t2
WHERE t1.manager_id = t2.employee_id

非自连接示例：

连接不同的两张或多张表，称为非自连接。

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

扩展：

• 多个表中有相同列时，必须在列名之前加上表名前缀。
• 在不同表中具有相同列名的列可以用 表名 加以区分。
• 使用别名可以简化查询。 列名前使用表名前缀可以提高查询效率。需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替， 不能使用原有的表名，否则就会报错。