第六章：多表查询

第六章：多表查询

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

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

6.1：一个案例引发的多表连接

1. 案例说明

   SELECT employee_id, department_name
   FROM employees, departments;
   

• 分析错误情况

  SELECT * FROM employees;
  #输出107行
  SELECT * FROM departments;
  #输出27行
  SELECT 107*27 FROM dual;
  

  我们把上述多表查询中出现的问题称为：笛卡尔积的错误。

2. 笛卡尔积(或交叉连接)的理解

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

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

   SELECT employee_id, department_name
   FROM employees CROSS JOIN departments;
   

3. 案例分析与问题解决

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

     1. 省略多个表的连接条件(或关联条件)。
     2. 连接条件(或关联条件)无效。
     3. 所有表中的所有行互相连接。

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

   • 加入连接条件后，查询语法

     SELECT employee_id, department_name
     FROM employees, departments
     WHERE employees.department_id = departments.department_id;
     

     注意：

     1. 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。
     2. 从sql优化的角度，建议多表查询，每个字段前都指明所在的表。
     3. 如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名
     4. 如果有n个表实现多表的查询，则需要至少n-1个连接条件。

6.2：多表查询分类讲解

1. 分类1：等值连接 VS 非等值连接

   • 等值连接

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

   • 非等值连接

     SELECT e.last_name, e.salary, j.grade_level
     FROM employees e, job_grades j
     WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;
     

2. 分类2：自连接 VS 非自连接

   • 自连接

     ​ 当table1和table2本质上是同一张表，只是用取别名的方式虚拟两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

     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;
     

   • 非自连接

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

3. 分类3：内连接 VS 外连接

   • 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表比匹配的行

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

   • 外连接：两个表在连接过程中除了返回满足条件的行以外还返回左(或右)表中不满足条件的行，这种连接称为左(或右)外连接。没有匹配的行时，结果表中相应的列为空(NULL)。

     1. 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
     2. 如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。

   • SQL92：使用(+)创建连接

     1. 在SQL 92中采用(+)代表从表所在的位置。即左或右外连接中，(+)表示哪个是从表。

     2. Oracle对SQL 92支持比较好，而MySQL则不支持SQL 92的外连接。

        #左外连接
        SELECT last_name,department_name
        FROM employees ,departments
        WHERE employees.department_id = departments.department_id(+);
        
        #右外连接
        SELECT last_name,department_name
        FROM employees ,departments
        WHERE employees.department_id(+) = departments.department_id;
        

        • 注意：而且在SQL 92中，只有左外连接和右外连接，没有满(或全)连接。
          

6.3：SQL99语法实现多表查询

1. 基本语法

   • 使用JOIN .... ON子句创建连接的语法结构

     SELECT table1.column, table2.column,table3.column
     FROM table1
       JOIN table2 ON table1 和 table2 的连接条件
         JOIN table3 ON table2 和 table3 的连接条件
     

     ​ SQL 99采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰可见。如果你采用SQL 92，可读性就会大打折扣。

   • 语法说明

     1. 可以使用ON子句指定额外的连接条件。
     2. 这个连接条件是与其它条件分开的。
     3. ON子句使语句具有更高的易读性。
     4. 关键字JOIN、INNER JOIN、CROSS JOIN的含义是一样的，都表示内连接。

2. 内连接的实现

   • 语法

     SELECT 字段列表
     FROM A表 INNER JOIN B表
     ON 关联条件
     WHERE 等其他子句;
     

   • 举例

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

3. 外连接的实现

   • 左外连接

     1. 语法

        SELECT 字段列表
        FROM A表 LEFT JOIN B表
        ON 关联条件
        WHERE 等其他子句;
        

     2. 举例

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

   • 右外连接

     1. 语法

        SELECT 字段列表
        FROM A表 RIGHT JOIN B表
        ON 关联条件
        WHERE 等其他子句;
        

     2. 举例

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

   • 满外连接

     1. 满外连接的结果=左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
     2. SQL 99是支持满外连接的。使用FULL JOIN或FULL OUTER JOIN来实现。
     3. 需要注意的是，MySQL不支持FULL JOIN，但是可以用LEFT JOIN UNION RIGHT JOIN代替。

6.4：UNION的使用

​ 合并查询结果利用UNION关键字，可以给出多条SELECT语句，并将他们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且互相对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

1. 语法格式

   SELECT column, ... FROM table1
   UNION [ALL]
   SELECT column, ... FROM table2
   

2. UNION操作符

   UNION操作符返回两个查询的结果集的并集，去除重复记录。
   

3. UNION ALL操作符

   UNION ALL操作符返回两个查询的结果的并集。对于两个结果集的重复部分，不去重。

   注意：执行UNION ALL语句时所需的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
   

6.5：7种SQL JOINS的实现

1. 中图：内连接

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

2. 左上图：左外连接

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

3. 右上图：右外连接

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

4. 左中图

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

5. 右中图

   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;
   

6. 左下图：满外连接

   # 方式一
   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;
   
   # 方式二
   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;
   

7. 右下图

   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;
   

6.6：SQL 99语法新特性

1. 自然连接

   ​ SQL 99在SQL 92的基础上提供了一些特殊语法，比如NATURAL JOIN用来表示自然连接。我们可以把自然连接理解为SQL 92中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

   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;
   

2. USING连接

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

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

   ​ 能看出与自然连接NATURAL JOIN不同的是，USING指定了具体的相同的字段名称，你需要在USING的括号()中填入要指定的同名字段。同时使用JOIN ... USING可以简化JOIN ON的等值连接。