多表查询，也可以叫做关联查询，指两个或更多张表一起完成查询操作。
前提条件：这些被一起查询的表之间是存在某种关联的（比如一对一，一对多，多对一），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表之间，就是通过部门编号进行关联

一、多表连接案例的举例

1.1 案例说明

image.png
从这多张表中获取数据：
image.png

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

查询出来的结果如下，不难发现结果的数目是不正确的

  1. +-----------+----------------------+
  2. | last_name | department_name      |
  3. +-----------+----------------------+
  4. | King      | Administration       |
  5. | King      | Marketing            |
  6. | King      | Purchasing           |
  7. | King      | Human Resources      |
  8. | King      | Shipping             |
  9. | King      | IT                   |
  10. | King      | Public Relations     |
  11. | King      | Sales                |
  12. | King      | Executive            |
  13. | King      | Finance              |
  14. | King      | Accounting           |
  15. | King      | Treasury             |
  16. ...
  17. | Gietz     | IT Support           |
  18. | Gietz     | NOC                  |
  19. | Gietz     | IT Helpdesk          |
  20. | Gietz     | Government Sales     |
  21. | Gietz     | Retail Sales         |
  22. | Gietz     | Recruiting           |
  23. | Gietz     | Payroll              |
  24. +-----------+----------------------+
  25. 2889 rows in set (0.01 sec)

上述错误，我们称为笛卡尔积(或交叉连接)错误

1.2 笛卡尔积的错误

image.png
相当于employees中的last_name字段和dapartments表中的每一个department_name都做了笛卡尔积运算。导致最后出现了2889条数据

1.3 问题的解决

  • 笛卡尔积的错误会在下面的条件中产生：
    1. 省略多个表的连接条件（或关联条件）
    2. 连接条件（或关联条件）无效 => 相当于没指定条件
    3. 所有表中的行互相连接

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

    1. SELECT    table1.column, table2.column
    2. FROM    table1, table2
    3. WHERE    table1.column1 = table2.column2;  #连接条件

  • 正确的写法：

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

  • 如果在表中有相同列的时候，应该在列名之前添加上表名的前缀

    1. SELECT
    2.   users.id id,
    3.   users.`name` `name`,
    4.   users.role_id,
    5.   roles.`name`
    6. FROM
    7.   t_users users,
    8.   t_roles roles
    9. WHERE
    10.   users.role_id = roles.id;

    二、多表查询的分类

    分为以下三个角度

    1. 等值连接和非等值连接
    2. 自连接和非自连接
    3. 内连接和外连接

1. 等值连接和非等值连接

等值连接：查询的条件是相等的关系
非等值连接：查询的条件是非相等的关系，比如大于，小于等等

2. 自连接和非自连接

自连接：表中有一个字段和自己本身相关联

比如管理者和普通员工都属于员工表，但是普通员工会有一个manage_id字段指向其管理者，这就是典型的自连接

非自连接：表中没有一个字段是和自己本身相关联

3. 内连接和外连接

内连接：结果集中不包含不满足一张表和另一张表不匹配的部分
外连接：结果集中除了满足两张表匹配的部分，还有不满足条件的部分

  • 左外连接：左表中的数据全部保留，右表中能匹配的就正确显示内容，不匹配的则为NULL
  • 右外连接：右表中的数据全部保留，左表中能匹配的就是正确显示数据，不能匹配的就为NULL
  • 满外连接：两张表中的数据都全部保留
    注意：MySQL不支持满外连接**(FULL OUTER JOIN)**查询

SQL99和SQL92：mysql不支持sql92的+来实现外连接，在mysql中，我们使用join on的方式实现外连接，但是在其他的DBMS中，比如Oracle就支持sql92语法

普通内连接

  1. # 内连接：结果集中不包含一个表和另一个表中不匹配的行
  2. # 只取同时满足两边条件的数据
  3. select employee_id,department_name
  4. from employees e,departments d
  5. where e.department_id = d.department_id;

一共会显示106条记录，因为有一位员工还没有分配部门，所以不会被查出来
image.png

