MySQL中的多表查询①_为什么要进行多表查询

为什么使用多表查询

使用多表查询的目的肯定是因为你要的数据在多张表里面。有人可能会单独去查询每张表，很显然那样效率是很低的。还有人会说为什么要把数据放在多张表里面呢，放在一张里面不行吗？可以，但是会有缺点：

• 会出现很多冗余的数据
• 冗余的数据会造成内存的过度消耗
• 冗余的数据会造成IO的次数变多，从而减少了效率
• 不方便维护

使用多表查询的前提

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

笛卡尔积的错误

想要理解笛卡尔积的错误，那么首先我们就要知道什么是笛卡尔积
笛卡尔乘积 是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

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

例如：
已知我们有107员工，27个部门

多表的相关数据：

出现笛卡尔积的错误

#错误的实现方式1：每个员工都与每个部门匹配了一遍。
SELECT employee_id,department_name
FROM employees,departments;  #查询出2889条记录

#错误的方式2
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;#查询出2889条记录
1
2
3
4
5
6
7
8
9

很明显这是一个笛卡尔积的错误：107*27 = 2889.

笛卡尔积的错误发生条件：

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

为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

标准的代码实现：

SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;
1
2
3

注意点：
①如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表
②从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。
③可以给表起别名，在SELECT和WHERE中使用表的别名。在FROM中命令别名（易知FROM语句是最先执行的）
④如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
⑤如果有n个表实现多表的查询，则需要至少n-1个连接条件

多表查询的分类

角度1：等值连接 vs 非等值连接

角度2：自连接 vs 非自连接

角度3：内连接 vs 外连接

等值连接 vs 非等值连接

非等值连接就是不使用等号去连接
例如：
现有两个表employees，job_grades。前一张表记载了每一位员工的工资，后一张表记载了相应工资区间对应的工资等级。

现在的需求是得到每一位员工的工资等级
代码实现

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`;
1
2
3
4

自连接 vs 非自连接

自连接：很好理解就是自己与自己链接
例子：
在员工表employees中，有如下属性：

我们现在的需求是找出每一位员工对应的主管的id(也就是图中的manager_id)，我们可以知道不论是员工还是他的主管他们肯定是在员工表之内的，从而我们很容易想到使用自连接
代码实现：

SELECT emp.employee_id,emp.last_name,mgr.employee_id `manager_id`,mgr.last_name `last_name` 
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;
1
2
3

结果：（部分）

内连接 vs 外连接

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

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

我们可以用两个集合来形象的理解这些概念：




那么我们怎么实现外连接呢？
在SQL92语法中我们可以直接使用"+"，完成外连接。
右外连接 在左边补加号
左外连接 在右边补加号
也就是说哪边是从表哪边补一个加号

注意：

• Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接
• 而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

例如：我们现在有一个需求查询所有的员工的last_name,department_name信息 （已知有的员工没有部门，有的部门没有员工）

很明显这个地方要使用左外连接，因为他要求的是所有的员工！
代码实现：

#MySQL不支持：
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id(+);
1
2
3
4