集合操作

常用的集合操作主要有三種：UNION（聯(lián)合集）暇咆、INTERSECT（交叉集）锋爪、EXCEPT（求差集）。以上三種集合的操作都是直接作用在兩個或者多個 SQL 查詢語句之間爸业，將所有的元組按照特定的要求篩選后拼接起來其骄。SQL 查詢后實際上是得到一個新的數(shù)據(jù)表的形式，因此所作用的數(shù)據(jù)表之間必須定義相同的屬性扯旷，且屬性定義的順序相同拯爽。

舉個例子，要獲取所有擁有 gmail 或者 hotmail 郵箱賬號的學(xué)生信息：

(SELECT * FROM Student WHERE Email like '%@gmail.com')
UNION
(SELECT * FROM Student WHERE Email like '%@hotmail.com');

但以下語句無法正常工作薄霜，因為所作用的兩個表的屬性不同：

(SELECT StudentID, Name FROM Student)
UNION
(SELECT Email FROM Student);

連接操作 JOIN

當(dāng)我們想要查詢得到的數(shù)據(jù)包含多個表的內(nèi)容某抓，也就是要同時獲得不同表中屬性數(shù)值的時候，常使用 JOIN 語句

內(nèi)連接（INNER JOIN）

內(nèi)連接是應(yīng)用程序中用的普遍的"連接"操作惰瓜，它一般都是默認(rèn)連接類型否副。內(nèi)連接基于連接謂詞將兩張表（如 A 和 B）的列組合在一起，產(chǎn)生新的結(jié)果表崎坊。查詢會將 A 表的每一行和 B 表的每一行進(jìn)行比較备禀，并找出滿足連接謂詞的組合。當(dāng)連接謂詞被滿足奈揍，A 和 B 中匹配的行會按列組合（并排組合）成結(jié)果集中的一行曲尸。

內(nèi)連接可以進(jìn)一步被分為：相等連接、自然連接男翰、交叉連接

交叉連接 （CROSS JOIN）

交叉連接另患，又稱為笛卡爾連接（Cartesian join）或者叉乘（Product），它是所有類型內(nèi)連接的基礎(chǔ) 蛾绎。把表視為行記錄的集合昆箕，交叉連接返回這兩個集合的笛卡爾積鸦列。等價于連接條件永遠(yuǎn)為“真”。

如果 A 和 B 是兩個集合鹏倘，那么它們的交叉連接就記為：A x B薯嗤。比如：

SELECT * FROM employee CROSS JOIN department;

-- 隱式表達(dá)交叉連接
SELECT * FROM employee, department;

相等連接（equi-join，或 equijoin）

相等連接是比較連接的一種特例纤泵，它的連接謂詞只用了相等比較骆姐。通常在 JOIN 語句后跟 ON 關(guān)鍵字補(bǔ)充相等比較語句。舉個例子捏题，找出所有至少存在一名學(xué)生注冊的課程名字：

SELECT DISTINCT c.Cname
FROM Course c INNER JOIN Enrol e ON c.No=e.CourseNo;

自然連接（NATURAL JOIN）

自然連接比相等連接的進(jìn)一步特例化玻褪。兩表做自然連接時，兩表中的所有名稱相同的列都將被比較公荧，這是隱式的归园。自然連接得到的結(jié)果表中，兩表中名稱相同的列只出現(xiàn)一次稚矿。也就是說，通過相等連接得到的結(jié)果表中捻浦，用于比較的列可能會重復(fù)晤揣，而在自然連接中并不會出現(xiàn)。

SELECT *
FROM Student s NATURAL JOIN Enrol1 e;

外連接（OUTER JOIN）

外連接與內(nèi)連接最大的區(qū)別在于朱灿，外連接并不要求連接的兩表的每一條記錄在對方表中都一條匹配的記錄昧识。要保留所有記錄（甚至這條記錄沒有匹配的記錄也要保留）的表稱為保留表，沒有匹配的部分用 NULL 代替盗扒。

外連接可依據(jù)連接表保留左表跪楞，右表或全部表的行而進(jìn)一步分為左外連接，右外連接和全連接侣灶。

需要注意一點甸祭，當(dāng)外部連接既包含 ON 子句又包含 WHERE 子句時，應(yīng)當(dāng)只把表之間的連接條件寫在 ON 子句中褥影，對表中數(shù)據(jù)的篩選必須寫在 WHERE 子句中池户。而內(nèi)部連接的各條件表達(dá)式既可以放在 ON 子句又可以放在 WHERE 子句中。這是因為對于外部連接凡怎，保留表中被 ON 子句篩除掉的行要被添加回來校焦，在此操作之后才會用 WHERE 子句去篩選連接結(jié)果中的各行。

三種外連接類型

左外連接（LEFT OUTER JOIN）

左外連接（left outer join）统倒，亦簡稱為左連接（left join）寨典，若 A 和 B 兩表進(jìn)行左外連接，那么結(jié)果表中將包含"左表"（即表 A）的所有記錄房匆，即使那些記錄在"右表" B 沒有符合連接條件的匹配耸成。這意味著即使 ON 語句在 B 中的匹配項是0條报亩，連接操作還是會返回一條記錄，只不過這條記錄中來自于 B 的每一列的值都為 NULL墓猎。這意味著左外連接會返回左表的所有記錄和右表中匹配記錄的組合（如果右表中無匹配記錄捆昏，來自于右表的所有列的值設(shè)為 NULL）。如果左表的一行在右表中存在多個匹配行毙沾，那么左表的行會復(fù)制和右表匹配行一樣的數(shù)量骗卜，并進(jìn)行組合生成連接結(jié)果。

SELECT *
FROM Student s LEFT JOIN Enrol1 e
ON s.StudentID=e.StudentID;

右外連接（RIGHT OUTER JOIN）

右外連接（right outer join）左胞，亦簡稱右連接（right join）寇仓，它與左外連接完全類似，只不過是作連接的表的順序相反而已烤宙。如果 A 表右連接 B 表遍烦，那么"右表" B 中的每一行在連接表中至少會出現(xiàn)一次。如果 B 表的記錄在"左表" A 中未找到匹配行躺枕，連接表中來源于 A 的列的值設(shè)為 NULL服猪。

右連接操作返回右表的所有行和這些行在左表中匹配的行（沒有匹配的，來源于左表的列值設(shè)為 NULL）拐云。

SELECT *
FROM Student s RIGHT JOIN Enrol1 e
ON s.StudentID=e.StudentID;

子查詢 Subqueries

子查詢通常用在 FROM 子句或者 WHERE 子句當(dāng)中罢猪。用于 FROM 子句的目的通常是從查詢的到結(jié)果表中采取進(jìn)一步的查詢，比如調(diào)用內(nèi)置函數(shù)叉瘩，獲取最大值膳帕、最小值、平均值、計數(shù)等等。用于 WHERE 子句的情況炼杖，通常會使用一下關(guān)鍵字：

• IN：檢查某個元組是否存在于子查詢結(jié)果中。
• EXISTS：檢查子查詢結(jié)果是否為空汤徽。
• ALL, SOME or ANY： 使用子查詢結(jié)果前對該結(jié)果進(jìn)行相應(yīng)的比較。
• NOT：上述關(guān)鍵字前使用 NOT 是結(jié)果取反灸撰。

列出最少有10名學(xué)生參加的課程的所有學(xué)生信息以及該課程的名字：

SELECT s.*, e1.CourseNo
FROM Student s NATURAL JOIN Enrol e1
WHERE e1.CourseNo IN
    (SELECT e2.CourseNo
     FROM Enrol e2
     GROUP BY e2.CourseNo
     HAVING COUNT(*) < 10);

列出所有至少參加一門課程的學(xué)生信息：

SELECT s.*
FROM Student s
WHERE EXISTS (SELECT *
              FROM Enrol e
              WHERE s.StudentID=e.StudentID);

列出所有沒有參加任何課程的學(xué)生信息：

SELECT s.*
FROM Student s
WHERE NOT EXISTS (SELECT *
                  FROM Enrol e
                  WHERE s.StudentID=e.StudentID);

列出在2016學(xué)年第二學(xué)期中泻骤，報名學(xué)生人數(shù)最多的課程：

SELECT e.CourseNo
FROM (SELECT e1.CourseNo, COUNT(*) AS NoOfStudents
      FROM Enrol e1
      WHERE e1.Semester = '2016 S2'
      GROUP BY e1.CourseNo) e
WHERE e.NoOfStudents =
     (SELECT MAX(e2.NoOfStudents)
      FROM (SELECT e1.CourseNo, COUNT(*) AS NoOfStudents
            FROM Enrol e1
            WHERE e1.Semester = '2016 S2'
            GROUP BY e1.CourseNo) e2);

列出在2016學(xué)年第二學(xué)期中，報名學(xué)生人數(shù)比至少一個其他課程要多的課程：

SELECT e.CourseNo
FROM (SELECT e1.CourseNo, COUNT(*) AS NoOfStudents
      FROM Enrol e1
      WHERE e1.Semester = '2016 S2'
      GROUP BY e1.CourseNo) e
WHERE e.NoOfStudents > ANY
     (SELECT e2.NoOfStudents
      FROM (SELECT e1.CourseNo, COUNT(*) AS NoOfStudents
            FROM Enrol e1
            WHERE e1.Semester = '2016 S2'
            GROUP BY e1.CourseNo) e2);

具體創(chuàng)建數(shù)據(jù)庫的代碼可以通過下面的鏈接得到：

https://github.com/OddUlrich/Experiment-Code/tree/master/SQL%20Select%20Practice

若想做更多關(guān)于查詢的練習(xí)梧奢，可以訪問下面的這個網(wǎng)站：

https://pgexercises.com/

參考資料：
連接 （join）：https://zh.wikipedia.org/wiki/%E8%BF%9E%E6%8E%A5