MySQL 06 章——多表查询

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

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

一、一个案例引发的多表连接

（1）案例说明

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

1. 笛卡尔积是一个数学运算。假设我有两个集合X和Y，那么X和Y的笛卡尔积就是X和Y的所有可能组合，也就是说第一个对象来自于X的的所有可能，第二个对象来自于Y的所有可能。组合的个数即为两个集合中元素个数的乘积
2. SQL92中，笛卡尔积也称为交叉连接，英文是CROSS JOIN。在SQL99中也是使用CROSS JOIN表示交叉连接。它的作用就是把任意表进行连接，即使这两张表不相关。在MySQL如下情况会出现笛卡尔积：

（3）案例分析与问题解决

1. 多表查询的正确方式，需要有连接条件（就是先把表格交叉连接，然后筛选出符合条件的）：
2. 笛卡尔积的错误会在下面条件下产生：
   1. 省略多个表的连接条件（或关联条件）
   2. 连接条件（或关联条件）无效
   3. 所有表中的所有行互相连接
3. 因为employees表和departments表中都有字段department_id，所以这样会导致错误：
4. 如果查询语句中出现了多个表中都存在的字段，则必须指明该字段所在的表。建议：从SQL优化的角度，建议多表查询时，每个字段前都指明其所在的表
5. 可以在FROM中给表起别名，在SELECT和WHERE中使用表的别名
6. 如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名
7. 练习：查询员工的employee_id，last_name，department_name，city
8. 总结：如果有n个表实现多表查询，则至少需要n-1个连接条件

二、多表查询分类讲解

（1）等值连接 VS 非等值连接

1. 非等值连接的例子：

（2）自连接 VS 非自连接

1. 非自连接：不同的表进行的连接操作
2. 自连接的例子（连接的表是同一张表，给同一张表起不同的名字）：

（3）内连接 VS 外连接

1. 内连接：当合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行（比如，有的员工没有被分配部门，他的部门id是null，所以没有出现在结果中）
2. 外连接：当合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行
3. 外连接的分类：（1）左外连接（2）右外连接（3）满外连接
4. 左外连接：两个表在连接过程中，除了返回满足连接条件的行以外，还返回左表中不满足条件的行，这种连接称为左外连接。如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表
5. 右外连接：两个表在连接过程中，除了返回满足连接条件的行以外，还返回右表中不满足条件的行，这种连接称为右外连接。如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表
6. SQL92语法实现内连接的方式见上（看一中的（2）），略；SQL92语法实现外连接的方式：使用+，但是MySQL不支持SQL92中外连接的写法。因为左边的数据更多，所以+写在右边，用以补齐

三、SQL99语法实现多表查询

1. SQL99语法实现内连接（JOIN前面可以省略INNER）：
2. SQL99语法实现外连接（必须标明左外连接还是右外连接）：
3. 上面这个例子中，如果是左外连接：那么有的员工没有被分配部门，他的部门id就为null，因为是左外连接，所以他被保留。如果是右外连接：那么有的部门没有员工，即它的部门id没有在员工表中出现过，因为是右外连接，所以它被保留
4. 满外连接（MySQL不支持FULL OUTER JOIN）

四、UNION的使用

1. 合并查询结果：利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间，使用UNION或UNION ALL关键字分隔
2. UNION操作符：返回两个查询结果集的并集，去除重复记录
3. UNION ALL操作符：返回两个查询结果集的并集。对于两个结果集的重复部分，不去重
4. 注意：执行UNION ALL语句时，所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复数据，则尽量使用UNION ALL语句，以提高查询数据的效率

五、7种SQL JOINS的实现

1. 内连接的实现：

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

2. 左外连接的实现：

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

3. 右外连接的实现：

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

4. 图一的实现：

   SELECT e.employee_id,d.department_name
   FROM employees e LEFT OUTER JOIN departments d
   ON e.department_id = d.department_id
   WHERE e.department_id IS NULL;

5. 图二的实现：

   SELECT e.employee_id,d.department_name
   FROM employees e RIGHT OUTER JOIN departments d
   ON e.department_id = d.department_id
   WHERE e.employee_id IS NULL;

