mysql join

最新推荐文章于 2023-07-02 00:51:03 发布

yushiyaogg

最新推荐文章于 2023-07-02 00:51:03 发布

阅读量426

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/yushiyaogg/article/details/31404397

版权

Mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1.join，left jioin， right join， inner join，full join区别

join的默认方法为inner join

关于inner join， left join， right join， full join的区别举个例子就清楚了

首先有两个表a和b

select * from a;

+----+-----------+
| ID | aNum |
+----+-----------+
| 1 | a20050111 |
| 2 | a20050112 |
| 3 | a20050113 |
| 4 | a20050114 |
| 5 | a20050115 |
+----+-----------+

select * from b;

+----+-------------+
| ID | bName |
+----+-------------+
| 1 | 2006032401 |
| 2 | 2006032402 |
| 3 | 2006032403 |
| 4 | 2006032404 |
| 8 | 2006032408 |
+----+-------------+

left join的情况

select * from a left join b on a.ID=b.ID;

+----+-----------+------+-------------+
| ID | aNum | ID | bName |
+----+-----------+------+-------------+
| 1 | a20050111 | 1 | 2006032401 |
| 2 | a20050112 | 2 | 2006032402 |
| 3 | a20050113 | 3 | 2006032403 |
| 4 | a20050114 | 4 | 2006032404 |
| 5 | a20050115 | NULL | NULL |
+----+-----------+------+-------------+

right join的情况

select * from a right join b on a.ID=b.ID;

+------+-----------+----+-------------+
| ID | aNum | ID | bName |
+------+-----------+----+-------------+
| 1 | a20050111 | 1 | 2006032401 |
| 2 | a20050112 | 2 | 2006032402 |
| 3 | a20050113 | 3 | 2006032403 |
| 4 | a20050114 | 4 | 2006032404 |
| NULL | NULL | 8 | 2006032408 |
+------+-----------+----+-------------+

join（inner join）的情况

+----+-----------+----+-------------+
| ID | aNum | ID | bName |
+----+-----------+----+-------------+
| 1 | a20050111 | 1 | 2006032401 |
| 2 | a20050112 | 2 | 2006032402 |
| 3 | a20050113 | 3 | 2006032403 |
| 4 | a20050114 | 4 | 2006032404 |
+----+-----------+----+-------------+

full join（因为版本不支持full join，使用union实现了full join操作）

select * from a left join b on a.ID=b.ID union select * from a right join b on a.ID=b.ID;

+------+-----------+------+-------------+
| ID | aNum | ID | bName |
+------+-----------+------+-------------+
| 1 | a20050111 | 1 | 2006032401 |
| 2 | a20050112 | 2 | 2006032402 |
| 3 | a20050113 | 3 | 2006032403 |
| 4 | a20050114 | 4 | 2006032404 |
| 5 | a20050115 | NULL | NULL |
| NULL | NULL | 8 | 2006032408 |
+------+-----------+------+-------------+

我们可以发现inner join是只显示a.ID=b.ID的部分。

left join除了显示a.ID=b.ID的部分，还显示了a.ID中有但是b表中没有与之对应的项目

right join反之，除了显示a.ID=b.ID的部分，还显示了b.ID中有而a表中没有与之对应的项目

full join中，显示没有对应的所有项目