查询SQL——②from join 表的联结（个人超详细总结）

本文的内容结构：表的联结

一、为什么要用联结？

二、联结的两种写法

三、联结的类型（逻辑联结）cross join →equi join=inner join →outer join(left outer join、right outer join、full outer join)这是满足业务逻辑需求的部分。

四、多表联结的写法（联结两个表、联结三个或以上的表）

五、底层物理联结方式（Nested Loop Join, Merge Join, Hash Join）这是SQL性能优化的部分。

Inner join, Outer join为逻辑连接，即SQL语句中From子句的表的连接方式，我们看到的都是联结后的展示的结果，而其中底层的物理联结过程我们并不清楚。

数据库会根据分析结果采取合适的底层物理连接方式Nested Loop Join, Merge Join, Hash Join。

 

注意：

①子查询只是返回单列结果，不要当成联结

②join、cross join、inner join重点要看后面的语句有没有等值过滤的语句

③等值联结并不是默认是自然联结

④联结经常用到表别名，因为自连接必用，并且也是为了少写表名缩短语句

⑤联结写法逻辑的总结

 

 

 

查询SQL逻辑：

select     单列，多列，聚集函数、窗口函数

from   ...   join ... on ...（本文内容）

where     过滤条件

group by 

order by 

limit 

 

 

一、为什么要用联结？

因为关系表的设计把不同的信息分解成多个表，通过一些共同的值来关联，

我们要用联结来关联查询多个表的数据，才能得到我们要的数据结果。

我们从表中查询数据，实际业务中很少只查单表，

单表是最简单的情况，基本都是要多表查询的。
 

 

二、联结的两种写法：DBMS支持两种格式的写法，简单格式A,B和标准格式join

举例等值联结

简单格式：select ... from A,B where A.a=B.b

标准格式：select ... from A join B on A.a=B.b

where子句和on子句的作用是一样的。

不过开发习惯上on子句上只写等值联结的条件，毕竟on子句就是为了联结条件而存在的。其他的过滤条件则在下面的where子句来写。

where子句本该拿来作为过滤的，但是简单格式只有where子句，所以联结条件只能写在where子句了。

首选的是标准格式join语法，简单格式A,B是被SQL纯正论者所“鄙视”的。

叉联结： from A,B（不加where）等于 A join B（不加on）

内连接（等值联结）：from A,B where A.列a=B.列b 等于 A join B on

很多人会弄不清这两种联结的区别，其实只是两种不同的写法而已，两种写法都支持，并且都要加相同列等值的过滤条件才会是等值联结，没加过滤条件就都是笛卡尔积（叉联结）

有个限制的区别就是，简单格式做不了外连接，只能做叉联结与等值联结，而join可以做外连接。

 

三、联结的类型

cross join →equi join=inner join →outer join(left outer join、right outer join、full outer join)

①叉联结（笛卡尔积）cross join：没有联结关系的条件返回的结果集，结果集为m*n行。

实际基本不会用到，但先从这个说起就好理解等值联结和外联结了。

写法：cross join 或者join或者A,B，并且不加where子句的联结关系过滤条件或者on子句，得到的结果都是没有进行等值匹配的m*n。

②内联结（也就是等值联结）：返回联结条件匹配的结果集

自连接、自然联结都是属于等值联结的情况。

自联结(self join)是两个相同的表联结，必须用到表别名来区分。写法还是join或者inner join也可以。

自然联结(natural join)是去掉重复的列名，我们会有等值联结的列，就会有重复的列。

标准联结返回所有的列，自然联结返回去重的列，这个是个概念，DBMS没有支持这个语法也不会去帮你做这个去重的操作，所以是由你个人去逐个列出来的。

《SQL必知必会》P116说到系统不完成这项工作，由你自己完成。

如果支持这个语法的话，也是要在select *的时候才会看到效果，但一般实际开发中都不用select *的，而是逐个列出自己所需要的列，所以自然联结没什么用到的地方。

亲测在mysql中没有支持natural join这个功能。

③外联结（左外联结、右外联结、全外联结）：有时候需要包含那些没有关联行的非等值条件行。

比如统计每个顾客的订单数，包括那些没有下过订单的顾客，则要用到外联结。

左外联结：保留左边表的所有行，包括那些没有关联上的行。写法：left outer join，也可以写left join。

右外联结：保留右边表的所有行，包括那些没有关联上的行。写法：right outer join，也可以写right join。

全外联结：关联的行+左右两个表不关联的行都保留。写法：full outer join。（Mysql不支持）

