6. 多表查询

笛卡尔积
多表查询的分类
UNION的使用
7种SQL JOINS的实现
- USING连接

多表查询，也称为关联查询，指两个或多个表一起完成查询操作
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键

笛卡尔积

SELECT last_name, department_name FROM employees, departments;
SELECT last_name, department_name FROM employees CROSS JOIN departments;
SELECT last_name, department_name FROM employees INNER JOIN departments;
SELECT last_name, department_name FROM employees JOIN departments;

笛卡尔积也称为交叉连接，英文是CROSS JOIN。它的作用是可以把任意表进行连接，即使这些表不相关。两张表笛卡尔积的行数为两张表的行数的乘积，即两张表的所有行互相连接
笛卡尔积没有任何意义，为了避免避免笛卡尔积，需要在WHERE子句中加入有效的连接条件

多表查询的分类

分类一：等值连接 vs 非等值连接
等值连接：
```
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;
```
说明：
多个表中有相同列名时，必须在列名之前加上表名前缀，用于区分
从SQL优化的角度，建议多表查询时，每个字段前都指明其所在的表
建议对表使用别名以提高查询效率
- 注意：如果使用了表的别名，那么在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则会报错
连接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;

分类二：自连接 vs 非自连接

自连接：

SELECT worker.last_name, manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id

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

分类三：内连接 vs 外连接

内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时，结果表中相应的列为空（NULL）
- 如果是左外连接，则连接条件中左边的表称为主表，右边的表称为从表
- 如果是右外连接，则连接条件中右边的表称为主表，左边的表称为从表
SQL92：使用(+)创建连接
- 在SQL92中，采用(+)指明从表所在的位置
- 在SQL92中，只有左外连接和右外连接，没有满外连接
- Oracle对SQL92支持友好，而MySQL则不支持SQL92的外连接 ```sql 左外连接 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;

<a name="zJrAM"></a>
### SQL99语法实现多表查询
<a name="PsbC5"></a>
#### 基本语法
使用JOIN...ON子句创建连接的语法格式：
```sql
SELECT table1.column, table2.column, table3.column
FROM table1
  JOIN table2 ON table1 和 table2 的连接条件
    JOIN table3 ON table2 和 table3 的连接条件

说明：

可以使用 ON 子句指定额外的连接条件
这个连接条件是与其他条件分开的

ON 子句使语句具有更高的易读性

内连接(INNER JOIN)的实现

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

说明：关键字JOIN、INNER JOIN、CROSS JOIN的含义是一样的，都是表示内连接

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

外连接(OUTER JOIN)的实现

左外连接：

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

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

右外连接：

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

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

满外连接：

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据
SQL99是支持满外连接的，使用FULL JOIN 或 FULL OUTER JOIN来实现
注意：MySQL不支持FULL JOIN，但是可以使用LEFT JOIN UNION RIGHT JOIN实现

UNION的使用
合并查询结果利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分割
```
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
```
UNION：返回两个查询的结果集的并集，去除重复记录
UNION ALL：返回两个查询的结果集的并集，对于两个结果集的重复部分，不去重
注意：执行UNION ALL语句所需要的资源比UNION语句少。如果明确知道合并数据后的结果集不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句
7种SQL JOINS的实现
```sql 中图：内连接 SELECT employee_id, last_name, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

左上图：左外连接 SELECT employee_id, last_name, department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;

右上图：右外连接 SELECT employee_id, last_name, department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

左中图：A - A∩B SELECT employee_id, last_name, department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id WHERE d.department_id IS NULL

右中图：B - A∩B SELECT employee_id, last_name, 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, last_name, 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, last_name, department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

右下图：左中图 + 右中图 SELECT employee_id, last_name, 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, last_name, department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id WHERE e.department_id IS NULL

<a name="pMFnC"></a>
### SQL99语法新特性
<a name="KAtkh"></a>
#### 自然连接
用NATURAL JOIN表示自然连接，它会自动查询两张表中所有相同的字段，然后进行等值连接
```sql
SELECT employee_id, last_name, department_name
FROM employees e NATURAL JOIN departments d;

等价于：
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;

USING连接

使用USING指定数据表里的同名字段进行等值连接，但是只能配合JOIN一起使用

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

等价于
SELECT employee_id, last_name, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

笛卡尔积

多表查询的分类

分类一：等值连接 vs 非等值连接

分类二：自连接 vs 非自连接

分类三：内连接 vs 外连接

内连接(INNER JOIN)的实现

外连接(OUTER JOIN)的实现

UNION的使用

7种SQL JOINS的实现

USING连接