0基础学MySQL数据库—从小白到大牛（5）多表查询

---

一、多表查询

1.1错误的多表查询会引发笛卡尔积错误

SELECT STUDNT_ID,CLASS_NAME
FROM STUDENT,CLASS
错误的连接方式，会导致笛卡尔积错误。

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

• 省略多个表的连接条件
• 连接条件或关联条件无效
• 所有表中的所有行互相连接
• 为了避免笛卡尔积，可以在WHERE加入有效的连接条件

正确的多表查询方式是需要有连接条件的

SELECT STUDNT_ID,CLASS_NAME
FROM STUDENT,CLASS
#两个表的连接条件
WHERE STUDENT.CLASS_ID = CLASS.CLASS_ID;

1.2如果查询语句中出现了多个表中都存在的字段，必须指明此字段所在的表

SELECT STUDENT.STUDNT_ID,CLASS.CLASS_NAME，CLASS.CLASS_ID
FROM STUDENT,CLASS
WHERE STUDENT.CLASS_ID = CLASS.CLASS_ID;

从SQL优化的角度，建议多表查询的时候，每个字段都指明所在的表

1.3如果表名很复杂或者很长，可以把表名取别名

可以在SELECT和WHERE中使用表的别名

SELECT STU.STUDENT_ID,C.CLASS_NAME，C.CLASS_ID
FROM STUDENT STU,CLASS C
WHERE STU.CLASS_ID = C.CLASS_ID;

注意：如果使用了别名，必须使用别名，不可用使用原来的名字

如果N个表实现多表查询的话，则至少需要N-1个连接条件

SELECT STU.STUDENT_ID,C.CLASS_NAME，C.CLASS_ID,CU.CURRICULUM_NAME,CU.CURRICULUM_ID
FROM STUDENT STU,CLASS C,CURRICULUM CU
WHERE STU.CLASS_ID = C.CLASS_ID
AND STU.CURRICULUM_ID = CU.CURRICULUM_ID

二、多表查询分类

• 等值连接 和 非等值连接
• 自连接 和 非自连接
• 内连接 和 外连接（左连接 和 右连接）

2.1等值连接 和 非等值连接

以上举例都是等值连接，接下来举例非等值连接
这是查询学生成绩等级，在成绩等级表（STU_GRADES）并没有可以等值连接的字段名，所以采用非等值连接

SELECT STU.STUDENT_NAME,STU.GRADE,S.GRADE_LEVEL
FROM STUDENT STU,STU_GRADES S
#第一种方式
WHERE STU.GRADE >= S.LOWEST_SAL AND STU.GRADE <= S.HIGHEST_SAL;
#第二种方式
WHERE STU.GRADE BETWEEN S.LOWEST_SAL AND S.HIGHEST_SAL;

2.2自连接和非自连接

自连接就是自我引用，自己引用自己

• 举例
  查询员工的id和姓名及其管理者的id和姓名
  Employee（员工表）Employee_id（id）Employee_name（姓名）

SELECT E1.Employee_id,E1.Employee_name,E2.Employee_id,E2.Employee_name
FROM Employee E1,Employee E2
WHERE E1.Employee_id = E2.Employee_id;

2.3内连接和外链接

• 内连接：合并具有同一列的两个以上的表的行，结果集不包含一个表与另一个表不匹配的行
• 外链接：两个表在连接过程中除了返回满足连接条件的行以外还返回左或右表中不满足条件的行，这种称为左或右外连接，没有匹配行时，结果表中相应的列为空（NULL）
• 如果左外连接，则连接条件中左边的表称为主表，右边的表称为从表
• 如果右外连接，则连接条件中右边的表称为主表，左边的表称为从表

简单来说，外链接就是不满足连接条件也显示

2.3.1外链接的分类

外链接还分为左外链接、右外链接、满外连接

常用的SQL标准
SQL存在不同版本的标准规范，因为不同规范下的表连接操作是有区别的
SQL有两个主要的标准，分别是SQL92和SQL99最重要的SQL标准就是这两个，92的型式简单，但是SQL语句会比较长，可读性较差。99的语法更复杂，但是可读性更强。
SQL92和SQL99是经典的SQL标准，也分别叫做SQL-2和SQL-3标准

• SQL92语法实现外连接，使用+号：
  举例：
  employees(员工表),departments(部门表)
  查询所有员工的ID和部门名称

SELECT employees_ID,departments_NAME
FROM employees E , departments D
WHERE E.departments_ID = D.departments_ID(+);#需要使用左外连接

MySQL不支持SQL92规范的写法
MySQL不支持SQL92规范的写法
MySQL不支持SQL92规范的写法

• SQL99语法中使用JOIN…ON的方式实现多表查询，这种方式也可以解决外连接的问题，MySQL也是支持这个操作的。
  SQL99语法实现内连接

SELECT employees_ID,departments_NAME
FROM employees E INNER JOIN departments D#INNER可以省略
ON E.departments_ID = D.departments_ID;
JOIN ...
ON ...

• JOIN一个表，要申明跟谁有连接条件。
• JOIN…ON…加的越多索引性能越差，尽量不超过3个。
  
• SQL99语法实现内、外、满连接：
  举例：
  employees(员工表),departments(部门表)
  查询所有员工的ID和部门名称

SELECT employees_ID,departments_NAME
FROM employees E LEFT OUTER JOIN departments D #OUTER可以省略
ON E.departments_ID = D.departments_ID;
只需加上LEFT OUTER实现左外连接
只需加上RIGHT OUTER实现右外连接
只需加上FULL OUTER实现满外连接

MySQL不支持FULL OUTER JOIN
MySQL不支持FULL OUTER JOIN
MySQL不支持FULL OUTER JOIN

图片来源：https://www.bilibili.com/video/BV1iq4y1u7vj?p=28&vd_source=850e589f185b2086feb7914361078e19

三、UNION的使用

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

SELECT ... FROM T1
UNION[ALL]#两个结果集重复部分不去重
SELECT ... FROM T2
UNION#两个结果集重复部分去重
SELECT ... FROM T3

如果需要满外连接的时候需要使用UNION

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

四、SQL99语法新特性

4.1自然连接

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

SELECT employees_ID,departments_NAME
FROM employees E JOIN departments D
ON E.departments_ID = D.departments_ID;
JOIN ...
ON ...
在SQL99中可以写成
SELECT employees_ID,departments_NAME
FROM employees E NATURAL JOIN departments D;

但是不够灵活，会强制连接其他的字段

4.2USING

USING与NATURAL JOIN 不同的是，USING指定了具体相同字段的名称，只需要在USING的括号（）填入要指定的字段名字，同时使用上JOIN…USING可以简化JOIN ON等值连接

SELECT employees_ID,departments_NAME
FROM employees E JOIN departments D
USING (departments_id);