MySQL中join用法解析

灰太狼 2022-07-19 00:28 215阅读 0赞

# MySQL中join用法解析 #

实例数据库如下：  
student表：

mysql> select * from student;
    +-----------+-----------+------+------+-------+
    | Sno       | Sname     | Ssex | Sage | Sdept |
    +-----------+-----------+------+------+-------+
    | 201215121 | 李勇      | 男   |   22 | CS    |
    | 201215122 | 刘晨      | 女   |   19 | CS    |
    | 201215123 | 王敏      | 女   |   18 | MA    |
    | 201215125 | 张立      | 男   |   19 | IS    |
    | 201215128 | 陈冬      | 男   |   18 | IS    |
    | 201215126 | 张成民    | 男   |   18 | CS    |
    +-----------+-----------+------+------+-------+
    6 rows in set (0.00 sec)

sc表：

mysql> select * from sc;
    +-----------+------+-------+
    | Sno       | Cno  | Grade |
    +-----------+------+-------+
    | 201215121 |    1 |    92 |
    | 201215121 |    2 |    85 |
    | 201215121 |    3 |    88 |
    | 201215122 |    2 |    90 |
    | 201215122 |    3 |    80 |
    | 201215128 |    1 |    78 |
    +-----------+------+-------+
    6 rows in set (0.00 sec)

## LEFT JOIN（左连接） ##

![这里写图片描述][20160807132513755]  
sql语句如下：

select * from student left join sc on student.Sno=sc.Sno;

运行结果如下：

+-----------+-----------+------+------+-------+-----------+------+-------+
    | Sno       | Sname     | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
    +-----------+-----------+------+------+-------+-----------+------+-------+
    | 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    1 |    92 |
    | 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    2 |    85 |
    | 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    3 |    88 |
    | 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    2 |    90 |
    | 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    3 |    80 |
    | 201215128 | 陈冬      | 男   |   18 | IS    | 201215128 |    1 |    78 |
    | 201215123 | 王敏      | 女   |   18 | MA    |      NULL | NULL |  NULL |
    | 201215125 | 张立      | 男   |   19 | IS    |      NULL | NULL |  NULL |
    | 201215126 | 张成民    | 男   |   18 | CS    |      NULL | NULL |  NULL |
    +-----------+-----------+------+------+-------+-----------+------+-------+

在此例中left join 是以student表中的记录为基础，student表可以看成左表，sc表可以看成右表，左表中的记录会完全显示出来，加上匹配到的右表，如果左边没有匹配到，则其余部分显示为null。

## USING字句 ##

using字句和on字句，类似，但结果略有不同。  
例如：

mysql> select student.Sno,Sname,Grade from student left join sc on student.Sno=sc.Sno;
    +-----------+-----------+-------+
    | Sno       | Sname     | Grade |
    +-----------+-----------+-------+
    | 201215121 | 李勇      |    92 |
    | 201215121 | 李勇      |    85 |
    | 201215121 | 李勇      |    88 |
    | 201215122 | 刘晨      |    90 |
    | 201215122 | 刘晨      |    80 |
    | 201215128 | 陈冬      |    78 |
    | 201215123 | 王敏      |  NULL |
    | 201215125 | 张立      |  NULL |
    | 201215126 | 张成民    |  NULL |
    +-----------+-----------+-------+
    9 rows in set (0.00 sec)

以上等价于

select Sno,Sname,Grade from student left join sc using(Sno);
    +-----------+-----------+-------+
    | Sno       | Sname     | Grade |
    +-----------+-----------+-------+
    | 201215121 | 李勇      |    92 |
    | 201215121 | 李勇      |    85 |
    | 201215121 | 李勇      |    88 |
    | 201215122 | 刘晨      |    90 |
    | 201215122 | 刘晨      |    80 |
    | 201215128 | 陈冬      |    78 |
    | 201215123 | 王敏      |  NULL |
    | 201215125 | 张立      |  NULL |
    | 201215126 | 张成民    |  NULL |
    +-----------+-----------+-------+

不同的地方，例如：

