快捷導(dǎo)航

一文詳解MySQL中數(shù)據(jù)表的外連接

更新時(shí)間：2022年08月02日 11:43:13 作者：不渴望力量的哈士奇

因?yàn)?nbsp;MySQL 是關(guān)系型數(shù)據(jù)庫(kù)，數(shù)據(jù)是拆分重組在多個(gè)數(shù)據(jù)表里面的。所以我們勢(shì)必要從多個(gè)數(shù)據(jù)表中提取數(shù)據(jù)，通過(guò) SQL 語(yǔ)句的內(nèi)連接與外連接就能夠?qū)崿F(xiàn)多表查詢了，本文就來(lái)講講MySQL的外連接

該章節(jié)的內(nèi)容為多表連接查詢的外連接，因?yàn)?MySQL 是關(guān)系型數(shù)據(jù)庫(kù)，數(shù)據(jù)是拆分重組在多個(gè)數(shù)據(jù)表里面的。所以我們勢(shì)必要從多個(gè)數(shù)據(jù)表中提取數(shù)據(jù)，通過(guò) SQL 語(yǔ)句的內(nèi)連接與外連接就能夠?qū)崿F(xiàn)多表查詢了。這部分內(nèi)容是需要我們重點(diǎn)學(xué)習(xí)的，學(xué)習(xí)的過(guò)程中會(huì)穿插多種的案例來(lái)強(qiáng)化對(duì)表連接的語(yǔ)法的運(yùn)用。

為什么要使用外連接

在解釋為什么使用 “外連接” 之前，先來(lái)看一個(gè)記錄。(如下：)

針對(duì)表中的張三沒(méi)有所屬的部門(mén)編號(hào)，我們暫且將他歸類為 “臨時(shí)工” ，沒(méi)有固定的部門(mén)編制。

