MySQL基礎（六）-----子查詢和連接配接查詢

子查詢簡而言之就是在一個查詢裡面再嵌套一個或者多個查詢語句；

連接配接查詢指的就是将多個表或者單個表連接配接在一起進行查詢

本文涉及的的表還是來自MySQL基礎（四）-----簡單查詢和帶附帶條件的查詢_Spring@W的部落格-CSDN部落格不知道的可以去檢視一下

一、子查詢

1、簡單的多表查詢

目前為止，我們的查詢都是作用于單個表的，倘若我們需要從多個表中查詢資料。那我們應該如何處理？樣例問題，我要查詢名字為‘韓信’同學的成績應該如何查詢？

最笨的處理方法：

先從學生資訊表查詢到韓信的學生id；
再根據學生id去查詢成績表；

實際效果：

但是如果我們想查詢更複雜的情況呢？接下來介紹的方法就可以替我們解決

2、标量子查詢

看上述的查詢語句，分析可以得到，第二個查詢語句的條件其實是用到了第一個語句的結果。為了書寫簡單，我們把二者合二為一。

sql複制代碼SELECT * from student_score WHERE number = (SELECT id from student WHERE `name`='韓信');

可以發現結果和分開查詢的結果是一緻的。這是為什麼呢？

我們把第二條查詢語句用小括号括起來，并作為一個操作數放到了第一條查詢語句的搜尋條件處，這樣就起到了合并兩條查詢語句的作用；
小括号中的查詢語句也被稱為子查詢或者内層查詢，而使用子查詢的結果作為搜尋條件的查詢稱為外層查詢；
如果在一個查詢語句中需要用到更多的表，那麼可以在一個子查詢中繼續嵌套另一個子查詢，在執行查詢語句時，将按照從内到外的順序依次執行這些查詢。
所有的子查詢都必須用小括号擴起來，否則是違法的！

在上述樣例中，子查詢的結果隻有一個值（也就是韓信的學号），這種子查詢被稱為标量子查詢。

因為标量子查詢單純地代表一個值，是以它可以作為表達式的操作數來參與運算；
标量子查詢除了用在外層查詢的搜尋條件中，還可以放在查詢清單處。（樣例如下）

由于标量子查詢單純地代表一個值，是以可以與其他操作數通過運算符連接配接起來，組成更複雜的表達式。我們常用于where子句後面。樣例如下

3、列子查詢

如果我們要查詢MySQL技術專業學生的成績，我們必須先從學生資訊表student中根據專業名稱找到學生的id，然後再根據id去查成績表student_score獲得對應的成績。分析：方法一：

方法二：

sql複制代碼SELECT * FROM student_score WHERE number IN(SELECT id from student WHERE major='MySQL技術');

方法一中的搜尋條件用到了第一條查詢語句的查詢結果，我們在标量子查詢的基礎上，就可以把第一條語句作為内層查詢，第二條作為外層查詢，進而得到我們的方法二的語句。

在方法二的語句中，子查詢的結果集并不是一個單獨的值，而是一個列（id列,它包含2個值，分别是20230103,20230104），是以這個子查詢也被稱為列子查詢。

4、行子查詢

有列子查詢，當然肯定會有行子查詢。隻要子查詢的結果集中最多隻包含一條記錄，而且這條記錄中有超過一個列的資料（如果該條記錄隻包含一個列的話，該子查詢就成了标量子查詢），那麼這個子查詢就可以稱為行子查詢。樣例如下：

編輯

再子查詢中限定最多隻能傳回一條記錄，該子查詢就可以被看作為一個行子查詢

注意：在想要得到标量子查詢或者行子查詢，但又不能保證子查詢的結果集隻有一條記錄時，需要使用limit 1子句來限制結果集中記錄的數量

之前的篇幅中我們介紹過一個操作數都是單一的值，。但是，在上述例子中的子查詢執行後産生的結果集是一個行（包含2個列），是以與其進行等值比較的另一個操作數也得包含2個值，在上述語句中的（number,subject）（注意這個地方必須要用小括号擴起來，否則會産生歧義）。它表達的語義就是，先擷取到子查詢中的執行結果，然後再執行外層查詢，如果成績表中記錄的number等于子查詢中的number列，并且subject列等于‘計算機科學與技術’，那麼就将該記錄加入到結果集。

5、表子查詢

如果子查詢的結果集中包含多行多列，那麼這個子查詢也可以稱為表子查詢

樣例：

子查詢執行之後的結果集中包含多行多列，是以可以被看作是一個表子查詢。

6、EXISTS和NOT EXISTS子查詢

