mysql join、inner join、left join 、right join 详解

IARNO_

已于 2023-02-09 20:08:29 修改

阅读量390

点赞数 1

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

于 2023-02-09 20:06:48 首次发布

本文链接：https://blog.csdn.net/iarno/article/details/128959671

版权

Mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

join & inner join

理解为“有效连接”，两张表中都有的数据才会显示。

MySQL [test_join]> show tables;
+---------------------+
| Tables_in_test_join |
+---------------------+
| table_A             |
| table_B             |
+---------------------+
2 rows in set (0.00 sec)

MySQL [test_join]> select * from table_A;
+----+--------+
| id | a_name |
+----+--------+
|  1 | aaa    |
|  2 | bbb    |
|  3 | ccc    |
+----+--------+
3 rows in set (0.00 sec)

MySQL [test_join]> select * from table_B;
+----+-------------------------+--------+
| id | aid (table_A表中的主键id) | b_name |
+----+-------------------------+--------+
|  1 |          1              | 111    |
|  2 |          1              | 222    |
|  3 |          2              | 333    |
|  4 |          1              | 444    |
+----+-------------------------+--------+
4 rows in set (0.00 sec)

MySQL [test_join]> select * from table_A A join table_B B on A.id = B.aid;
+----+--------+----+------+--------+
| id | a_name | id | aid  | b_name |
+----+--------+----+------+--------+
|  1 | aaa    |  1 |    1 | 111    |
|  1 | aaa    |  2 |    1 | 222    |
|  2 | bbb    |  3 |    2 | 333    |
|  1 | aaa    |  4 |    1 | 444    |
+----+--------+----+------+--------+
4 rows in set (0.01 sec)

MySQL [test_join]> select * from table_A A inner join table_B B on A.id = B.aid;
+----+--------+----+------+--------+
| id | a_name | id | aid  | b_name |
+----+--------+----+------+--------+
|  1 | aaa    |  1 |    1 | 111    |
|  1 | aaa    |  2 |    1 | 222    |
|  2 | bbb    |  3 |    2 | 333    |
|  1 | aaa    |  4 |    1 | 444    |
+----+--------+----+------+--------+
4 rows in set (0.01 sec)

left join

以左表为主, 出左表全部数据, 右表不存在数据 null 补全。

MySQL [test_join]> select * from table_A A left join table_B B on A.id = B.aid;
+----+--------+------+------+--------+
| id | a_name | id   | aid  | b_name |
+----+--------+------+------+--------+
|  1 | aaa    |    1 |    1 | 111    |
|  1 | aaa    |    2 |    1 | 222    |
|  2 | bbb    |    3 |    2 | 333    |
|  1 | aaa    |    4 |    1 | 444    |
|  3 | ccc    | NULL | NULL | NULL   |
+----+--------+------+------+--------+
5 rows in set (0.00 sec)

right join

以右表为主, 出右表全部数据, 左表不存在数据 null 补全。

MySQL [test_join]> select * from table_B B right join table_A A on B.aid = A.id;
+------+------+--------+----+--------+
| id   | aid  | b_name | id | a_name |
+------+------+--------+----+--------+
|    1 |    1 | 111    |  1 | aaa    |
|    2 |    1 | 222    |  1 | aaa    |
|    3 |    2 | 333    |  2 | bbb    |
|    4 |    1 | 444    |  1 | aaa    |
| NULL | NULL | NULL   |  3 | ccc    |
+------+------+--------+----+--------+
5 rows in set (0.01 sec)