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)
Salin selepas log masuk
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)
Salin selepas log masuk
LEFT JOIN（左连接）
sql语句如下：
select * from student left join sc on student.Sno=sc.Sno;
Salin selepas log masuk
Salin selepas log masuk
运行结果如下：
+-----------+-----------+------+------+-------+-----------+------+-------+
| 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 |
+-----------+-----------+------+------+-------+-----------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
在此例中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)
Salin selepas log masuk
以上等价于
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 |
+-----------+-----------+-------+
Salin selepas log masuk
不同的地方，例如：
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 |
+-----------+-----------+------+------+-------+-----------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
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 |
+-----------+-----------+------+------+-------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
重复的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 |
+-----------+------+-------+--------+------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
INNER JOIN（相等连接或内连接）
不会显示以谁为基础，只会显示符合条件的记录
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 |
+-----------+--------+------+------+-------+-----------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
以上语句等同于：
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 |
+-----------+--------+------+------+-------+-----------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
扩展
如果只想从A表中取出一些记录，但不包含B表
可以在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 |
+-----------+-----------+------+------+-------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
求差集
可以结合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 |
+-----------+-----------+------+------+-------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
FULL JOIN
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 |
+-----------+-----------+------+------+-------+-----------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
注：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 |
+-----------+-----------+------+------+-------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
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)
Salin selepas log masuk
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)
Salin selepas log masuk
LEFT JOIN（左连接）
sql语句如下：
select * from student left join sc on student.Sno=sc.Sno;
Salin selepas log masuk
Salin selepas log masuk
运行结果如下：
+-----------+-----------+------+------+-------+-----------+------+-------+
| 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 |
+-----------+-----------+------+------+-------+-----------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
在此例中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)
Salin selepas log masuk
以上等价于
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 |
+-----------+-----------+-------+
Salin selepas log masuk
不同的地方，例如：
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 |
+-----------+-----------+------+------+-------+-----------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
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 |
+-----------+-----------+------+------+-------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
重复的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 |
+-----------+------+-------+--------+------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
INNER JOIN（相等连接或内连接）
不会显示以谁为基础，只会显示符合条件的记录
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 |
+-----------+--------+------+------+-------+-----------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
以上语句等同于：
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 |
+-----------+--------+------+------+-------+-----------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
扩展
如果只想从A表中取出一些记录，但不包含B表
可以在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 |
+-----------+-----------+------+------+-------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
求差集
可以结合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 |
+-----------+-----------+------+------+-------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
FULL JOIN
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 |
+-----------+-----------+------+------+-------+-----------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
注：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 |
+-----------+-----------+------+------+-------+------+-------+
Salin selepas log masuk
Salin selepas log masuk
以上就是MySQL中join用法解析的内容，更多相关内容请关注PHP中文网（www.php.cn）！
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