6. 图三的实现：

   SELECT employee_id,department_name
   FROM employees e LEFT OUTER JOIN departments d
   ON e.department_id = d.department_id
   WHERE e.department_id IS NULLUNION ALLSELECT employee_id,department_name
   FROM employees e RIGHT OUTER JOIN departments d
   ON e.department_id = d.department_id
   WHERE e.employee_id IS NULL;

7. 满外连接的实现：

   SELECT employee_id,department_name
   FROM employees e LEFT OUTER JOIN departments d
   ON e.department_id = d.department_idUNION ALLSELECT employee_id,department_name
   FROM employees e RIGHT OUTER JOIN departments d
   ON e.department_id = d.department_id
   WHERE e.employee_id IS NULL;

六、SQL99语法新特性

（1）自然连接

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

3. 在SQL99中：

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

（2）USING连接

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

七、课后练习

1. 显示所有员工的姓名、部门号和部门名称

   #显示所有员工的姓名、部门号和部门名称
   SELECT e.last_name,e.department_id,d.department_name
   FROM employees e LEFT OUTER JOIN departments d
   ON e.department_id = d.department_id;

2. 查询90号部门员工的job_id和90号部门员工的location_id

   #查询90号部门员工的job_id和90号部门员工的location_id
   SELECT e.job_id,d.location_id
   FROM employees e JOIN departments d
   ON e.department_id = d.department_id
   WHERE e.department_id = 90;

3. 选择所有有奖金的员工的 last_name , department_name , location_id , city

   #选择所有有奖金的员工的 last_name , department_name , location_id , city
   SELECT e.last_name,d.department_name,l.location_id,l.city
   FROM employees e LEFT OUTER JOIN departments d
   ON e.department_id = d.department_id
   LEFT OUTER JOIN locations l
   ON d.location_id = l.location_id
   WHERE e.commission_pct IS NOT NULL;

4. 选择city在Toronto的员工的last_name , job_id , department_id , department_name

   #选择city在Toronto的员工的last_name , job_id , department_id , department_name
   SELECT e.last_name,e.job_id,d.department_id,d.department_id
   FROM employees e JOIN departments d
   ON e.department_id = d.department_id
   JOIN locations l
   ON d.location_id = l.location_id
   WHERE l.city = 'Toronto';

5. 查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

   #查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
   SELECT e.last_name,e.job_id,e.salary,d.department_name,l.street_address
   FROM employees e LEFT OUTER JOIN departments d
   ON e.department_id = d.department_id
   LEFT OUTER JOIN locations l
   ON d.location_id = l.location_id
   WHERE d.department_name = 'Executive';

6. 选择指定员工的姓名、员工号，以及他的管理者的姓名、员工号

   #选择指定员工的姓名、员工号，以及他的管理者的姓名、员工号
   SELECT emp.last_name "employees",emp.employee_id "Emp#",mgr.last_name "manager",mgr.employee_id "Mgr#"
   FROM employees emp LEFT OUTER JOIN employees mgr
   ON emp.manager_id = mgr.employee_id;

7. 查询哪些部门没有员工

   #查询哪些部门没有员工
   SELECT d.department_id,d.department_name
   FROM employees e RIGHT OUTER JOIN departments d
   ON e.department_id = d.department_id
   WHERE e.employee_id IS NULL;

8. 查询哪个城市没有部门

   #查询哪个城市没有部门
   SELECT l.city
   FROM departments d RIGHT OUTER JOIN locations l
   ON d.location_id = l.location_id
   #总结：where相当于是在on的基础上再筛选
   WHERE d.location_id IS NULL;

9. 查询部门名为 Sales 或 IT 的员工信息

   #查询部门名为 Sales 或 IT 的员工信息
   SELECT e.employee_id,e.last_name,d.department_name
   FROM employees e JOIN departments d
   ON e.department_id = d.department_id
   WHERE d.department_name IN('Sales','IT');

该笔记根据尚硅谷的MySQL课程整理