你可能不知道的 SQL 小筆記（查詢篇）

學．誌｜Chris Kang

Published in

不止數據｜Not Only Data

8 min readJan 3, 2020

在使用 SQL 時，可能會漏掉一些小觀念，這些觀念可能是在操作時會無意間忽略的。因此本篇文章特別整理了一些可能會被忽略的技巧。

選擇數據

DISTINCT 會限制所有的欄位，並不單單只是套用在跟隨著欄位。除非所有選定的欄位都一致，不然仍然會顯示部分不一致的欄位。
ORDER BY 應該要出現在最後一行，避免出現 Error。
ORDER BY 能夠進行複數欄位的排列，只要以逗號分隔即可。如果想要對多欄進行降序，必須在每一欄都指定一次，例如：

ORDER BY A_Column ASC, B_Column DESC;

篩選數據

AS 盡量於 SQL 中使用，可用於簡化欄位。
如果要檢驗某個區間的資料，可以使用 BETWEEN。用法為：

WHERE A_Column BETWEEN 10 AND 20。

要檢查是否有 NULL，可以使用 WHERE A_Col IS NULL。
在進行 WHERE 條件篩選時，為避免錯誤篩選，務必將複合條件以括號分開。如果沒有括號，SQL 的判斷順序為 () > AND > OR

WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') 
  AND prod_price >= 10;

IN 用來取得符合特定條件的資料，可用來取代 OR 進行重複的指令。
NOT 會加在條件前面，例如： WHERE NOT vend_id = 'DLL01'

用來查找特定字元的方式有（NULL 例外）：

LIKE：能夠找到匹配的資料
％：前方或後方不特定的字元長度（可為零），或是中間 WHERE prod_name LIKE 'F%y'
_：特定字元長度

拼接資料格：

拼接資料格（Concatenate），使用 || 或是 + 來連接資料：
用 RTRIM() 可以把輸出的空格全部取消。

SELECT vend_name + ' (' + vend_country + ')' -- Majority of DB   SELECT vend_name || ' (' || vend_country || ')'   
SELECT Concat(vend_name, ' (', vend_country, ')') -- MYSQL      Bear Emporium                                     (USA        )--AFTER RTRIM    
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'Bear Emporium (USA)

函數使用（Aggregation）：

COUNT(column) 會忽略 NULL，COUNT(*) 則不會忽略
對不同的列進行計算，默認會加上 ALL 在欄位前方，否則要加上 DISTINCT。
如果指定列名，用 COUNT()，會忽略其中 NULL 的值；相反地，用 COUNT(*) 則不忽略 NULL。
如果指定列名，則 DISTINCT 只能用 COUNT()，因為會忽略 NULL。
SELECT 可包含多個函數（如 AVG、MIN 等）。

群組（Group）

大多 DBS 不允許 GROUP BY 帶有可變長度的欄位，如字串。
如果在 SELECT 中使用表達式，則必須在 GROUP BY 中使用相同的表達式，不能用簡稱。
GROUP BY 必須出現在 WHERE 之後，ORDER BY 之前。
如果必須要在分組後在進行篩選，則使用 HAVING。

子查詢（Subquery）

子查詢不能相嵌太多層，會嚴重限制性能，而且有時子查詢也會有閱讀上的困難。

技巧差異：

使用 WHERE：

FROM Customers 
WHERE cust_id IN (SELECT cust_id 
                  FROM Orders 
                  WHERE order_num IN (SELECT order_num 
                                      FROM OrderItems
                                      WHERE prod_id = 'RGAN01'));

使用 SELECT：

SELECT cust_name, cust_state, 
      (SELECT COUNT(*) 
       FROM Orders 
       WHERE Orders.cust_id = Customers.cust_id) AS orders 
       FROM Customers 
ORDER BY cust_name;

WHERE Orders.cust_id = Customers.cust_id 完全限定列名，當名稱相同時，就必須用前綴來限定特定列名。

關連表 JOIN

主鍵（Primary Key）用於視為該表格中的唯一值，用以辨別個別資料的獨特性。
AS 所使用的別名可以使用在任何地方，無論是 SELECT 、 WHERE 或是 FROM。
創建關連的最簡單形式，就是把兩張表一起讀取（亦即，表的關連式會以 WHERE 子句來串連）：

# 三個表格關連
/* WHERE 前面兩個用於關連三個表格，最後一個則用於限定條件*/SELECT prod_name, vend_name, prod_price, quantity 
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
 AND OrderItems.prod_id = Products.prod_id
 AND order_num = 20007;

可以使用子查詢的方式來設定條件式，同樣也能夠透過自連結（Self-Join）進行兩次查詢。

可以自行測試自關連或是子查詢何者效能更好，以作為常態查詢方式。

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
 FROM Customers
 WHERE cust_contact = ‘Jim Jones’);-- Self Join
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
 AND c2.cust_contact = ‘Jim Jones’;

UNION 必須：

由兩條或兩條以上的 SELECT 語句組成
兩個 SELECT 選擇的列應該要完全一樣
列數據類型必須為可以兼容的類別，例如數值或日期。

UNION ALL 會返回兩個表設定條件的所有資料，因此可能會出現重複資料。UNION 幾乎總是用來完成多重 WHERE 的條件。

UNION 中有兩個高級關連方式：
EXCEPT：A 表存在但 B 表不存在的內容
INTERSECT：A、B 表都存在的內容

視圖（View Table）：

視圖是一種查詢快捷，同時也能滿足不同資料格式的呈現。
創建視圖

CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

以上就是 SQL 在查詢時，容易忘記的一些小觀念，本文可以作為參考與複習使用。

謝謝你／妳，願意把我的文章閱讀完

如果你喜歡筆者在 Medium 的文章，可以拍個手（Claps），最多可以按五個喔！也歡迎你分享給你覺得有需要的朋友們。

參考資料：

SQL 必知必會
SQL Join的一些总结｜黄钧航(JKhuang)