有時外層查詢并不關心子查詢中的結果是什麼，隻關心子查詢的結果集是不是為空集。可以用到EXISTS和NOT EXISTS這兩個運算符。

樣例：

因為子查詢中學生表裡面沒有‘hahahahahha’專業的同學，是以傳回結果為空，是以整個語句查詢不到資料。

在包含[NOT] EXISTS子查詢的語句中，由于我們隻關心子查詢的結果集是不是空集，而不關心具體結果是什麼，是以子查詢的查詢清單裡面寫啥都可以，并不一定非得寫‘*’；

7、不相關子查詢和相關子查詢

在之前的子查詢中，子查詢都可以獨立運作并産生結果，之後再拿結果作為外層查詢的條件去執行外層查詢，這種子查詢稱為不相關子查詢。

樣例：

在樣例中的子查詢中隻使用了學生表而沒有使用成績表，它可以獨立運作并産生結果，是以這是一種典型的不相關子查詢。

有時候，我們需要在子查詢的語句中引用外層查詢的列，這樣的話子查詢就不能當作一個獨立的語句去執行，這種子查詢被稱為相關子查詢。

樣例：

sql複制代碼SELECT id number,sn,`name`,id_number,major FROM student WHERE EXISTS (SELECT * FROM student_score WHERE student.id=student_score.number);

注意，當兩個表中的列相同時，在子查詢的where語句中書寫時要區分開，可以在前面加上表名。直接寫：相同的列名=相同的列名會造成二義性，會把MySQL伺服器搞懵的，不知道這個列到底是哪個表的。正确的寫法：表1.相同的列名=表2.相同的列名，即使表名不相同也可以這樣寫，這種顯示地将列所屬的表名書寫出來的名稱稱為該列的全限定名。

8、對同一個表的子查詢

不光可以對不同的表進行子查詢，對同一個表也可以進行子查詢。樣例如下：

sql複制代碼SELECT * FROM student_score WHERE `subject` = 'MySQL技術' AND score >(SELECT AVG(score) FROM student_score WHERE `subject` = 'MySQL技術');

二、連接配接查詢

介紹連接配接查詢之前，我們先來重新定義一下關系表。我們把學生表和成績表合并起來，就起名為student_merge

這樣我們查詢資料起來很友善，但是它的劣勢也是顯而易見的。

浪費存儲空間--我們每一次新加一門學科成績時都要儲存一次學生的資訊，重複操作且還是重複存儲；
維護困難--當修改某個學生的基本資訊時，要修改的地方将會是所有包含該學生的記錄，很容易導緻資訊的不一緻，增大維護的難度。

是以為了盡可能的減少存儲的備援資訊，降低維護難度，我們一開殺就把這個合并表分成了學生表和學生成績表。但是這兩張表之間有某種關系作為紐帶，這裡的某種關系指的就是兩個表都擁有的學生id。

1、連接配接的概念

我們将表拆分為學生表和成績表後，的确解決了資料備援的問題，但是資料的查詢卻成為了一個問題。到目前為止，所有篇幅中介紹的查詢語句中還無法把某個學生的所有資訊查詢出來。即使是子查詢也不行，雖然子查詢可以在一個查詢語句中設計多個表，但是整個查詢語句最終産生的結果集還是用來展示外層查詢的結果，子查詢的結果隻是被當作中間結果來使用。

下面重建立兩個測試表并插入資料，供連接配接查詢使用。

sql複制代碼CREATE TABLE t1(
	a1 int,
	b1 char(1)
);
CREATE TABLE t2(
	a2 int,
	b2 char(1)
);

INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c');
INSERT INTO t2 VALUES(2,'b'),(3,'c'),(4,'d');

目前我們已經建立了t1和t2兩個表，并且往兩個表中各加入了三條資料。

兩個表連接配接的本質就是把一個表中記錄與另一個表中的記錄兩兩想呼喊組合，将組合後的記錄加入到最終的結果集。把t1和t2表連接配接起來的過程如圖所示

這個過程看起來就是把t1表的記錄和t2表的記錄連起來組成新的更大的記錄，是以這個查詢過程稱為連接配接查詢。

在沒有任何過濾條件的情況下，兩個表連接配接起來生成的結果集也被稱為笛卡兒積。因為表t1中有3條記錄，t2中有3條記錄，是以這兩個表連接配接後的笛卡兒積就有33=9條記錄。

連接配接查詢的文法：

sql複制代碼	SELECT * FROM 表名1,表名2;

樣例：

查詢清單處的‘*’代表要讀取from子句中的每個表的所有列。上面的語句其實和下面的這些寫法是等價的。

寫法一：這種寫法是将t1、t2表中的列名都顯式的寫出來，也就是使用了列的全限定名；