join on实现左外连接

使用场景：部分员工可能暂时还未分配到部门

  1. # 左外连接
  2. SELECT employee_id,department_name
  3. FROM employees e LEFT JOIN departments d
  4. ON e.department_id = d.department_id;

image.png

join on实现右外连接

使用场景：有些部门可能刚刚创建，暂时还没有分配员工

  1. # 右外连接
  2. SELECT employee_id,department_name
  3. FROM employees e RIGHT JOIN departments d
  4. ON e.department_id = d.department_id;

image.png

join on实现三张表之间的外连接

  1. # 三张表之间连接
  2. SELECT employee_id,department_name,city
  3. FROM employees e LEFT JOIN departments d
  4. ON e.department_id = d.department_id
  5. LEFT JOIN locations l
  6. ON d.location_id = l.location_id;

image.png
**SQL99**语法下的7种外连接方式

image.png

4. 满外连接

4.1 UNION 合并查询

利用UNION关键字，可以给出多条select语句，并且将多个结果集合并成为一个结果集。但是，合并的时候，两张表对应的列数和数据类型必须相同，并且相互对应。各select语句之间使用union或union all来连接
UNION：返回两个结果集的并集，并去除中间重复的部分
UNION ALL：返回两个结果集的并集，但是不去除重复的部分

UNION ALL查询的时候占用的资源更少。一般来说，如果不需要对数据进行去重，或者查询的时候明确知道不会有重复的数据，那么就优先使用UNION ALL进行查询。

4.2 七种JOIN的实现

image.png

  1. # 中图
  2. SELECT employee_id,department_name
  3. FROM employees e JOIN departments d
  4. ON e.department_id = d.department_id;
  6. # 左上图
  7. SELECT employee_id,department_name
  8. FROM employees e LEFT JOIN departments d
  9. ON e.department_id = d.department_id;
  11. # 右上图
  12. SELECT employee_id,department_name
  13. FROM employees e RIGHT JOIN departments d
  14. ON e.department_id = d.department_id;
  16. # 中左图
  17. SELECT employee_id,department_name
  18. FROM employees e LEFT JOIN departments d
  19. ON e.department_id = d.department_id
  20. WHERE d.department_id IS NULL;
  22. # 中右图
  23. SELECT employee_id,department_name
  24. FROM employees e RIGHT JOIN departments d
  25. ON e.department_id = d.department_id
  26. WHERE e.department_id IS NULL;
  28. # 下左图
  29. SELECT employee_id,department_name
  30. FROM employees e LEFT JOIN departments d
  31. ON e.department_id = d.department_id
  32. UNION ALL
  33. SELECT employee_id,department_name
  34. FROM employees e RIGHT JOIN departments d
  35. ON e.department_id = d.department_id
  36. WHERE e.department_id IS NULL;
  38. # 下右图
  39. SELECT employee_id,department_name
  40. FROM employees e LEFT JOIN departments d
  41. ON e.department_id = d.department_id
  42. WHERE d.department_id IS NULL
  43. UNION ALL
  44. SELECT employee_id,department_name
  45. FROM employees e RIGHT JOIN departments d
  46. ON e.department_id = d.department_id
  47. WHERE e.department_id IS NULL;

5. SQL99的语法新特性

5.1 新特性1: 自然连接

SQL99提供了一些特殊的语法，比如NATURAL JOIN用来表示自然连接。我们把自然连接理解为SQL92中的等值连接。会自动查询两张连接表中所有相同的字段，然后进行等值连接。

  1. # SQL92实现等值连接
  2. SELECT employee_id,last_name,department_name
  3. FROM employees e JOIN departments d
  4. ON e.department_id = d.department_id
  5. AND e.manager_id = d.manager_id
  7. # SQL99使用自然连接实现
  8. # 会找到两张表中所有相同的字段，然后进行等值连接
  9. SELECT employee_id,last_name,department_name
  10. FROM employees e NATURAL JOIN departments d;

优点：语法简单
缺点：封装性强，有的时候并不需要查这么多字段，不能自定义查询某个字段

