06_多表查询

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

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

1.一个案例引发的多表连接

1.1案例说明

#查询员工名为’ Abel’的人在哪个城市工作?
SELECT *
FROM employees
WHERE last_name = ‘Abel’;

SELECT *
FROM departments
WHERE department_id = 80;

SELECT *
FROM locations
WHERE location_id = 2500;

为什么需要多表，合成一张表不行吗？

首先可以合成一张表。为什么不这样做呢？

1.数据冗余

将多个表合成一张表，会有大量的冗余字段。其次对于很多为NULL的字段，会浪费很多空间

2.从IO分析

查询一张表的数据，会将查询到的数据从磁盘中加载到内存中。一次IO加载的数据量是有限的，例如一次IO加载1万个字段；对于小表来说如果一行只有10个字段，一次IO加载的行就会更多；而对于大表来说如果一行有1000个字段一次IO加载的行就会很少。对于大表进行select * 来说就需要更多次IO才可以将表中所有数据加载到内存。效率也就低

3.从维护分析

例如查询操作，原来3张表，对于查询其中一张表，其他2张表就不涉及锁定问题，其他用户要对剩余2张表进行查询就可以随时进行。而如果合并为一张表，一个用户进行查询操作就会将整个表进行锁定，其他用户想进行其他字段的查询就会阻塞。因此多表对于多进程来说效率更高，表的粒度越小，更利于并发

查询结果：

表的查询如何实现?：出现笛卡尔积的错误

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

SELECT*
FROM employees;#107条记录

SELECT 2889 / 107
FROM DUAL;

SELECT *
FROM departments; #27条记录

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

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

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

1.3案例分析与问题解决

• 笛卡尔积的错误会在下面条件下产生:
• • 省略多个表的连接条件(或关联条件)。
• • 连接条件(或关联条件)无效
• • 所有表中的所有行互相连接
• 为了避免笛卡尔积，可以在WHERE加入有效的连接条件。
• • 加入连接条件后，查询语法:

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

• • 在WHERE子句中写入连接条件。

2多表查询分类讲解

分类1:等值连接vs非等值连接

等值连接

#1。熟悉常见的几个表
DESC employees;
DESC departments ;
DEsc locations;

#查询员工名为’ Abel’的人在哪个城市工作?
SELECT *
FROM employees
WHERE last_name = ‘Abel’;

SELECT *
FROM departments
WHERE department_id = 80;

SELECT *
FROM locations
WHERE location_id = 2500;

#2．表的查询如何实现?：出现笛卡尔积的错误

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

SELECT*
FROM employees;#107条记录

SELECT 2889 / 107
FROM DUAL;

SELECT *
FROM departments; #27条记录

#4，如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。
SELECT employee_id, department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;

SELECT employees.employee_id, departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;
#建议:从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。

#5．可以给表起别名，在SELECT和WHERE中使用表的别名。
#如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
SELECT emp.employee_id, dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.department_id=dept.department_id;

#6.如果有n个表实现多表的查询，则需要至少n-1个连接条件
#练习:查询员工的employee_id, last_name , department_name, city
SELECT employee_id,last_name , department_name , city,e.department_id,l.location_id
FROM employees e , departments d,locations l
WHERE e.department_id= d.department_id
AND d.location_id= l.location_id;

非等值连接

分类2:自连接vs非自连接

分类3:内连接vs外连接

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

SQL92:使用(+)创建连接(MySQL不支持）

3.SQL99语法实现多表查询

3.1基本语法

3.2内连接(INNER JOIN)的实现

3.3外连接(OUTER JOIN)的实现

3.3.1左外连接(LEFT OUTER JOIN)

3.3.2右外连接(RIGHT OUTER JOIN)

3.3.3满外连接(FULL OUTER JOIN)（MySQL不支持）

4.UNION的使用

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

5.7种SQLJOINS的实现

5.1代码实现

5.2语法格式小结

6.SQL99语法新特性

6.1自然连接

6.2 USING连接

不适用于自连接

7.章节小结

表连接的约束条件可以有三种方式: WHERE,ON,USING

• WHERE:适用于所有关联查询
• ON:只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
• USING:只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

附录:常用的SQL标准有哪些

在正式开始讲连接表的种类时，我们首先需要知道SQL存在不同版本的标准规范，因为不同规范下的表连接操作是有区别的。

SQL有两个主要的标准，分别是SQL92和 SQL99。92和99代表了标准提出的时间，SQL92就是92年提出的标准规范。当然除了SQL92和sQL99以外，还存在SQL-86、SQL-89、SQL:2003、SQL:2008、SQL:2011和SQL:2016等其他的标准。

这么多标准，到底该学习哪个呢?实际上最重要的SQL标准就是SQL92和SQL99。一般来说SQL92的形式更简单，但是写的SsQL语句会比较长，可读性较差。而SQL99相比于SQL92来说，语法更加复杂，但可读性更强。我们从这两个标准发布的页数也能看出，SQL92的标准有500页，而SQL99标准超过了1000页。实际上从
sQL99之后，很少有人能掌握所有内容，因为确实太多了。就好比我们使用Windows、Linux和Office的时候，很少有人能掌握全部内容一样。我们只需要掌握一些核心的功能，满足日常工作的需求即可。

SQL92和SQL99是经典的SQL标准，也分别叫做SQL-2和SQL-3标准。也正是在这两个标准发布之后，SQL影响力越来越大，甚至超越了数据库领域。现如今SQL已经不仅仅是数据库领域的主流语言，还是信息领域中信息处理的主流语言。在图形检索、图像检索以及语音检索中都能看到SQL语言的使用。

练习