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

最新推荐文章于 2025-04-07 20:58:30 发布

原创最新推荐文章于 2025-04-07 20:58:30 发布 · 337 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

MySQL

博客介绍了 SQL 数据库中 INNER JOIN（内连接）、LEFT JOIN（左连接）和 RIGHT JOIN（右连接）三种连接方式。内连接会过滤不满足 ON 条件的数据；左连接保留左边表数据，右边不满足条件显示 NULL；右连接保留右边表数据，左边不满足条件显示 NULL。

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

用户表
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 |
+------+----------+-----------+------+-----------+

标签

#sql #mysql #数据库