寫法二：由于t1、t2表中的列名并不重複，是以沒有可能讓伺服器發懵的二義性，是以在查詢清單上直接使用列名也是可以的；

寫法三：這種寫法的意思就是查詢t1表的全部的列和t2表的全部的列。

2、連接配接查詢的過濾條件

當表的資料多了後，笛卡兒積的結果是非常巨大的，是以我們必須要在進行連接配接查詢的時候指定過濾條件。

連接配接查詢中的過濾條件分為兩種

涉及單表的條件：這種隻涉及單表的過濾條件已經提到過很多次了，前面篇幅中也一直稱為搜尋條件，比如t1.a1>1隻是針對t1表的過濾條件等；
涉及兩表的條件：這種過濾條件我們之前沒見過，比如t1.a1=t2.a2、t1.b1>t2.b2等，這些條件找那個涉及兩個表

樣例分析：

過濾條件：

t1.a1 > 1
t1.a1 = t2.a2
t2.b2 < 'd'

查詢過程分析：

首先确定第一個需要查詢的表，這個表為驅動表。這裡假設使用t1作為驅動表，那麼就需要在t1表中查找滿足t1.a1>1的記錄。符合條件的有2條記錄；
針對滿足t1.a1>1條件的驅動表中的兩條記錄，都需要到t2表中查找比對的記錄（所謂比對的記錄，指的是符合過濾條件的記錄）。因為是根據t1表中的記錄去查找t2表中的記錄，是以t2表也可以被稱為被驅動表。符合條件1的記錄有兩條，是以要查t2表兩次，查兩次的條件分别為:a1=a2=2時，t1.a1=t2.a2 AND t2.b2<'d'；a1=a2=3時，t1.a1=t2.a2 AND t2.b2<'d'；

從上述的過程分析中可以得到，兩表連接配接查詢中，驅動表隻需要查詢一次，而被驅動表可能會被查詢多次。

3、内連接配接和外連接配接

假設一個需求，我們要查詢學生的基本新，還要查詢學生的成績資訊。

這個需求就需要兩表查詢了。連接配接過程就是從學生表中取出記錄，在成績表裡面去查找學生id相同的記錄，過濾條件就是student.id=student.number。樣例：

ini複制代碼SELECT student.id number,sn,`name`,department,major,`subject`,score FROM student,student_score WHERE student.id=student_score.number;

從結果我們可以看到，除了20230105和20230106兩個同學，其他的同學資訊都展示出來了，這是因為這兩位同學可能因為某些原因沒有參加考試，是以沒有成績；

但是我們想要檢視全部的記錄，這個結果的本質就是：驅動表中的記錄即使在被驅動表中沒有比對的記錄，也需要加到結果集中；

是以我們有了内連接配接和外連接配接：

對于内連接配接的兩個表，如果驅動表中的記錄在被驅動表中找不到比對的記錄，則該記錄不會加入到最後的結果集中。上面提到的連接配接都是内連接配接；
對于外連接配接的兩個表，即使驅動表中的記錄在被驅動表中沒有比對記錄，也仍然需要加入到結果集中。

可是仍然存在問題，對于外連接配接來說，有時候我們并不想把驅動表的全部記錄都加入到最後的結果集中。這個時候就需要用到on子句。

where子句和on子句過濾條件擁有不同的含義：

where子句中的過濾條件--我們之前一直吧過濾條件放在where子句彙總，不論是内連接配接還是外連接配接，凡是不符合where子句中過濾條件的記錄都不會被加入到最後的結果集；
on子句中的過濾條件--對于外連接配接的驅動表的記錄來說，如果無法在被驅動表中找到比對on子句中過濾條件的記錄，那麼該記錄仍然會被加入到結果集中，對應的被驅動表記錄的各個字段使用NULL值填充

注意：這個on子句的特殊作用隻有在外連接配接查詢中才會得以展現，如果把on子句放到内連接配接中，MySQL會把它和where子句一樣對待。内連接配接中的where子句和on子句是等價的。

3.1、連接配接文法

在MySQL中，根據選取的驅動表的不同，外連接配接可以細分為左（外）連接配接和右（外）連接配接兩種。

左（外）連接配接的文法

模闆：

sql複制代碼SELECT * FROM 表1 LEFT [OUTER] JOIN 表2 ON 過濾條件 [WHERE 過濾條件];

其中，中括号裡的outer單詞是可以省略的。對于左（外）連接配接來說說，我們把放在LEFT [OUTER] JOIN左側的表稱為外表或者驅動表，右側的表稱為内表或者被驅動表。（表1是外表或者驅動表，表2是内表或者被驅動表）通常會将涉及量表的過濾條件放到on子句中（并非絕對的，具體怎麼過濾根據使用者個人選擇）