介于如此的場(chǎng)景，那么問(wèn)題就出現(xiàn)了。當(dāng)我們想要查詢每一名員工和他的所屬部門(mén)的名稱時(shí)，在使用內(nèi)連接的情況下，因?yàn)槲覀兊逆溄訔l件是` “員工表” 的 “部門(mén)編號(hào)” = “部門(mén)表” 的 “部門(mén)編號(hào)” ，就會(huì)將 “張三” 漏掉。雖然說(shuō) “張三” 沒(méi)有 “部門(mén)編號(hào)” ，但是他作為 “臨時(shí)工” 也是公司的一員，所以要引入外連接的語(yǔ)法才能解決這個(gè)問(wèn)題，否則就會(huì)丟失一些符合邏輯的數(shù)據(jù)。

外連接簡(jiǎn)介

外連接與內(nèi)連接的區(qū)別：

內(nèi)連接的結(jié)果接中只會(huì)出現(xiàn)符合連接條件的記錄，不符合連接條件的記錄是絕對(duì)不會(huì)出現(xiàn)在結(jié)果集里面的。

外連接則顯得很特殊，無(wú)論是否符合數(shù)據(jù)連接條件，都會(huì)出現(xiàn)在結(jié)果級(jí)里。（就比如上文提到的查詢員工部門(mén)標(biāo)號(hào)信息，因?yàn)?“張三” 沒(méi)有部門(mén)編號(hào)，如果使用內(nèi)連接，則 “張三” 就不符合 “連接條件”，也就不會(huì)出現(xiàn)在結(jié)果集里面；換做 “外連接” 就不會(huì)漏掉了。）

外連接例句如下

SELECT 
	e.empno, e.ename, d.dname
FROM
	t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno;

-- 在連接的時(shí)候仍然是鏈接 "員工表" 與 "部門(mén)表" ，只不過(guò)連接關(guān)鍵字由 "JOIN" 變成了 "LEFT JOIN" (下文再為大家詳細(xì)解釋)
-- 兩張表的連接條件還是使用 "ON" 關(guān)鍵字去連接的 ， 連接條件依然是 "員工表" 的 "部門(mén)編號(hào)" = "部門(mén)表" 的 "部門(mén)編號(hào)" 

-- LEFT JOIN 為 "外連接" 的 "左外連接" ；（在 "外連接" 中，是分為 "左外連接" 與 "右外連接" 的）

-- 在該SQL語(yǔ)句中 "LEFT JOIN" 左右各有數(shù)據(jù)表  "t_emp e" 與 "t_dept d" 
-- 所以這里的 "左連接" 的意思就是：保留 左表 的所有記錄，然后與 右表 去連接，如果 右表 有符合條件的記錄，則正常連接即可；
-- 如果 右表 沒(méi)有符合條件的連接記錄， 右表 則展示 "NULL" 值與 "左表" 去匹配

左連接與右連接

“左連接” 也被稱之為 “左外連接” ，就是保留左表所有的記錄，與右表做連接。如果右表有符合條件的記錄就與左表連接；如果右表沒(méi)有符合條件的記錄，就用 “NULL” 與左表連接。

區(qū)別于 “左連接” 的就是 “右連接”，“右連接” 與 “左連接” 相反，是保留右表的所有記錄與左表的符合條件的記錄進(jìn)行連接；同樣的，如果左表沒(méi)有符合條件的記錄，就用 “NULL” 與右表連接。

右連接 SQL 語(yǔ)句示例：

SELECT 
	e.empno, e.ename, d.dname
FROM
	t_dept d
RIGHT JOIN t_emp e ON e.deptno = d.deptno;

-- 這里有個(gè)需要注意的地方，就是相較于上文中的 "左連接" ，這里的 "右連接" 左右兩張的表的位置做了調(diào)換

在這里，大家可以看到，仍然可以查出來(lái) “張三” 的沒(méi)有 “部門(mén)編號(hào)” 的記錄。所以說(shuō) “左連接” 與 “右連接” 的區(qū)別并不是很大。

外連接練習(xí)①

查詢每個(gè)部門(mén)的名稱和部門(mén)的人數(shù)？

這道題看似簡(jiǎn)單，但是里面存在著兩個(gè)難點(diǎn)，而且也存在容易出錯(cuò)的地方，詳見(jiàn)下文 SQL 語(yǔ)句示例，與示意圖。

SELECT 
    d.deptno, d.dname, COUNT(*)
FROM
    t_dept d LEFT JOIN t_emp e 
ON d.deptno = e.deptno
GROUP BY d.deptno;

OK，問(wèn)題就在這里開(kāi)始出現(xiàn)了。

大家注意這里的 “40” - “OPERATIONS” 部門(mén)，這個(gè)部門(mén)實(shí)際上是沒(méi)有人的，也就是人數(shù)為 “0” ，但是奇怪的是，這里進(jìn)行統(tǒng)計(jì)的時(shí)候，卻出現(xiàn)了統(tǒng)計(jì)人數(shù) “1” ，這是為什么呢?

這是因?yàn)槲覀冊(cè)谑褂梅纸M的時(shí)候，用的是 “左連接” ，保留了左表的所有的數(shù)據(jù)，所以就按照左表的 “deptno” 進(jìn)行分組。（因?yàn)槭潜Ａ袅俗蟊淼挠涗洠苑纸M也需要按照左表進(jìn)行分組。）；接下來(lái)重要的就是 “COUNT(*)” 這一部分了，“COUNT(*)” 統(tǒng)計(jì)的是所有的有效記錄。所以當(dāng)左表 “t_dept” 的所有記錄與右表 “t_emp” 進(jìn)行連接的時(shí)候，右表會(huì)使用 "NULL"值與左表 “t_dept” 連接，連接完之后就是一條有效的記錄，如此既然是有效記錄，那么 “COUNT(*)” 統(tǒng)計(jì)到的結(jié)果就是 “1”。

所以，如此統(tǒng)計(jì)到 40 部門(mén)的統(tǒng)計(jì)結(jié)果為 “1” ，也是情有可原的，但是這個(gè)結(jié)果并不是我們想要的，要怎么去解決呢？參考下方的 SQL 語(yǔ)句。

SELECT 
    d.deptno, d.dname, COUNT(e.deptno)
FROM
    t_dept d LEFT JOIN t_emp e 
ON d.deptno = e.deptno
GROUP BY d.deptno;

這個(gè) SQL 語(yǔ)句還是非常不錯(cuò)的，有非常多的細(xì)節(jié)與未考慮到的情況，只有真正寫(xiě)一遍的時(shí)候才能會(huì)注意到這些襲擊。

外連接練習(xí)②

查詢每個(gè)部門(mén)的名稱和部門(mén)的人數(shù)？如果是沒(méi)有部門(mén)的員工，部門(mén)名稱用 “NULL” 替代。(這里其實(shí)就是指的 "張三")

可能這里會(huì)覺(jué)得剛剛使用的是 "左外連接" 保留的部門(mén)表中所有的記錄，這里直接換成 "右" 外連接不就行了么？其實(shí)...沒(méi)那么簡(jiǎn)單。

這個(gè)練習(xí)的 SQL 語(yǔ)句需要使用到"UNION"關(guān)鍵字去實(shí)現(xiàn)，利用"UNION" 關(guān)鍵字將多個(gè)查詢語(yǔ)句的結(jié)果集進(jìn)行并集的合并處理(排除重復(fù)內(nèi)容)。

"UNION"關(guān)鍵字在 SQL 語(yǔ)句中的用法如下：

(SQL查詢語(yǔ)句) UNION (SQL查詢語(yǔ)句) -- 如果存在多條查詢語(yǔ)句的話，可以繼續(xù)使用 UNION 關(guān)鍵字連接

PS：這里需要注意一下，“UNION” 合并多少個(gè)結(jié)果集其實(shí)無(wú)所謂，關(guān)鍵是這些結(jié)果集的字段數(shù)量和字段的名稱必須要相同。如果說(shuō)第一個(gè) SQL 查詢語(yǔ)句返回的是 10個(gè) 字段，第二個(gè)返回的是 2個(gè)字段，這種情況是完全沒(méi)辦法合并的。

(SELECT 
	d.deptno, d.dname, COUNT(e.deptno)
FROM
	t_dept d LEFT JOIN t_emp e 
ON d.deptno = e.deptno
GROUP BY d.deptno)
UNION
(SELECT 
	d.deptno, d.dname, COUNT(*)
FROM
	t_dept d RIGHT JOIN t_emp e 
ON d.deptno = e.deptno
GROUP BY d.deptno);

-- 第一個(gè)查詢語(yǔ)句，得到的結(jié)果集是各個(gè)部門(mén)的人數(shù)。
-- 第二個(gè)查詢語(yǔ)句，得到的結(jié)果集是隸屬于各個(gè)部門(mén)的人數(shù)，但是因?yàn)?"張三" 是一個(gè)沒(méi)有部門(mén)所屬的 "臨時(shí)工"
-- 所以兩個(gè)查詢語(yǔ)句的結(jié)果集合并之下沒(méi)救如下圖所示。