mysql中的几种join 及 full join，自然连接问题

一时失言乱红尘 2023-06-10 08:30 24阅读 0赞

【注意】：1）、Oracle数据库支持full join，mysql是不支持full join的，但仍然可以同过左外连接+ union+右外连接实现  
                  2）、自然连接：通过MySql自己的判断完成连接过程，不需要指定连接条件。MySql会使用表内的，相同的字段，作为连接条件。自然连接分为内外之分。

### **0、初始化SQL语句:** ###

> /\*join 建表语句\*/  
> drop database if exists test;  
> create database test;  
> use test;  
>    
> /\* 左表t1\*/  
> drop table if exists t1;  
> create table t1 (id int not null,name varchar(20));  
> insert into t1 values (1,'t1a');  
> insert into t1 values (2,'t1b');  
> insert into t1 values (3,'t1c');  
> insert into t1 values (4,'t1d');  
> insert into t1 values (5,'t1f');  
>    
> /\* 右表 t2\*/  
> drop table if exists t2;  
> create table t2 (id int not null,name varchar(20));  
> insert into t2 values (2,'t2b');  
> insert into t2 values (3,'t2c');  
> insert into t2 values (4,'t2d');  
> insert into t2 values (5,'t2f');  
> insert into t2 values (6,'t2a');
> 
> select \* from t1  
> id |name |  
> \---|-----|  
> 1  |t1a  |  
> 2  |t1b  |  
> 3  |t1c  |  
> 4  |t1d  |  
> 5  |t1f  |
> 
> select \* from t2  
> id |name |  
> \---|-----|  
> 2  |t2b  |  
> 3  |t2c  |  
> 4  |t2d  |  
> 5  |t2f  |  
> 6  |t2a  |

### 1、笛卡尔积(没有加筛选条件的内连接) ###

**两表关联，把左表的列和右表的列通过笛卡尔积的形式表达出来。**

> select \* from t1 join t2;  
> 或者  
> select \* from t1 inner join t2;  
> 或者  
> select \* from t1, t2;

执行结果如下:

> id |name |id |name |  
> \---|-----|---|-----|  
> 1  |t1a  |2  |t2b  |  
> 2  |t1b  |2  |t2b  |  
> 3  |t1c  |2  |t2b  |  
> 4  |t1d  |2  |t2b  |  
> 5  |t1f  |2  |t2b  |  
> 1  |t1a  |3  |t2c  |  
> 2  |t1b  |3  |t2c  |  
> 3  |t1c  |3  |t2c  |  
> 4  |t1d  |3  |t2c  |  
> 5  |t1f  |3  |t2c  |  
> 1  |t1a  |4  |t2d  |  
> 2  |t1b  |4  |t2d  |  
> 3  |t1c  |4  |t2d  |  
> 4  |t1d  |4  |t2d  |  
> 5  |t1f  |4  |t2d  |  
> 1  |t1a  |5  |t2f  |  
> 2  |t1b  |5  |t2f  |  
> 3  |t1c  |5  |t2f  |  
> 4  |t1d  |5  |t2f  |  
> 5  |t1f  |5  |t2f  |  
> 1  |t1a  |6  |t2a  |  
> 2  |t1b  |6  |t2a  |  
> 3  |t1c  |6  |t2a  |  
> 4  |t1d  |6  |t2a  |  
> 5  |t1f  |6  |t2a  |

### 2、左连接 ###

两表关联，左表全部保留，右表关联不上用null表示。

![20191026195807394.png][]

> select \* from t1 left join t2 on t1.id = t2.id;  
> id |name |id |name |  
> \---|-----|---|-----|  
> 2  |t1b  |2  |t2b  |  
> 3  |t1c  |3  |t2c  |  
> 4  |t1d  |4  |t2d  |  
> 5  |t1f  |5  |t2f  |  
> 1  |t1a  |   |     |

### 3、右连接 ###

右表全部保留，左表关联不上的用null表示。

![201910261959128.png][]

> select \* from t1 right join t2 on t1.id =t2.id;  
> id |name |id |name |  
> \---|-----|---|-----|  
> 2  |t1b  |2  |t2b  |  
> 3  |t1c  |3  |t2c  |  
> 4  |t1d  |4  |t2d  |  
> 5  |t1f  |5  |t2f  |  
>    |     |6  |t2a  |

### 4、内连接 ###

![20191026201133241.png][]

两表关联，保留两表中交集的记录。

>  select \* from t1 inner join t2 on t1.id = t2.id;  
> id |name |id |name |  
> \---|-----|---|-----|  
> 2  |t1b  |2  |t2b  |  
> 3  |t1c  |3  |t2c  |  
> 4  |t1d  |4  |t2d  |  
> 5  |t1f  |5  |t2f  |

### 5、左表独有 ###

![20191026201118338.png][]

两表关联，查询左表独有的数据。

> select \* from t1 left join t2 on t1.id = t2.id where t2.id is null;  
> id |name |id |name |  
> \---|-----|---|-----|  
> 1  |t1a  |   |     |

### 6、右表独有 ###

![20191026201101625.png][]

两表关联，查询右表独有的数据

> select \* from t1 right join t2 on t1.id = t2.id where t1.id is  null;  
> id |t1\_name |id |t2\_name |  
> \---|--------|---|--------|  
>    |        |6  |t2a     |

### 7、全连接 ###

两表关联，查询它们的所有记录。

![这里写图片描述][SouthEast]

**oracle里面有full join,但是在mysql中没有full join。我们可以使用union来达到目的。**

**报错哈，因为在mysql中没有 full join，所以select \* from t1 full join t2 on t1.id =t2.id 会报错**

> **select \* from t1 left join t2 on t1.id = t2.id  
> union   
> select \* from t1 right join t2 on t1.id = t2.id;**
> 
> **id |name |id |name |  
> \---|-----|---|-----|  
> 2  |t1b  |2  |t2b  |  
> 3  |t1c  |3  |t2c  |  
> 4  |t1d  |4  |t2d  |  
> 5  |t1f  |5  |t2f  |  
> 1  |t1a  |   |     |  
>    |     |6  |t2a  |**

### 8、并集去交集 ###

两表关联，取并集然后去交集。

![20191026201034579.png][]

> select \* from t1 left join t2 on t1.id = t2.id where t2.id is null  
> union   
> select \* from t1 right join t2 on t1.id = t2.id where t1.id is null;
> 
> id |name |id |name |  
> \---|-----|---|-----|  
> 1  |t1a  |   |     |  
>    |     |6  |t2a  |

### 9、自然连接 ###

**通过MySql自己的判断完成连接过程，不需要指定连接条件。MySql会使用表内的，相同的字段，作为连接条件。**

**自然连接分为内外之分。**

**如果两个表有多个相同字段，或者没有相同字段，则自然连接返回为空。**

> select \* from t1  natural join t2;  
> id |name |  
> \---|-----|

修改表的字段名称

>     ALTER TABLE t1 CHANGE COLUMN name t1_name VARCHAR(20) 
>     ALTER TABLE t2 CHANGE COLUMN name t2_name VARCHAR(20)

**自然连接，内连接**

> select \* from t1  natural join t2;  
> id |t1\_name |t2\_name |  
> \---|--------|--------|  
> 2  |t1b     |t2b     |  
> 3  |t1c     |t2c     |  
> 4  |t1d     |t2d     |  
> 5  |t1f     |t2f     |

**自然连接，左连接**

> select \* from t1  natural left join t2;  
> id |t1\_name |t2\_name |  
> \---|--------|--------|  
> 2  |t1b     |t2b     |  
> 3  |t1c     |t2c     |  
> 4  |t1d     |t2d     |  
> 5  |t1f     |t2f     |  
> 1  |t1a     |        |

**自然连接，右连接**

> select \* from t1  natural right join t2;  
> id |t2\_name |t1\_name |  
> \---|--------|--------|  
> 2  |t2b     |t1b     |  
> 3  |t2c     |t1c     |  
> 4  |t2d     |t1d     |  
> 5  |t2f     |t1f     |  
> 6  |t2a     |        |

参考：https://www.cnblogs.com/canger/p/9760217.html  
           https://blog.csdn.net/chenjiard/article/details/79989519

[20191026195807394.png]: /images/20230531/cb8a0b51f8314139a4be92005124c70c.png
[201910261959128.png]: /images/20230531/59c6b439a74e43958a13acf35b638c7c.png
[20191026201133241.png]: /images/20230531/d3b5f5b780794ce290b85b4aeb1f1b98.png
[20191026201118338.png]: /images/20230531/f3f4ee7cfc0e43ae9da3597daaa38efc.png
[20191026201101625.png]: /images/20230531/86f64657572d437f891f0b3cd5041ff5.png
[SouthEast]: /images/20230531/e2ffaaba5c224fb2824a50a8e0555653.png
[20191026201034579.png]: /images/20230531/4b227e33f5cd45abbcf8327a4c9c329c.png