SQL中join语句详解

超、凢脫俗 2023-10-06 13:31 42阅读 0赞

### 1.inner join(内连接) ###

**只返回匹配的行。**  
![内连接][20200723133124271.png]  
**select \* from table\_a a inner join table\_b b on a.name = b.name**

### 2.left join(左外连接) ###

**返回左表的全部数据，和右表中满足on条件的行，如果左表的行在右表中没有匹配的数据，那么这一行中右表对应的数据为null。**  
![左外连接][20200723140519688.png]  
**select \* from table\_a a left join table\_b b on a.name = b.name**

### 3.right join(右外连接) ###

**返回右表中所有的行，和左表中满足on条件的行，如果右表的行在左表中没有匹配，那么这一行中左表的对应数据为null。**  
![右外连接][2020072314135140.png]  
**select \* form table\_a a right join table\_b b on a.name = b.name**

### 4.left join excluding inner join(左连接-内连接) ###

**就是在left join查询中加入一个where条件(b.name is null)，过滤掉A，B表的交集。  
left join 会返回左表的全部，右表中匹配的返回，不匹配的null值填充，本查询刚好过过滤掉右表为null的记录**  
![在这里插入图片描述][20200723142918607.png]  
**select \* from table\_a a left join table\_b b on a.name = b.name where b.name is null**

### 5.right join excluding inner join(右连接-内连接) ###

**就是在right join查询中加入一个where条件(a.name is null)，过滤掉A,B表的交集。  
right join 会返回右表的全部，左表中匹配的返回，不匹配的用null填充，本查询刚好过滤掉左表为null的记录**  
![在这里插入图片描述][20200723234201694.png]  
**select \* from table\_a a right join table\_b b on a.name = b.name where a.name is null**

\*\*

### 注意MySQL不支持，full outer join ###

\*\*

### 6.full outer join(外连接) ###

**会返回左表，右表所有的行，对应表中没有数据以null填充。**  
![在这里插入图片描述][2020072323575262.png]  
**由于mysql数据库并不支持full outer join，所以需要使用left join + left join来代替  
select \* from table\_a a left join table\_b b on a.name = b.name  
union  
select \* from table\_a a right join table\_b b on a.name = b.name**

**7.full outer join excluding inner join(外连接-内连接)**

![在这里插入图片描述][20200723235934613.png]  
**select \* from table\_a a left join table\_b b on a.name = b.name where b.name is null  
union  
select \* form table\_a a right join tablb\_b b on a.name = b.name where a.name is null**

[20200723133124271.png]: https://img-blog.csdnimg.cn/20200723133124271.png
[20200723140519688.png]: https://img-blog.csdnimg.cn/20200723140519688.png
[2020072314135140.png]: https://img-blog.csdnimg.cn/2020072314135140.png
[20200723142918607.png]: https://img-blog.csdnimg.cn/20200723142918607.png
[20200723234201694.png]: https://img-blog.csdnimg.cn/20200723234201694.png
[2020072323575262.png]: https://img-blog.csdnimg.cn/2020072323575262.png
[20200723235934613.png]: https://img-blog.csdnimg.cn/20200723235934613.png