MySQL中join用法解析

345

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）！

? 版權聲明
THE END
數據庫
# 數據庫
喜歡就支持一下吧
點贊5 分享