企业官网建设流程全解析

多表查询（关联查询）是指从多个表中获取数据的查询操作，通过表之间的关联关系（一对一、一对多）将数据连接起来。例如员工表和部门表，通过部门编号进行关联。

1、笛卡尔积的理解

案例：查询员工的编号以及部门的名称

SELECT employee_id,department_name FROM employees,departments;

查询结果：出现2889条数据，明显出现错误（每个员工跟所有的部门进行匹配）

错误原因：

• 省略了多个表的连接条件（关联条件）
• 连接条件（关联条件）无效
• 所有表中的所有行互相连接

正确示例：在 WHERE 加入有效的连接条件

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

2、多表查询的讲解

等值连接

（1）区分重复的列名

案例：查询员工的编号、部门的名称以及部门编号

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

说明：department_id在员工表以及部门表中都有，如果没有指明此字段所在的表就会报错

从优化的角度考虑，多表查询时尽量每个查询字段都指明所在的表

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

（2）别名的使用

在上个案例中我们发现查询语句实在是太冗余，尤其是select字段，此时我们可以采用别名

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

• 使用别名可以简化查询。

• 列名前使用表名前缀可以提高查询效率。

！！！注意：如果采用了表的别名，在查询字段（SELECT）、过滤条件（WHERE）中只能采用别名进行替代不能再使用原来的表名，会报错。

（3）练习：查询员工的编号、姓名、部门名称以及城市

SELECT e.employee_id,e.last_name,d.department_name,l.city FROM employees e,departments d,locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

• 多个连接条件采用 AND 操作符
• 连接 n 个表至少需要 n -1个连接条件

非等值连接

案例：查询员工姓名、薪资以及等级

连接条件：员工薪资在某个等级的最低与最高薪资之间（非等值）

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

自连接

相同表之间的自我引用叫做自连接

案例：查询员工编号、员工姓名及其管理者编号与姓名（都在employees表）

SELECT emp.employee_id,emp.last_name,mag.employee_id,mag.last_name FROM employees emp,employees mag WHERE emp.manager_id = mag.employee_id;

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。

然后两个表再进行内连接，外连接等查询。

非自连接

不同表之间的连接操作，以上示例大部分都是非自连接。

内连接

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

也就是严格执行过滤条件，上述案例都是内连接

外连接

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

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

案例：查询所有的员工的姓名以及部门名称

在SQL92标准中使用（+）代表从表的位置，也就是说在左外或右外连接中（+）代表哪个是从表

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

• MySQL 不支持92版本的🔗规则

在SQL99标准中使用JOIN ON子句建立连接，也可以解决内外连接的问题，MySQL 支持该规则。具体规则如下章所示

3、SQL99多表查询

（1）内连接实现

基本语法结构：

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

• 连接条件在 ON 子句之后，具有更佳的易读性
• JOIN、INNER JOIN、CROSS JOIN都表示内连接

示例：查询员工姓名、部门名称以及城市

SELECT e.last_name,d.department_name,l.city FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id;

（2）外连接实现

• 外连接用 OUTER JOIN 实现
• 左外连接（LEFT OUTER JOIN）右外连接（RIGHT OUTER JOIN）全外连接（FULL OUTERR JOIN）
• 基本语法：

#实现查询结果是A SELECT 字段列表 FROM A表 LEFT OUTER JOIN B表 ON 关联条件 WHERE 等其他子句;

案例：查询所有的员工的姓名以及部门名称

# 左外连接 107条 SELECT last_name,department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id; # 右外连接 122条 SELECT last_name,department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

查询结果：107条数据

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
• MySQL 不支持FULL JOIN，可以使用左外连接 UNION 右外连接实现

（3）UNION 的使用

使用 UNION 关键字将查询结果组合成单个结果集，可以考虑 UNION 或者 UNION ALL

• 两个表对应的列数和数据类型必须相同，并且相互对应
• UNION 会执行去重操作，返回两个查询的并集
• UNION ALL 会重复计算交集部分
• 执行UNION ALL语句时所需要的资源比UNION语句少，尽量使用UNION ALL 语句

语法格式：

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

案例：查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男' UNION ALL SELECT id,tname FROM t_usmale WHERE tGender='male';

4、七种SQL JOINS 实现

A---> employees 表 B---> departments 表

图1：内连接

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

图2：左外连接

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

图3：右外连接

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

图4：左外连接-内连接

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

图5：右外连接-内连接

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

图6：满外连接（左外连接）+（右外连接-内连接）

# 123条 SELECT e.employee_id,d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id UNION ALL SELECT e.employee_id,d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.department_id IS NULL;

图7：（左外连接-内连接）+（右外连接-内连接）

# 17条 SELECT e.employee_id,d.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 e.employee_id,d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.department_id IS NULL;

5、SQL99语法新特性

（1）自然连接（NATURAL JOIN）

自然连接类似于等值连接，会自动查询两张表的相同字段，然后进行等值连接。

# SQL92中写法 SELECT e.employee_id,e.last_name,d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id AND e.manager_id = d.manager_id; # SQL99中写法 SELECT e.employee_id,e.last_name,d.department_name FROM employees e NATURAL JOIN departments d;

（2）USING连接

使用 USING 连接指定数据表里的同名字段进行等值连接。

• 只能配合 JOIN 一起使用
• 要求两个关联字段在关联表中名称一致

# 写法一 SELECT e.employee_id,e.last_name,d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; # 写法二 USING SELECT e.employee_id,e.last_name,d.department_name FROM employees e JOIN departments d USING(department_id);