注意：對于左（外）連接配接和右（外）連接配接來說，on子句中的過濾條件是不能省略的

解決需求：

sql複制代碼SELECT student.id number,sn,`name`,department,major,`subject`,score FROM student LEFT JOIN student_score on student.id=student_score.number;

右（外）連接配接的文法

右（外）連接配接的文法和左（外）連接配接的文法是一樣的，隻是把left改成了right而已

sql複制代碼SELECT * FROM 表1 RIGHT [OUTER] JOIN 表2 ON 過濾條件 [WHERE 過濾條件];

隻不過驅動表是RIGHT [OUTER] JOIN右側的表，被驅動表是左側的表

3.2、内連接配接的文法

再次強調一下，内連接配接和外連結的根本差別就是在驅動表的記錄不符合on子句中的過濾條件時，内連接配接不會把該驅動表的記錄加入到最後的結果集；而外連接配接會。

其實最簡單的内連接配接的文法就是直接把需要連接配接的多個表都放在from子句後面，除此之外，MySQL還提供了其他多種進行内連接配接查詢的文法。

模闆：

sql複制代碼SELECT * FROM 表1 [INNER | CROSS] JOIN 表2 [ON 過濾條件] [WHERE 過濾條件];

以下的幾個内連接配接寫法都是等價的：

vbnet複制代碼SELECT * FROM 表1,表2 [WHERE 過濾條件];
SELECT * FROM 表1 JOIN 表2 [ON 過濾條件] [WHERE 過濾條件];
SELECT * FROM 表1 INNER JOIN 表2 [ON 過濾條件] [WHERE 過濾條件];
SELECT * FROM 表1 CROSS JOIN 表2 [ON 過濾條件] [WHERE 過濾條件];

在這些寫法裡面，還是推薦inner join的形式書寫内連接配接，正好和外連接配接的left join 和right join區分開了。不過由于在内連接配接中on子句和where子句是等價的，是以内連接配接中不要求強制寫明on子句。

大家可以根據自己掌握程度，去試試所有的連接配接文法。

4、多表連接配接

隻要你願意，你可以連接配接任意數量的表。在建立一個t3表，做個示範

sql複制代碼CREATE TABLE t3(
	a3 int,
	b3 char(1)
);

INSERT INTO t3 VALUES(3,'c'),(4,'d'),(5,'e');

樣例：

sql複制代碼SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.a1=t2.a2 AND t1.a1=t3.a3;

注意，内連接配接的表位置可以互換，不會影響結果，但是外連接配接不行。内連接配接中驅動表和被驅動表可以互換，在外連接配接中驅動表和被驅動表不可輕易互換。

5、表的别名

表的别名和列的别名一樣，都是用空白字元或者as隔開。在表名特别長的情況下，為表定義别名可以讓語句更加清晰一些。表的别名可以用在order by 、group by 等子句上。

樣例：

sql複制代碼SELECT s1.id number,sn,`name`,department,major,`subject`,score FROM student s1 LEFT JOIN student_score s2 on s1.id=s2.number;

6、自連接配接

不光可以多個不同表之間連接配接，同一個表也可以進行連接配接，但是要注意一個點就是，操作同一個表連接配接時，要給表起别名，否則會報錯。樣例：

由于被連接配接的表其實源自同一個表，是以這種連接配接稱為自連接配接。自連接配接也可以用于外連接配接也可以用于内連接配接，後面可以加上過濾條件。這裡就不展示了，感興趣的可以自己去嘗試寫一寫，在評論區讨論一下。

7、連接配接查詢與子查詢的轉換

有的查詢需求既可以使用連接配接查詢解決，也可以使用子查詢解決。都是等價的。實際開發中請大家根據自己的習慣進行書寫。

樣例：

sql複制代碼/**連接配接查詢**/
SELECT s2.* FROM student s1 INNER JOIN student_score s2 WHERE s1.id=s2.number and s1.major='計算機科學與技術';
/**子查詢**/
SELECT * FROM student_score WHERE number in(SELECT id number FROM student WHERE major='計算機科學與技術');

MySQL伺服器正在内部可能會将子查詢轉換為連接配接查詢來處理，當然也可能用到别的方式來處理。

MySQL基礎（六）-----子查詢和連接配接查詢。就分享到這個地方，後續會更新MySQL基礎（七）-----并集查詢和資料的插入、删除、更新。

今天的分享就到此結束了，如果覺得對您有幫助，麻煩給個三連！

面試官問：你是怎麼使用多表查詢的？快速讓你掌握多表查詢的秘訣