【MySQL】MySQL内连接，左连接，右连接查询

　　

　概念

• inner join（内连接）：获取两个表中字段匹配关系的记录。也就是只会返回共有的内容。
• left join（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• right join（右连接）： 获取右表所有记录，即使左表没有对应匹配的记录。

　

　示例

• 先在数据库中建立两张表student和score，具体内容如下：

　　【student】

mysql> select * from student;
--------------
select * from student
--------------

+----+---------------------+------+-------+------------+-----------+
| id | name                | sex  | birth | department | address   |
+----+---------------------+------+-------+------------+-----------+
|  1 | rooneymara          | f    |  1985 | psychology | american  |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia |
|  3 | ellenpage           | f    |  1987 | music      | canada    |
|  4 | tomholland          | m    |  1996 | cs         | england   |
|  5 | scarlettjohansson   | f    |  1984 | music      | american  |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   |
|  7 | evagreen            | f    |  1980 | math       | france    |
+----+---------------------+------+-------+------------+-----------+
7 rows in set (0.00 sec)

 

　　【score】

mysql> select * from score;
--------------
select * from score
--------------

+----+--------+------------+-------+
| id | stu_id | c_name     | grade |
+----+--------+------------+-------+
|  1 |      1 | psychology |    98 |
|  2 |      1 | music      |    80 |
|  3 |      2 | psychology |    65 |
|  4 |      2 | cs         |    88 |
|  5 |      3 | cs         |    95 |
|  6 |      4 | psychology |    70 |
|  7 |      4 | music      |    92 |
|  8 |      5 | music      |    94 |
|  9 |      6 | psychology |    90 |
| 10 |      6 | cs         |    85 |
| 11 |      8 | music      |    91 |
+----+--------+------------+-------+
11 rows in set (0.00 sec)

　　

• 　内连接

　　查询student表中的所有个人信息及score表中的c_name,grade

mysql> select a.*,c_name,grade from student a join score b on a.id=b.stu_id;
--------------
select a.*,c_name,grade from student a join score b on a.id=b.stu_id
--------------

+----+---------------------+------+-------+------------+-----------+------------+-------+
| id | name                | sex  | birth | department | address   | c_name     | grade |
+----+---------------------+------+-------+------------+-----------+------------+-------+
|  1 | rooneymara          | f    |  1985 | psychology | american  | psychology |    98 |
|  1 | rooneymara          | f    |  1985 | psychology | american  | music      |    80 |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia | psychology |    65 |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia | cs         |    88 |
|  3 | ellenpage           | f    |  1987 | music      | canada    | cs         |    95 |
|  4 | tomholland          | m    |  1996 | cs         | england   | psychology |    70 |
|  4 | tomholland          | m    |  1996 | cs         | england   | music      |    92 |
|  5 | scarlettjohansson   | f    |  1984 | music      | american  | music      |    94 |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   | psychology |    90 |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   | cs         |    85 |
+----+---------------------+------+-------+------------+-----------+------------+-------+
10 rows in set (0.00 sec)

　
　　以上语句等价于：

mysql> select a.*,c_name,grade from student a,score b where a.id=b.stu_id;
--------------
select a.*,c_name,grade from student a,score b where a.id=b.stu_id
--------------

+----+---------------------+------+-------+------------+-----------+------------+-------+
| id | name                | sex  | birth | department | address   | c_name     | grade |
+----+---------------------+------+-------+------------+-----------+------------+-------+
|  1 | rooneymara          | f    |  1985 | psychology | american  | psychology |    98 |
|  1 | rooneymara          | f    |  1985 | psychology | american  | music      |    80 |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia | psychology |    65 |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia | cs         |    88 |
|  3 | ellenpage           | f    |  1987 | music      | canada    | cs         |    95 |
|  4 | tomholland          | m    |  1996 | cs         | england   | psychology |    70 |
|  4 | tomholland          | m    |  1996 | cs         | england   | music      |    92 |
|  5 | scarlettjohansson   | f    |  1984 | music      | american  | music      |    94 |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   | psychology |    90 |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   | cs         |    85 |
+----+---------------------+------+-------+------------+-----------+------------+-------+
10 rows in set (0.00 sec)

　　

• 　左连接

　　student表中id为7的数据，在score中没有对应的内容。所以最后一条查询结果c_name,grade对应内容为null。

mysql> select a.*,c_name,grade from student a left join score b on a.id=b.stu_id;
--------------
select a.*,c_name,grade from student a left join score b on a.id=b.stu_id
--------------

+----+---------------------+------+-------+------------+-----------+------------+-------+
| id | name                | sex  | birth | department | address   | c_name     | grade |
+----+---------------------+------+-------+------------+-----------+------------+-------+
|  1 | rooneymara          | f    |  1985 | psychology | american  | psychology |    98 |
|  1 | rooneymara          | f    |  1985 | psychology | american  | music      |    80 |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia | psychology |    65 |
|  2 | chrishemsworth      | m    |  1983 | cs         | australia | cs         |    88 |
|  3 | ellenpage           | f    |  1987 | music      | canada    | cs         |    95 |
|  4 | tomholland          | m    |  1996 | cs         | england   | psychology |    70 |
|  4 | tomholland          | m    |  1996 | cs         | england   | music      |    92 |
|  5 | scarlettjohansson   | f    |  1984 | music      | american  | music      |    94 |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   | psychology |    90 |
|  6 | benedictcumberbatch | m    |  1976 | psychology | england   | cs         |    85 |
|  7 | evagreen            | f    |  1980 | math       | france    | null       |  null |
+----+---------------------+------+-------+------------+-----------+------------+-------+
11 rows in set (0.00 sec)

　　

• 　右连接

　　score表中id为11的数据，在student中没有对应的内容，所以最后一条查询结果id,name,sex等对应内容为null。

mysql> select a.*,c_name,grade from student a right join score b on a.id=b.stu_id;
--------------
select a.*,c_name,grade from student a right join score b on a.id=b.stu_id
--------------

+------+---------------------+------+-------+------------+-----------+------------+-------+
| id   | name                | sex  | birth | department | address   | c_name     | grade |
+------+---------------------+------+-------+------------+-----------+------------+-------+
|    1 | rooneymara          | f    |  1985 | psychology | american  | psychology |    98 |
|    1 | rooneymara          | f    |  1985 | psychology | american  | music      |    80 |
|    2 | chrishemsworth      | m    |  1983 | cs         | australia | psychology |    65 |
|    2 | chrishemsworth      | m    |  1983 | cs         | australia | cs         |    88 |
|    3 | ellenpage           | f    |  1987 | music      | canada    | cs         |    95 |
|    4 | tomholland          | m    |  1996 | cs         | england   | psychology |    70 |
|    4 | tomholland          | m    |  1996 | cs         | england   | music      |    92 |
|    5 | scarlettjohansson   | f    |  1984 | music      | american  | music      |    94 |
|    6 | benedictcumberbatch | m    |  1976 | psychology | england   | psychology |    90 |
|    6 | benedictcumberbatch | m    |  1976 | psychology | england   | cs         |    85 |
| null | null                | null |  null | null       | null      | music      |    91 |
+------+---------------------+------+-------+------------+-----------+------------+-------+
11 rows in set (0.00 sec)