MySQL之连接查询

6. 连接查询

含义： 又称多表查询，多表连接，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象： 表1有m行，表2有n行，结果就会有m*n行
发生的原因： 没有有效的连接条件
如何避免： 添加有效的连接条件 （where之后）

分类：
年代分类：不同的年代sql语言推出了不同的标准

1. SQL92标准
   仅仅支持内连接和部分外连接，在oracle，sqlserver中支持一部分外连接。mysql中不支持外连接
2. SQL99标准 推荐使用
   msyql中支持内连接，外连接(左外，右外)，交叉连接【MySQL中的】
   按功能分类：
3. 内连接
   1. 等值连接
   2. 非等值连接
   3. 自连接
4. 外连接
   1. 左外连接
   2. 右外连接
   3. 全外连接（mysql中不支持）
5. 交叉连接
   下面是SQL92标准的语法：

1.等值连接

就是where后的连接条件 用“=”相连

语法：

select 查询列表

from 表1 as 别名， 表2 as 别名

where 表1.key(列) = 表2.key(列) 这就是连接条件

[and 筛选条件]

[group by 分组字段]

[having 分组后筛选]

[order by 排序字段]

特点：

1. 多表等值连接的结果为多表的交集部分
2. n表连接至少需要n-1个连接条件 需要使用关键字and进行连接
3. 多表的顺序没有要求 就是from后的
4. 一般需要给表起别名 后边方便
5. 可以搭配前面介绍的所有子句

注意：

如果在from后为起了表别名，则查询的字段就不能使用原来的表明区限定（那个表的那个字段）

2.非等值查询

语法：

与等值查询连接类似，唯一不同的地方：

where 非等值的连接条件

where salary between lowest_sal and heighest_sal;

使用一张表的salary去比较另一种表中的lowset_sal字段和heighest_sal字段，哪一行符合条件，显示那一行（就将前面那一张表的这一行和后面这一张表符合的哪一行连接起来）

3.自连接

相当于等值连接，但又只涉及到一张表，就是使用一张查询两次

语法：

与等值查询连接类似，唯一不同的地方：

from 表 as 别名1, 表 as 别名 2

# 查询员工的名字、上级的名字
select e.last_name,m.last_name
from employees e, employees m
where e.manager_id = m.employee_id;

4. sql99语法

syntax：

select 查询列表

from 表1 as 别名 [连接类型]

join 表2 as 别名

on 连接条件

[where 筛选条件]

[group by 分组字段]

按照连接类型分类：

1. 内连接
   inner （可以省略不写）【重点】
2. 外连接
   1. 左外连接 left [outer] 【重点】
   2. 右外连接 right [outer] 【重点】
   3. 全外连接 full [outer] mysql中不支持
3. 交叉连接
   ​ cross

1.内连接

查询涉及表的交集部分

其语法同sql92上只有一点不同，其余部分都是相同的

syntax：

select 查询列表

from 表1 as 别名

inner join 表2 as 别名

on 连接条件

sql92 from后的“,” 被替换为 “连接条件 + join”，“where 连接条件”被变为“on 连接条件”

1.1 等值连接

除了和sql92的语法有变化之外，没有任何变化

多表查询：

select 查询列表

from 表1

inner join 表2 on 表1.列 = 表2.列

inner join 表3 on 表1和表2连接起来的列 = 表3.列

表1和表2连接起来的列 其实就是表2中的列，也可以是表1的列（就是证明三条线平行的原理，a平行b，b平行c所以a和c平行）

查询顺序： 表1和表2连，然后合起来的表 和 表3连

特点：

1. 可以添加排序、分组、筛选
2. inner可以省略
3. 筛选条件放到where后面，连接条件放到on后面，提高分离性，便于阅读
4. inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集部分

1.2非等值连接

和sql92一样（除了语法）

1.3自连接

# 查询员工的名字、上级的名字
select e.last_name,m.last_name
from employees e 
inner join employees m
on e.manager_id = m.employee_id;

2.外连接

应用场景： 用于查询一个表中有，另一个表中没有的记录

主从表： 主表是发起匹配的表，从表是被匹配的表

特点：

1.外连接的查询结果为主表中有的所有记录

​ 如果从表中有和它匹配的（也就是内连接），则显示匹配的值。如果从表中没有和它匹配的值，则显示null。

​ 外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录

2.主从表

​ 左外连接： left join 左边的是主表

​ 右外连接： right join 右边的是主表

3.左外和右外交换两个表的顺序，可以实现同样的效果

2.1左外、右外连接

syntax：

select 查询列表

from A

left [outer] join B

on A.key = B.key

where 从表(B).主键 is null; #此操作为了去掉内连接的结果（就是主表和从表匹配到的结果，甚于的就是没有匹配的结果）

从表.主键 is null

为什么是主键为null： 如果为别的列则可能存在是null的情况，但是主键不存在是null的情况

# 查询男朋友不再男生表中的女神名
# 左外连接
select b.name,bo.*
from beauty b
left outer join boys bo
on b.boyfriend_id = bo.id
where bo.id is null;
#如果left outer join 左边不是主表，则查询为 查询女朋友不在beauty表中的男生名  效果刚好相反

为了达到一样的效果：左外和右外语句仅仅是将主表和从表位置互换而已

2.3全外连接

MySQL中不支持该语法

select b.*,bo.*
from boys bo
full outer join beauty b
on b.boys_id = bo.id

全外连接的查询结果 = 内连接 + 表1中有而表2中没有的数据 + 表2中有而表1中没有的数据

他如果要去掉内连接的查询结果：

where A.key is null or B.key is null;

3.交叉连接

select bo.*,b.*
from boys bo
cross join beauty b;

就是使用sql99语法实现笛卡尔乘积。不加on连接条件。

使用cross join 代替了sql92语法中from 第一个和第二个表分隔的逗号。

所以在sql92语法中就不支持cross交叉连接，因为不使用cross就可以实现笛卡尔乘积现象。

4.sql92和sql99比较

功能： sql99支持的较多

可读性： sql99实现连接条件和筛选条件分离，可读性较高

少年易老学难成，一寸光阴不可轻