- 连接查询:将多张表(可以大于2张,一张表可以同时作为多张表来使用)进行记录的连接:按照某个指定的条件进行数据的拼接:最终结果是:记录数有可能变化,字段数基本上是一定会增加:至少是多张表的字段的合并
连接查询的意义
:在用户查看数据的时候,需要显示的数据来自多张表- 连接查询:join:左表 join 右表
- 左表:在join关键字左边的表
- 右表:在join关键字右边的表
- SQL中将连接查询分为4类:内连接、外连接、自然连接和交叉连接
交叉连接
- 交叉连接:cross join,从一张表中循环取出每一条记录,每条记录都去与另一张表中的所有记录匹配:匹配结果一定保留且无条件匹配,而连接本身字段就会增加,最终形成的结果叫做:笛卡尔积
SQL标准语法: select */字段列表 from 左表 cross join 右表;
MySQL方言: select */字段列表 from 左表,右表;
- 笛卡尔积没有意义: 应该尽量避免(交叉连接没用):如果在实际项目开发中想使用交叉连接的话,那么一定要带上判断条件去除笛卡尔积
SQL标准语法: select */字段列表 from 左表 cross join 右表 where 条件; -- 去除笛卡尔积
MySQL方言: select */字段列表 from 左表,右表 where 条件; -- 去除笛卡尔
- 交叉连接存在的价值: 保证连接这种结构的完整性
内连接
select */字段列表 from 左表 [inner] join 右表
on 匹配条件
- 内连接:[inner] join,从左表中取出每一条记录,去右表中与所有的记录进行匹配:匹配必须是某个条件在左表中与右表中相同最终才会保留结果:否则不保留结果
(1) 左表中的1条记录匹配到右表中的N条记录:保留N条记录
(2) 左表中的1条记录在右表中没有匹配到任何记录:不保留结果 - 基本语法:
select */字段列表 from 左表 [inner] join 右表 on 左表.字段 运算符 右表.字段
:其中on表示条件:条件字段就是代表相同业务含义 - 字段别名以及表别名的使用:在查询数据的时候,不同表有同名字段,这个时候需要加上表名称才能区分:但是有时表名称太长,通常可以使用表别名
- 内连接可以没有连接条件:即没有on之后的内容,这个时候系统会保留所有的结果,此时效果与交叉连接(cross join)是一样的:存在笛卡尔积!
- 带where条件的交叉连接等价于内连接:交叉连接的匹配条件只能使用where不能使用on
- 内连接可以使用where代替on关键字:但是where没有on的效率高
外连接
- 外连接:outer join:以某种表(
主表
:左表或右表)为主,取出里面的所有记录,然后每条记录与另外一种表(子表
:右表或左表)进行连接查询:不管能不能匹配上条件最终都会保留结果:
(1) 主表中的1条记录能够匹配上子表中N条记录:保留该N条记录
(2) 主表中的1条记录在子表中没有匹配上任何记录:此时会生成1条记录
, 保留主表记录,子表相关字段全部置为NULL - 也就是说主表的一条记录没有匹配到字表中的任何记录的话,那么也会保留一条记录:主表字段值+子表字段值全部为NULL:能匹配的的全部保留;一条记录都匹配不上的,主表字段保留,子表字段都置空NULL
- 外连接分为两种:主表 vs 子表
(1) 左表 left join 右表:左外连接或左连接:左表为主表,右表为子表
(2) 左表 right join 右表:右外连接或右连接:左表为子表,右表为主表 - 基本语法
select */字段列表 from 左表 left/right [outer] join 右表
on 匹配条件
- 外连接的匹配条件只能使用on不能使用where来代替on
- 虽然左连接与右连接的主表有差异,但是查询出来的结果集的记录中的字段的顺序值左表字段在左侧,右表字段在右侧:与谁是主表无关
左连接
右连接
自然连接
- 在实际开发自然连接基本上是不会使用的
- 自然连接:natural join:就是自动匹配连接条件:系统以字段名称作为匹配模式:即同名字段就作为连接条件,多个同名字段都作为连接条件
- 自然连接分为自然内连接与自然外连接
- 自然内连接:左表 natural join 右表
- 自然内连接的语法只能是:左表 natural join 右表:不能是左表 natural inner join 右表
- 自然外连接分为:自然左连接 vs 自然右连接:在外连接的基础上使用了默认的连接条件:而外连接必须显示给出连接条件
- 自然左连接:左表 natural left [outer] join 右表;
- 自然右连接:左表 natural right [outer] join 右表;
- 其实, 内连接和外连接都可以模拟自然连接: 使用同名字段,合并字段
左表 left/right/inner join 右表 using(字段名); – 使用同名字段作为连接条件: 自动合并条件 - 多表连接: A表 inner join B表 on 条件 left join C表 on条件 …:执行顺序: A表内连接B表,得到一个二维表, 左连接C表形成二维表…
连接查询总结
1. 交叉连接:原则上交叉连接是没有连接条件的 ==> 导致笛卡尔积
基本语法:左表 cross join 右表 <==> 左表,右表
应用场景:交叉连接搭配连接条件where ⇒ select */字段列表 from 表1,表2,... where 条件;
2.内连接:原则上是要给出连接条件的:如果没有连接条件==>导致笛卡尔积
基本语法:左表 [inner] join 左表 on/where 连接条件;
多表内连接:表1 join 表2 on/where 连接条件 join 表3 on/where 连接条件 ...
3. 外连接:原则上是要给出连接条件的:如果没有连接条件==>导致笛卡尔积
基本语法:左表 left/right [outer] join 右表 on 连接条件;
多表外连接:表1 left/right [outer] join 表2 on 连接条件
left/right [outer] join 表3 on 连接条件 ......
4. 自然连接:自动根据同名字段使用连接条件
自然内连接:左表 natural join 右表
自然外连接:左表 natural left/rgiht [outer] join 右表
- 使用MySQL在实际开发中使用频率较高的交叉连接:
(1) 带连接条件的交叉连接:去除笛卡尔积
(2) 内连接
(3) 外连接