5.2 新特性2: USING连接

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

  1. SELECT employee_id,last_name,department_name
  2. FROM employees e JOIN departments d
  3. USING (department_id);
  5. # 和下面这种写法是等价的
  6. SELECT employee_id,last_name,department_name
  7. FROM employees e ,departments d
  8. WHERE e.department_id = d.department_id;

注意：超过三张表禁止使用JOIN，因为JOIN相当于嵌套了一层循环，会大大提高查询所需要的时间

三、多表查询的课后练习

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

  1. SELECT
  2.     e.first_name,
  3.     e.last_name,
  4.     e.department_id,
  5.     d.department_name 
  6. FROM
  7.     employees e,
  8.     departments d 
  9. WHERE
  10.     e.department_id = d.department_id;
  12. # 或
  13. SELECT
  14.     e.first_name,
  15.     e.last_name,
  16.     e.department_id,
  17.     d.department_name 
  18. FROM
  19.     employees e
  20.     LEFT JOIN departments d ON e.department_id = d.department_id;

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

  1. SELECT
  2.     job_id,
  3.     location_id 
  4. FROM
  5.     employees e
  6.     JOIN departments d ON e.department_id = d.department_id 
  7.     AND d.department_id = 90;

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

commission_pct奖金率

  1. SELECT
  2.     e.last_name,
  3.     d.department_name,
  4.     d.location_id,
  5.     l.city 
  6. FROM
  7.     employees e
  8.     LEFT JOIN departments d ON e.department_id = d.department_id
  9.     LEFT JOIN locations l ON d.location_id = l.location_id 
  10. WHERE
  11.     e.commission_pct IS NOT NULL;

注意有的员工可能没有分配部门，但是其有奖学金，所以查询的时候需要使用左连接，避免查询的时候漏掉数据
image.png

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

  1. SELECT
  2.     e.last_name,
  3.     e.job_id,
  4.     e.department_id,
  5.     d.department_name,
  6.     l.city 
  7. FROM
  8.     employees e
  9.     JOIN departments d ON e.department_id = d.department_id
  10.     JOIN locations l ON d.location_id = l.location_id 
  11. WHERE
  12.     l.city = 'Toronto';

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

  1. SELECT
  2.     d.department_name,
  3.     l.street_address,
  4.     e.first_name,
  5.     e.last_name,
  6.     e.job_id,
  7.     e.salary,
  8.     j.job_title 
  9. FROM
  10.     employees e
  11.     JOIN departments d ON e.department_id = d.department_id
  12.     JOIN locations l ON d.location_id = l.location_id
  13.     JOIN jobs j ON e.job_id = j.job_id 
  14. WHERE
  15.     d.department_name = 'Executive';

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

  1. SELECT
  2.     e.first_name emp_first_name,
  3.     e.last_name emp_last_name,
  4.     e.employee_id emp_id,
  5.     e.manager_id emp_manag_id,
  6.     m.first_name manag_first_name,
  7.     m.last_name manag_last_name,
  8.     m.employee_id mang_emp_id 
  9. FROM
  10.     employees e
  11.     LEFT JOIN employees m ON e.manager_id = m.employee_id;

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

  1. SELECT
  2.     d.department_id,
  3.     d.department_name 
  4. FROM
  5.     employees e
  6.     RIGHT JOIN departments d ON e.department_id = d.department_id 
  7. WHERE
  8.     e.department_id IS NULL;

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

  1. SELECT
  2.     l.location_id,
  3.     l.city 
  4. FROM
  5.     departments d
  6.     RIGHT JOIN locations l ON d.location_id = l.location_id 
  7. WHERE
  8.     d.location_id IS NULL;

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

  1. SELECT
  2.     * 
  3. FROM
  4.     departments d
  5.     LEFT JOIN employees e ON d.department_id = e.department_id 
  6.     WHERE
  7.     # d.department_name = 'Sales' OR d.department_name = 'IT';
  8.     d.department_name IN ( 'Sales', 'IT' );