select * from student left join sc on student.Sno=sc.Sno;
    +-----------+-----------+------+------+-------+-----------+------+-------+
    | Sno       | Sname     | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
    +-----------+-----------+------+------+-------+-----------+------+-------+
    | 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    1 |    92 |
    | 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    2 |    85 |
    | 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    3 |    88 |
    | 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    2 |    90 |
    | 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    3 |    80 |
    | 201215128 | 陈冬      | 男   |   18 | IS    | 201215128 |    1 |    78 |
    | 201215123 | 王敏      | 女   |   18 | MA    |      NULL | NULL |  NULL |
    | 201215125 | 张立      | 男   |   19 | IS    |      NULL | NULL |  NULL |
    | 201215126 | 张成民    | 男   |   18 | CS    |      NULL | NULL |  NULL |
    +-----------+-----------+------+------+-------+-----------+------+-------+

select * from student left join sc using (sno);
    +-----------+-----------+------+------+-------+------+-------+
    | Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
    +-----------+-----------+------+------+-------+------+-------+
    | 201215121 | 李勇      | 男   |   22 | CS    |    1 |    92 |
    | 201215121 | 李勇      | 男   |   22 | CS    |    2 |    85 |
    | 201215121 | 李勇      | 男   |   22 | CS    |    3 |    88 |
    | 201215122 | 刘晨      | 女   |   19 | CS    |    2 |    90 |
    | 201215122 | 刘晨      | 女   |   19 | CS    |    3 |    80 |
    | 201215128 | 陈冬      | 男   |   18 | IS    |    1 |    78 |
    | 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
    | 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
    | 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
    +-----------+-----------+------+------+-------+------+-------+

重复的Sno列，如果用on字句会被输出两次

## RIGHT JOIN（右连接） ##

同LEFT JOIN，只不过以右表为基础，例如：

select * from student right join sc using (sno);
    +-----------+------+-------+--------+------+------+-------+
    | Sno       | Cno  | Grade | Sname  | Ssex | Sage | Sdept |
    +-----------+------+-------+--------+------+------+-------+
    | 201215121 |    1 |    92 | 李勇   | 男   |   22 | CS    |
    | 201215121 |    2 |    85 | 李勇   | 男   |   22 | CS    |
    | 201215121 |    3 |    88 | 李勇   | 男   |   22 | CS    |
    | 201215122 |    2 |    90 | 刘晨   | 女   |   19 | CS    |
    | 201215122 |    3 |    80 | 刘晨   | 女   |   19 | CS    |
    | 201215128 |    1 |    78 | 陈冬   | 男   |   18 | IS    |
    +-----------+------+-------+--------+------+------+-------+

## INNER JOIN（相等连接或内连接） ##

![这里写图片描述][20160807133304222]

不会显示以谁为基础，只会显示符合条件的记录

select * from student inner join sc on student.Sno=sc.Sno;
    +-----------+--------+------+------+-------+-----------+------+-------+
    | Sno       | Sname  | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
    +-----------+--------+------+------+-------+-----------+------+-------+
    | 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    1 |    92 |
    | 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    2 |    85 |
    | 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    3 |    88 |
    | 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 |    2 |    90 |
    | 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 |    3 |    80 |
    | 201215128 | 陈冬   | 男   |   18 | IS    | 201215128 |    1 |    78 |
    +-----------+--------+------+------+-------+-----------+------+-------+

以上语句等同于：

select * from student,sc where student.Sno=sc.Sno;
    +-----------+--------+------+------+-------+-----------+------+-------+
    | Sno       | Sname  | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
    +-----------+--------+------+------+-------+-----------+------+-------+
    | 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    1 |    92 |
    | 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    2 |    85 |
    | 201215121 | 李勇   | 男   |   22 | CS    | 201215121 |    3 |    88 |
    | 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 |    2 |    90 |
    | 201215122 | 刘晨   | 女   |   19 | CS    | 201215122 |    3 |    80 |
    | 201215128 | 陈冬   | 男   |   18 | IS    | 201215128 |    1 |    78 |
    +-----------+--------+------+------+-------+-----------+------+-------+

## 扩展 ##

### 如果只想从A表中取出一些记录，但不包含B表 ###

![这里写图片描述][20160807133509878]

可以在left join 后面加上一个where语句

select * from student left join sc using(Sno) where sc.Sno is null;
    +-----------+-----------+------+------+-------+------+-------+
    | Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
    +-----------+-----------+------+------+-------+------+-------+
    | 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
    | 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
    | 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
    +-----------+-----------+------+------+-------+------+-------+

