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

原创已于 2023-02-09 20:08:29 修改 · 469 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

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

Mysql 专栏收录该内容

1 篇文章

订阅专栏

文章详细解释了在MySQL中如何使用INNERJOIN,LEFTJOIN和RIGHTJOIN进行表之间的数据连接。INNERJOIN返回两个表中匹配的记录，LEFTJOIN返回左表所有记录及右表匹配的记录（无匹配则为NULL），而RIGHTJOIN则反之，返回右表所有记录及左表匹配的记录。

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)