注意：mysql、SQLite、Access不支持full outer join的功能，在mysql上尝试过后也是报了错的，全外连接极少用到，可以忽略，理解即可。

 

联结条件一般是等值联结，当然也可以是一些<=的情况或者a列=b列+5min的情况，

按照一定时间段显示累计值就经常用到，A表自连接B表，联结条件为A表当前时间段匹配B表所有小于当前时间段的值的值，以此来聚集得到累计值。

五分钟人数和五分钟金额，逐步累计值经常用到这种联结条件。

根据实际需求而定联结的条件即可，不要被“一定是两个列等值”的思维给束缚了。

 

 

四、联结两个表、联结三个及以上的表的写法

联结两个表的写法：

举例等值联结，若要写外连接，则修改join的类型

简单格式：select ... from A,B where A.a=B.b

标准格式：select ... from A join B on A.a=B.b

 

联结三个表的写法：

举例等值联结，若要写外连接，则修改join的类型

简单格式：

select ... from A,B,C 

where A.a=B.b

and A.a = C.c

 

标准格式：

select ... from A

join B on A.a=B.b

join C on A.a=C.c

错误写法：不可以写成下面这样，会报错

select ... from A

join B join C

on A.a=B.b on A.a=C.c

 

 

 

①注意：

子查询是in子句返回一个单列结果，只能返回单列的值给外层SQL来用，

不需要当成联结来理解，即使用子查询或者联结都能满足需求。

一般来说，联结比子查询要快。

 

②注意：

join、cross join、inner join重点要看后面的语句有没有等值过滤的语句

直接写join、cross join、inner join的话，是没法判断是笛卡尔积还是内连接的，

还是要看后面的语句有没有等值过滤的语句，没有where或者on子句来等值过滤的话，则是笛卡尔积，有的话则是等值联结。

举例说明：

cross join有加where子句的情况

SELECT * 
FROM sales A cross join sales B
where A.Sno = B.Sno

用的是cross join，但是加上了等值条件来过滤，

得到的也是等值联结的结果，而不是笛卡尔积的结果。

inner join没有加on子句的情况

而SELECT * 
FROM sales A inner join sales B

用的是inner join，但是没有加上on子句来作为等值联结的条件，得到的结果就是笛卡尔积

并不是用了inner join就是等值联结的，关键还是要看是否有联结的等值过滤条件。where子句或者on子句都可以。

所以这里join、cross join、inner join其实作用是一样的，区别只在于是否有等值过滤的条件。

直接写join也是可以的，一般写cross join、inner join是为了明确写的SQL的意思。

但是做外联结则不能省略join前面的关键字，一定要写left join、right join、full join，最好写全left outer join、right join、full outer join。

外连接与等值联结最大的区别就是外连接会保留没有等值匹配上的部分，如果一个表作为过滤条件后行数不变的基础表，则要选择外连接。

 

③注意：等值联结并不是默认是自然联结

《SQL必知必会》一书中，第13课P116写到，默认每个内连接都是自然联结，即去掉重复的共同列名的列。

其实不对，经过实测，MYSQL的等值联结inner join... on ...并不去掉重复列。

SELECT * 
FROM sales A inner join sales B
where A.Sno = B.Sno

 

④注意：联结经常用到表别名，

一个原因是为了少写完整表名，可以缩短SQL语句，

另一个原因是自联结时要联结两个相同的表，必须要用表别名来区别开来。必用表别名。

 

⑤联结写法逻辑的总结

首先根据问题需求来选好联结类型，并且清楚语法比如on子句，

然后保证有写联结条件，不然只会得出笛卡尔积

最后就是三个表以上的联结可以以此测试联结结果集，保证查询结果的正确

 

 

Q1：cross join  on A.id = B.id 与 inner join  on A.id = B.id，联结后的结果是否一样？

答案：是一样的，见注意②

Q2：联结多个表的写法？from A,B,C与 join A  joinB join C的写法区别？

答案：见第四部分。

 

 

-------------------------------------------------------我是分割线---------------------------------------------------------

 

五、底层物理联结方式（Nested Loop Join, Merge Join, Hash Join）

这是SQL性能优化的部分。

Inner join, Outer join为逻辑连接，即SQL语句中From子句的表的连接方式，我们看到的都是联结后的展示的结果，而其中物理的联结过程我们并不清楚。

数据库会根据分析结果采取合适的底层物理连接方式Nested Loop Join, Merge Join, Hash Join。

参考链接：https://blog.youkuaiyun.com/iteye_15147/article/details/82577151

https://www.cnblogs.com/shangyu/p/6055181.html

（以后再总结这部分）