### 求差集 ###

![这里写图片描述][20160807133836179]

可以结合union字句，由于本例中，右侧的已经全部对应的所以显示结果，和上一个一致。

select * from student left join sc using(Sno) where student.Sno is null union select * from student left join sc using(Sno) where sc.Sno is null;
    +-----------+-----------+------+------+-------+------+-------+
    | Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
    +-----------+-----------+------+------+-------+------+-------+
    | 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
    | 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
    | 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
    +-----------+-----------+------+------+-------+------+-------+

### FULL JOIN ###

![这里写图片描述][20160807134149038]

select * from student left join sc on student.Sno=sc.Sno union  select * from student right join sc on student.Sno=sc.Sno;
    +-----------+-----------+------+------+-------+-----------+------+-------+
    | Sno       | Sname     | Ssex | Sage | Sdept | Sno       | Cno  | Grade |
    +-----------+-----------+------+------+-------+-----------+------+-------+
    | 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    1 |    92 |
    | 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    2 |    85 |
    | 201215121 | 李勇      | 男   |   22 | CS    | 201215121 |    3 |    88 |
    | 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    2 |    90 |
    | 201215122 | 刘晨      | 女   |   19 | CS    | 201215122 |    3 |    80 |
    | 201215128 | 陈冬      | 男   |   18 | IS    | 201215128 |    1 |    78 |
    | 201215123 | 王敏      | 女   |   18 | MA    |      NULL | NULL |  NULL |
    | 201215125 | 张立      | 男   |   19 | IS    |      NULL | NULL |  NULL |
    | 201215126 | 张成民    | 男   |   18 | CS    |      NULL | NULL |  NULL |
    +-----------+-----------+------+------+-------+-----------+------+-------+

注：A left join B 等同于 B right join A

mysql> select * from student left join sc using(Sno);
    +-----------+-----------+------+------+-------+------+-------+
    | Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
    +-----------+-----------+------+------+-------+------+-------+
    | 201215121 | 李勇      | 男   |   22 | CS    |    1 |    92 |
    | 201215121 | 李勇      | 男   |   22 | CS    |    2 |    85 |
    | 201215121 | 李勇      | 男   |   22 | CS    |    3 |    88 |
    | 201215122 | 刘晨      | 女   |   19 | CS    |    2 |    90 |
    | 201215122 | 刘晨      | 女   |   19 | CS    |    3 |    80 |
    | 201215128 | 陈冬      | 男   |   18 | IS    |    1 |    78 |
    | 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
    | 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
    | 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
    +-----------+-----------+------+------+-------+------+-------+
    9 rows in set (0.00 sec)
    
    mysql> select * from sc right join student using(Sno);
    +-----------+-----------+------+------+-------+------+-------+
    | Sno       | Sname     | Ssex | Sage | Sdept | Cno  | Grade |
    +-----------+-----------+------+------+-------+------+-------+
    | 201215121 | 李勇      | 男   |   22 | CS    |    1 |    92 |
    | 201215121 | 李勇      | 男   |   22 | CS    |    2 |    85 |
    | 201215121 | 李勇      | 男   |   22 | CS    |    3 |    88 |
    | 201215122 | 刘晨      | 女   |   19 | CS    |    2 |    90 |
    | 201215122 | 刘晨      | 女   |   19 | CS    |    3 |    80 |
    | 201215128 | 陈冬      | 男   |   18 | IS    |    1 |    78 |
    | 201215123 | 王敏      | 女   |   18 | MA    | NULL |  NULL |
    | 201215125 | 张立      | 男   |   19 | IS    | NULL |  NULL |
    | 201215126 | 张成民    | 男   |   18 | CS    | NULL |  NULL |
    +-----------+-----------+------+------+-------+------+-------+

[20160807132513755]: /images/20220718/345e56e6d145447791744b1c2ae023a8.png
[20160807133304222]: /images/20220718/f8af41ad74bf43a4b88fc3158d79b9cd.png
[20160807133509878]: /images/20220718/0869e2c54f934b15be89ffd7873fc008.png
[20160807133836179]: /images/20220718/8467ff11d31045be92460fcbd0a49596.png
[20160807134149038]: /images/20220718/5d5e54ff1f8147a79cda5f7367e275bc.png