MySQL中join用法解析
- 黄舟原創
- 2017-02-27 11:54:421854瀏覽
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（左連接）
#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（相等連接或內連接）
不會顯示以誰為基礎，只會顯示符合條件的記錄
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表
#可以在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 | +-----------+-----------+------+------+-------+------+-------+
求差集
可以結合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
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（左連接）
#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（相等連接或內連接）
不會顯示以誰為基礎，只會顯示符合條件的記錄
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表
#可以在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 | +-----------+-----------+------+------+-------+------+-------+
求差集
可以結合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
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）！
陳述：
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn