1. 子查詢

子查詢： 任何一個充當另一個SQL語句的一部分的 SELECT 查詢語句都是子查詢，子查詢是一個很有用的技巧。子查詢的層級用括號實現。

MySQL執行時會先執行括號內的子查詢（內查詢），將獲得的結果返回給外查詢，子查詢不僅可用在 WHERE …… 中，也可用在 SELECT …… 或 FROM …… 等子句中

SELECT*
FROM products
WHERE unit_price > (SELECT unit_priceFROM productsWHERE name LIKE '%Lettuce%'
)SELECT*
FROM employees
WHERE salary > (SELECT AVG(salary) AS average_salaryFROM employees
)

2. IN運算符

當需要判定的條件中有多個數值時，用到IN和NOT IN運算符。（可以是字符也可以是數字）

SELECT *
FROM products
WHERE product_id NOT IN(SELECT DISTINCT product_id  -- distinct關鍵字用于屬性名前，得到不重復值FROM order_items
)

3. 子查詢VS連接

子查詢（Subquery）是將一張表的查詢結果作為另一張表的查詢依據并層層嵌套，其實也可以先將這些表連接（Join）合并成一個包含所需全部信息的詳情表再直接在詳情表里篩選查詢。兩種方法一般是可互換的，具體用哪一種取決于性能（Performance）和可讀性（readability）。

SELECT *
FROM products
LEFT JOIN order_items USING (product_id)
WHERE order_id IS NULL  
-- 等同于在WHERE中采用子查詢SELECTDISTINCT customer_id,first_name,last_name
FROM customers
LEFT JOIN orders USING (customer_id)
LEFT JOIN order_items USING (order_id)
WHERE product_id = 3    
-- 對于多表之間的查詢，先連接成大表，然后再查詢可讀性更強SELECT customer_id,first_name,last_name
FROM customers
WHERE customer_id IN (SELECT o.customer_idFROM order_items oiJOIN orders o USING (order_id)WHERE product_id = 3
)  -- 采用子查詢和外連接共同作用來進行多表查詢

4. ALL關鍵字

(MAX (……)) 和 > ALL(……) 等效可互換：“比這里面最大的還大” = “比這里面的所有的都大”
從以下代碼來進行對比：

SELECT *
FROM invoices
WHERE invoice_total > (SELECT MAX(invoice_total) -- 用于表中的屬性值計算最大值FROM invoicesWHERE client_id = 3
)SELECT *
FROM invoices
WHERE invoice_total > ALL ( -- 用于子查詢后結構的計算最大值SELECT invoice_totalFROM invoicesWHERE client_id = 3
)

5. ANY關鍵字

同SOME，表示其中任何一個
= ANY (……) 與 IN (……) 等效；
< ANY/SOME (……) 與 < (MIN (……)) 等效

SELECT *
FROM clients
WHERE client_id = ANY (    -- 可采用IN或= SOME來替換SELECT client_idFROM invoicesGROUP BY client_idHAVING COUNT(*) > 2
)

6. 相關子查詢

• 非相關子查詢：子查詢先查出整體的某平均值或滿足某些條件的一列id，作為主查詢的篩選依據，這種子查詢與主查詢無關，會先一次性得出查詢結果再返回給主查詢供其使用。
• 關子查詢：子查詢是依賴主查詢的，子查詢的判定中需要引入主查詢的屬性值（需要為主查詢中表名命別名），每一條記錄的主查詢對應的子查詢結果也會發生改變。這種關聯查詢是在主查詢的每一行/每一條記錄層面上依次進行的，另外也正因為這一點，相關子查詢會比非關聯查詢執行起來慢一些。

Note:偽代碼其實就是注釋的表達方式，一種按照程序執行步驟編寫的注釋，中英文都可以

SELECT *
FROM employees e
WHERE salary > (  -- 相當于執行循環計算，子查詢用到了主查詢中的屬性值SELECT AVG(salary)FROM employeesWHERE office_id = e.office_id
)

7. EXISTS運算符

• IN + 子查詢 等效于 EXIST + 相關子查詢，如果前者子查詢的結果集過大占用內存，用后者逐條驗證更有效率。
• EXIST()本質上是根據是否為空返回TRUE和FALSE，而IN 是獲取返回的數值
• EXIST也可以加NOT取反

SELECT * 
FROM clients
WHERE client_id IN (  -- 判斷的是某些數值SELECT DISTINCT client_idFROM invoices
)SELECT DISTINCT client_id,name
-- 外連接可能產生大量重復項，需要剔除
FROM clients
JOIN invoices USING (client_id)SELECT * 
FROM clients c   -- 數據庫本質上就是對表格數據的逐條判斷篩選，然后聯系到大量關系型數據庫表格
WHERE EXISTS (
-- 類似判斷語句，符合條件返回ture,則將此條記錄輸出
-- 對于clients表格中記錄進行輸出，不會產生重復SELECT *FROM invoicesWHERE client_id = c.client_id
)

NOTE:

1. EXISTS(…) 函數相當于是前置的 … IS NULL（共同點：都是根據是否為空返回布林值）
2. WHERE 確實是逐條驗證篩選行/記錄的
3. EXISTS也是相關子查詢
4. 對于大電商來說，如果用IN+子查詢法，子查詢可能會返回一個百萬量級的產品列表，這種情況還是用EXIST+相關子查詢逐條驗證法更有效率

8. SELECT子句中得子查詢

不僅WHERE篩選條件里可以用子查詢，SELECT選擇子句和FROM來源表子句也能用子查詢。

SELECT選擇語句是用來確定查詢結果選擇包含哪些字段，每個字段都可以是一個表達式，而每個字段表達式里的元素除了可以是原始的列，具體的數值，也同樣可以是其它各種花里胡哨的子查詢的結果。

SELECT invoice_id,invoice_total,(SELECT AVG(invoice_total) FROM invoices) AS invoice_average,-- '123' AS Test 命名屬性值，同時給其賦值。-- 當需要記錄值逐條輸出時，不能直接用聚合函數。必須和GROUP BY語句聯合輸出/*用括號+子查詢改變順序，【子查詢 (SELECT AVG(invoice_total) FROM invoices)是作為一個數值結果 152.388235 加入主查詢語句的】*/invoice_total - (SELECT invoice_average) AS difference/*SELECT表達式里要用原列名，不能直接用別名invoice_average要用列別名的話用子查詢（SELECT 同級的列別名）即可*/
FROM invoices
Note:可以理解為給屬性列來賦值
SELECT client_id,name,(SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales,-- 子查詢相對主查詢clients，會進行5次循環查詢。若用GRUOP BY則只會產生4組數值(SELECT AVG(invoice_total) FROM invoices) AS average,(SELECT total_sales - average) AS difference
FROM clients c

Note:形成表格過程中一定注意行中列數要匹配。

9. FROM子句中得子查詢

子查詢的結果同樣可以充當一個“虛擬表”作為FROM語句中的來源表，即將篩選查詢結果作為來源再進行進一步的篩選查詢。但注意只有在子查詢不太復雜時進行這樣的嵌套，否則最好用后面講的視圖先把子查詢結果儲存起來再使用。

復雜的子查詢再嵌套進FROM里會讓整個查詢看起來過于復雜，最好是將子查詢結果儲存為視圖，然后再直接使用該視圖作為來源表。（形成新表，然后查詢。）

FROM子句中的子查詢一般不用。