欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL中join用法解析

程序员文章站 2023-12-22 12:04:22
...


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(左连接)

MySQL中join用法解析
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(相等连接或内连接)

MySQL中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 |
+-----------+--------+------+------+-------+-----------+------+-------+

以上语句等同于:

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表

MySQL中join用法解析

可以在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 |
+-----------+-----------+------+------+-------+------+-------+

求差集

MySQL中join用法解析

可以结合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

MySQL中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 |
+-----------+-----------+------+------+-------+-----------+------+-------+

注: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 |
+-----------+-----------+------+------+-------+------+-------+

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(左连接)

MySQL中join用法解析
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(相等连接或内连接)

MySQL中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 |
+-----------+--------+------+------+-------+-----------+------+-------+

以上语句等同于:

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表

MySQL中join用法解析

可以在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 |
+-----------+-----------+------+------+-------+------+-------+

求差集

MySQL中join用法解析

可以结合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

MySQL中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 |
+-----------+-----------+------+------+-------+-----------+------+-------+

注: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 |
+-----------+-----------+------+------+-------+------+-------+

以上就是MySQL中join用法解析的内容,更多相关内容请关注PHP中文网(www.php.cn)!

相关标签: MySQL,join

上一篇:

下一篇: