这篇文章详细解释了MySQL中的join、inner join、left join和right join的用法。通过实例展示了如何在两张表之间进行有效连接，以及如何使用左连接和右连接来处理不存在的数据。文章还包含了一些实用的MySQL命令和查询结果，以帮助读者更好地理解这些概念。
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)
