MySQL 内连接、左连接、右连接

最新推荐文章于 2023-11-25 22:40:07 发布

御剑把酒听秋雨丶

最新推荐文章于 2023-11-25 22:40:07 发布

阅读量224

点赞数

分类专栏： MySQL 文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/fweiren/article/details/111318820

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

用户表及部门表结构如下所示：

用户表
mysql> select * from user;
+------+----------+-----------+
| id   | name     | depart_id |
+------+----------+-----------+
|    1 | zhangsan |         1 |
|    2 | lisi     |         1 |
|    3 | wangwu   |         2 |
|    4 | yeqi     |         2 |
|    5 | zhaoxiu  |      NULL |
+------+----------+-----------+

mysql> select * from department;
+------+-----------+
| id   | dname     |
+------+-----------+
|    1 | IT        |
|    2 | sale      |
|    3 | secretary |
+------+-----------+

INNER JOIN（内连接）

不满足 ON 条件的会直接过滤掉，不显示。

mysql> select * from user inner join department on user.depart_id = department.id;
+------+----------+-----------+------+-------+
| id   | name     | depart_id | id   | dname |
+------+----------+-----------+------+-------+
|    1 | zhangsan |         1 |    1 | IT    |
|    2 | lisi     |         1 |    1 | IT    |
|    3 | wangwu   |         2 |    2 | sale  |
|    4 | yeqi     |         2 |    2 | sale  |
+------+----------+-----------+------+-------+

LEFT JOIN（左连接）

不满足 ON 条件的会保留左边那张表的数据，右边表数据直接显示 NULL。

mysql> select * from user left join department on user.depart_id = department.id;
+------+----------+-----------+------+-------+
| id   | name     | depart_id | id   | dname |
+------+----------+-----------+------+-------+
|    1 | zhangsan |         1 |    1 | IT    |
|    2 | lisi     |         1 |    1 | IT    |
|    3 | wangwu   |         2 |    2 | sale  |
|    4 | yeqi     |         2 |    2 | sale  |
|    5 | zhaoxiu  |      NULL | NULL | NULL  |
+------+----------+-----------+------+-------+

RIGHT JOIN（右连接）

不满足 ON 条件的会保留右边那张表的数据，左边表数据直接显示 NULL。

mysql> select * from user right join department on user.depart_id = department.id;
+------+----------+-----------+------+-----------+
| id   | name     | depart_id | id   | dname     |
+------+----------+-----------+------+-----------+
|    1 | zhangsan |         1 |    1 | IT        |
|    2 | lisi     |         1 |    1 | IT        |
|    3 | wangwu   |         2 |    2 | sale      |
|    4 | yeqi     |         2 |    2 | sale      |
| NULL | NULL     |      NULL |    3 | secretary |
+------+----------+-----------+------+-----------+