msyql中子查詢IN，EXISTS，ANY，ALL，UNION介紹

在mysql中子查詢是將一個查詢語句嵌套在另一個查詢語句中內層查詢語句的查詢結果，可以為外層查詢語句提供查詢條件,mysql中常用的子查詢IN，EXISTS，ANY，ALL，UNION，下面我一一介紹介紹。

因為在特定情況下，一個查詢語句的條件需要另一個查詢語句來獲取

帶IN關鍵字的子查詢

IN 運算符用於 WHERE 表達式中，以列表項的形式支持多個選擇，語法如下：

WHERE column IN (value1,value2,...)
WHERE column NOT IN (value1,value2,...)
當 IN 前面加上 NOT 運算符時，表示與 IN 相反的意思，即不在這些列表項內選擇。

 

 代碼如下 復制代碼

查詢

SELECT ID,NAME FROM A　 WHERE　ID IN (SELECT AID FROM B)//查詢B表中AID的記錄
SELECT ID,NAME FROM A WHERE　ID　NOT IN (SELECT AID FROM B) 意思和上面相反

刪除

delete from articles where id in (1,2,3);  //刪除id=1,id=2,id=3的記錄
delete from articles where id not in (1);//刪除id!=1的記錄

此處首先查詢出department表中所有d_id字段的信息，並將結果作為條件

接著查詢employee表中以d_id為條件的所有字段信息

NOT IN的效果與上面剛好相反
 

帶比較運算符的子查詢

 代碼如下 復制代碼
mysql> SELECT d_id, d_name FROM department
    -> WHERE d_id!=
    -> (SELECT d_id FROM employee WHERE age=24);
+------+-----------+
| d_id | d_name    |
+------+-----------+
| 1002 | 生產部    |
| 1003 | 銷售部    |
+------+-----------+

2 rows in set (0.00 sec)這裡查詢出了哪些部門沒有年齡為24歲的員工，看起來有點復雜

此外，運算符還有很多，這裡不再贅述

 

帶EXISTS關鍵字的子查詢

MySQL EXISTS 和 NOT EXISTS 子查詢語法如下：

SELECT ... FROM table WHERE  EXISTS (subquery)
該語法可以理解為：將主查詢的數據，放到子查詢中做條件驗證，根據驗證結果（TRUE 或 FALSE）來決定主查詢的數據結果是否得以保留。

 代碼如下 復制代碼
mysql> SELECT * FROM employee
    -> WHERE EXISTS
    -> (SELECT d_name FROM department WHERE d_id=1004);
Empty set (0.00 sec)

此處內層循環並沒有查詢到滿足條件的結果，因此返回false，外層查詢不執行

NOT EXISTS剛好與之相反

當然，EXISTS關鍵字可以與其他的查詢條件一起使用

條件表達式與EXISTS關鍵字之間用AND或者OR來連接

 代碼如下 復制代碼

mysql> SELECT * FROM employee
    -> WHERE age>24 AND EXISTS
    -> (SELECT d_name FROM department WHERE d_id=1003);

提示
•EXISTS (subquery) 只返回 TRUE 或 FALSE，因此子查詢中的 SELECT * 也可以是 SELECT 1 或其他，官方說法是實際執行時會忽略 SELECT 清單，因此沒有區別。
•EXISTS 子查詢的實際執行過程可能經過了優化而不是我們理解上的逐條對比，如果擔憂效率問題，可進行實際檢驗以確定是否有效率問題。
•EXISTS 子查詢往往也可以用條件表達式、其他子查詢或者 JOIN 來替代，何種最優需要具體問題具體分析

帶ANY關鍵字的子查詢

ANY關鍵字表示滿足其中任一條件

 

 代碼如下 復制代碼

mysql> SELECT * FROM employee
    -> WHERE d_id!=ANY
    -> (SELECT d_id FROM department);

4 rows in set (0.00 sec)

帶ALL關鍵字的子查詢
ALL關鍵字表示滿足其中所有條件

 

 代碼如下 復制代碼 mysql> SELECT * FROM employee
    -> WHERE d_id>=ALL
    -> (SELECT d_id FROM department);

1 row in set (0.00 sec)暫時不明白這兩條語句是什麼意思，到時候再補上

 
UNION合並查詢結果

MySQL UNION 用於把來自多個 SELECT 語句的結果組合到一個結果集合中。語法為：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
...
在多個 SELECT 語句中，對應的列應該具有相同的字段屬性，且第一個 SELECT 語句中被使用的字段名稱也被用於結果的字段名稱。

UNION 與 UNION ALL 的區別
當使用 UNION 時，MySQL 會把結果集中重復的記錄刪掉，而使用 UNION ALL ，MySQL 會把所有的記錄返回，且效率高於 UNION。

 代碼如下 復制代碼 mysql> SELECT d_id FROM employee
    -> UNION
    -> SELECT d_id FROM department;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1004 |
| 1003 |
+------+

4 rows in set (0.00 sec)合並比較好理解，也就是將多個查詢的結果合並在一起，然後去除其中的重復記錄

如果想保存重復記錄可以使用UNION ALL語句