MySQL多表连接（三）——学习笔记
多表连接
多表查询：当查询的数据并不是来源一个表时，需要使用多表连接操作完成查询。根据不同表中的数据之间的关系查询相关联的数据。
多表连接类型：
-
内连接
连接两个表，通过相等或不等判断连接列，称为内连接。在内连接中典型的链接运算符有：**=或者<>**之类的比较运算符。包括等值连接和自然连接。
- 等值连接：对两个表中的不同列进行相等的判断；
- 非等值连接：除相等判断以外的连接；
- 自连接：一个表对自身关联的连接
- SQL99：交叉连接（CROSS JOIN）——笛卡尔乘积
- SQL99：内连接（INNER JOIN）
- SQL99：自然连接（NATURAL JOIN）
-
外连接
在两个表之间的连接，返回内连接的结果，同时还返回不匹配行的左（或右）表的连接，称为左（或右）连接。返回内连接的结果，同时还返回左或右连接，称为全连接。- 左外连接
- 右外连接
- 全外连接（MYSQL中无）
-
子查询
当一个查询时另一个查询的条件时，称为子查询。
1、笛卡尔乘积
笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为 X*Y，第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其中一个成员。
如何避免笛卡尔乘积
当一个连接条件无效或被遗落时，其结果是一个笛卡尔乘积，其中所有行的组合都被显示——第一个表中的所有行连接第二个表中的所有行。一个笛卡尔乘积会产生大量的行。应该在WHERE字句中始终包含一个有效的连接条件。
如果 X 集合有 n 个数据，Y 集合有 m 个数据，笛卡尔乘积返回的结果有 n*m 个数据。
2、语法结构
使用一个连接从多表中查询数据
SELECT table1.column,table2.column
FROM table1,table2
WHERE table.column1 = table2.column2;
- 在WHERE字句中写连接条件；
- 当多个表中有相同的列名时，将表名或表的别名作为列名的前缀；
2.1 定义连接
当数据从多表中查询时，要使用连接（ join ）条件。一个表中的行按照存在于相应列中的值被连接到另一个表中的行。
2.2 原则
- 在写一个连接表的SELECT 语句时，在列名前面用表名或表的别名可以使语义清楚，并且加快数据库访问；
- 连接 n 个表，最少需要 n-1 个连接条件，避免产生笛卡尔乘积；
2.3 表别名
简化查询语句的长度；有助于保持SQL语句代码较少，因此使用的存储器也少。
使用表别名原则
- 表别名最多可以有30个字符，但短一些更好；
- 如果在 FROM 字句中表别名被用于指定表，那么在整个 SELECT 语句中都可以使用表别名；
- 表别名最好是能够关联表的简称；
- 表别名只对当前 SELECT 语句有效；
3、等值连接
等值连接也被称为简单连接（simple joins）或者内连接（inner joins）——是通过等号来判断连接条件中的数据值是否匹配。
3.1 抉择矩阵（decision matrix）
通过行与列来分析一个查询的方式。
例如：显示同一个部门中所有名字为 Taylor 的雇员的名字和部门名称
SELECT last_name,department_name
from employees e,departments d
where e.department_id = d.department_id
and last_name='Taylor'
对应的抉择矩阵如下：
| 投影列 | 源表 | 条件 |
|---|---|---|
| last_name | employees | last_name = ‘Taylor’ |
| department_name | departments | employees.department_id = department.department_id |
4、 非等值连接
一个非等值连接是一种不使用相等（=）作为连接条件的查询。如：
!=、> 、< 、>= 、<= 、BETWEEN AND等都是非等值连接的条件判断。
5、自连接
表连接表本身的操作称为自连接。
案例：查询每个雇员的经理的名字（雇员和经理在同一个表）
SELECT worker.last_name,manager.last_name
FROM employees worker,employees manager
where worker.manager_id = manager.employee_id;
6、外连接
外连接是指查询符合连接条件的数据同时还包含孤儿数据。
- 左外连接：包含左表的所有满足判断条件的行的数据，同时还包含了右表判断列为空值的数据；
table1 LEFT OUTER JOIN table2 ON(连接条件)； - 右外连接：包含了右表的所有满足判断条件的行的数据，同时还包含了左表判断列为空值的数据；
table1 RIGHT OUTER JOIN table2 ON(连接条件)； - 全外连接：包含两表中满足条件的数据行，同时还包含两表中判断列为空值的数据；
table1 FULL OUTER JOIN table2 ON(连接条件)；
孤儿数据——被连接列的值为空的数据。
外连接的判断条件使用关键字 ON ！！！
7、交叉连接
-
CROSS JOIN字句导致两个表的交叉乘积； - 该连接和两个表之间的笛卡尔乘积是一样的；
SELECT last_name,department_name
from employees cross join department;
8、自然连接（NATURAL JOIN）
-
NATURAL JOIN子句基于两个表之间有相同名字的所有列； - 它从两个表中选择在所有的匹配列中有相等值的行；
- 如果有相同名字的列的数据类型不同，返回一个错误；
注意：
- 如果做自然连接的两个表中有多个字段满足相同名称和类型，它们会作为自然连接的条件。并且相等于
相等列判断1 and 相等列判断2 ...； - 自然连接与等值连接相同，简化了等值连接的写法，但在性能上并无差异；
- 使用自然连接时，相同列名的数据类型必须相同；
9、USING使用
- 当有多个列匹配时，用 USING 子句匹配唯一的列；
- 如果某列在USING中使用，那在引用该列时不能使用表名或别名作为前缀；
- NATURAL JOIN 和 USING 子句是相互排斥的；
select d.department_id,l.city
from department t join locations l
Using(location_id)
where location_id = 1800;
| NATURAL JOIN | USING |
|---|---|
| 使用NATURAL JOIN连接 | 用JOIN连接 |
| 表中存在多个相同列(包含类型)，是以AND连接匹配列 | 在USING后用括号来指定匹配列，用逗号分隔 |
| 可以使用表名或别名前缀 | 匹配列不能使用表名或别名为前缀 |
| 使用where关键字 | 使用where关键字 |
10、内连接（INNER JOIN）
内连接（INNER JOIN）：内连接通过INNER JOIN来建立两个表的连接。在内连接中使用INNER JOIN 作为表的连接，用ON子句给定连接条件。INNER JOIN 语句在性能上与其他语句没有优势。
- 可以等值连接、非等值连接；
- 每两个表连接用 ON 作为连接条件，WHERE作为判断条件；
- 可以试用 USING ，此时是等值连接；
--查询雇员id为202的雇员名字、部门名称，以及工作城市
--（1）等值连接
select 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 e.employee_id = 202;
--（2）内连接
select e.last_name,d.department_name,l.city
from employees e inner join departments d
on e.department_id = d.department_id
inner join locations l
on d.location_id = l.location_id
where e.employee_id = 202;
--（3）内连接使用USING
select e.last_name,d.department_name,l.city
from employees e inner join departments d
using(department_id) inner join locations l
using(location_id)
where e.employee_id = 202;