学习笔记 - 数据库 - join 链接查询

join 链接查询

inner join 、left join 、right join 关系和区别

inner join(内链接）只返回两表中链接字段相等的记录

left join(左链接) 返回包括左表中的所有记录和右表中 与左表联结字段相等 的记录.(两表中链接字段相等的记录，以及左表所有剩下的记录)

right join(右链接) 返回包括右表中的所有记录和左表中 与右表联结字段相等 的记录.(两表中链接字段相等的记录，以及左表所有剩下的记录,效果类似和 left join 左右两表交换一样)

现有两表如下：

user:
+----+-------+---------+
| id | name  | addr    |
+----+-------+---------+
|  1 | tom   | street1 |
|  2 | DIVA  | street2 |
|  3 | simis | street2 |
+----+-------+---------+

record:
+----+------+-----------+---------+
| id | msg  | date      | user_id |
+----+------+-----------+---------+
|  1 | bai  | 1233      |       1 |
|  2 | wds  | 132233    |       2 |
|  3 | west | asf132233 |       1 |
|  4 | hfd  | 34fw      |    NULL |
+----+------+-----------+---------+

使用 inner join 连接查询

mysql> select * from user u inner join record r on u.id = r.user_id;

查询结果中，只返回链接字段相等记录：

+----+------+---------+----+------+-----------+---------+
| id | name | addr    | id | msg  | date      | user_id |
+----+------+---------+----+------+-----------+---------+
|  1 | tom  | street1 |  1 | bai  | 1233      |       1 |
|  1 | tom  | street1 |  3 | west | asf132233 |       1 |
|  2 | DIVA | street2 |  2 | wds  | 132233    |       2 |
+----+------+---------+----+------+-----------+---------+

使用 left join：

mysql> select * from user u left join record r on u.id = r.user_id;

查询结果比使用 inner join 多返回了左表 user 的剩余数据记录，该记录和右表链接全为 NULL。

+----+-------+---------+------+------+-----------+---------+
| id | name  | addr    | id   | msg  | date      | user_id |
+----+-------+---------+------+------+-----------+---------+
|  1 | tom   | street1 |    1 | bai  | 1233      |       1 |
|  2 | DIVA  | street2 |    2 | wds  | 132233    |       2 |
|  1 | tom   | street1 |    3 | west | asf132233 |       1 |
|  3 | simis | street2 | NULL | NULL | NULL      |    NULL |
+----+-------+---------+------+------+-----------+---------+

使用 right join:

mysql> select * from user u right join record r on u.id = r.user_id;

查询结果中返回了所有右表记录，并且还有左表中链接字段相等的记录。

+------+------+---------+----+------+-----------+---------+
| id   | name | addr    | id | msg  | date      | user_id |
+------+------+---------+----+------+-----------+---------+
|    1 | tom  | street1 |  1 | bai  | 1233      |       1 |
|    1 | tom  | street1 |  3 | west | asf132233 |       1 |
|    2 | DIVA | street2 |  2 | wds  | 132233    |       2 |
| NULL | NULL | NULL    |  4 | hfd  | 34fw      |    NULL |
+------+------+---------+----+------+-----------+---------+