MySQL 08 连接

1 连接

使用 MySQL 的 JOIN 在两个或多个表中查询数据。

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

1）INNER JOIN

mysql> select * from tb1;
+----+--------------+----------+------------+
| id | title        | author   | date       |
+----+--------------+----------+------------+
|  1 | 学习mysql    | lihaogn  | 2018-07-13 |
|  2 | 学习java     | zhangsan | 2018-06-30 |
|  3 | 学习scala    | wanger   | NULL       |
|  4 | 学习python   | caliu    | 2018-08-29 |
|  5 | 学习spark    | wusan    | 2018-08-29 |
|  6 | 学习hadoop   | gaowu    | 2018-08-29 |
+----+--------------+----------+------------+
6 rows in set (0.00 sec)

mysql> select * from count_tb1;
+----------+-------+
| author   | count |
+----------+-------+
| zhangsan |    10 |
| lisi     |    20 |
| wanger   |    30 |
| lisi     |    50 |
+----------+-------+
4 rows in set (0.00 sec)

// 读取两张表中对应的字段
mysql> SELECT a.id,a.author,b.count FROM tb1 a INNER JOIN count_tb1 b ON a.author=b.author;
+----+----------+-------+
| id | author   | count |
+----+----------+-------+
|  2 | zhangsan |    10 |
|  3 | wanger   |    30 |
+----+----------+-------+
2 rows in set (0.00 sec)

// 上述语句等价于
SELECT a.id,a.author,b.count FROM tb1 a, count_tb1 b where a.author=b.author;

2）LEFT JOIN

// LEFT JOIN：读取左表所有选择的字段数据，即使右表没有对应的字段值
mysql> SELECT a.id,a.author,b.count FROM tb1 a LEFT JOIN count_tb1 b ON a.author=b.author;
+----+----------+-------+
| id | author   | count |
+----+----------+-------+
|  2 | zhangsan |    10 |
|  3 | wanger   |    30 |
|  1 | lihaogn  |  NULL |
|  4 | caliu    |  NULL |
|  5 | wusan    |  NULL |
|  6 | gaowu    |  NULL |
+----+----------+-------+
6 rows in set (0.01 sec)

3）RIGHT JOIN

// RIGHT JOIN:读取右表所有对应的字段数据，即使左表没有对应的字段值
mysql> SELECT a.id,a.author,b.count FROM tb1 a RIGHT JOIN count_tb1 b ON a.author=b.author;
+------+----------+-------+
| id   | author   | count |
+------+----------+-------+
|    2 | zhangsan |    10 |
|    3 | wanger   |    30 |
| NULL | NULL     |    20 |
| NULL | NULL     |    50 |
+------+----------+-------+
4 rows in set (0.00 sec)