SQL 中的集合運算元 UNION、INTERSECT 和 EXCEPT 提供了強大的資料操作能力，可以幫助開發者有效地合併、比較和篩選資料集。理解這些運算元的工作原理和應用場景對於提升資料函式庫操作效率至關重要。UNION 運算元可以將多個查詢結果合併成一個結果集，去除重複資料；INTERSECT 運算元可以找出多個資料集的交集，也就是共同擁有的資料；EXCEPT 運算元則可以找出第一個資料集中存在但第二個資料集中不存在的資料，也就是資料集的差集。這些運算元在資料分析、資料比對和資料清洗等方面都有廣泛的應用。例如，可以使用 UNION 合併來自不同資料表的資料，使用 INTERSECT 找出同時滿足多個條件的資料，使用 EXCEPT 找出特定資料的差異性。此外，在使用這些運算元時，需要注意資料型別的一致性和運算元執行的順序，以確保得到正確的結果。
資料集操作：玄貓的集合理論與 SQL 實戰
集合理論快速導覽：玄貓的觀點
在資料函式庫的世界裡，集合操作是不可或缺的一環。作為玄貓，我認為理解集合理論對於資料函式庫操作至關重要。簡單來說，集合理論就是研究集合（一堆積東西）之間關係的數學分支。
聯集（Union）：合併同類別項
聯集就像是將兩個籃子裡的東西倒在一起，然後拿掉重複的。例如：
A = {1, 2, 4, 7, 9}
B = {3, 5, 7, 9}
A 聯集 B = {1, 2, 3, 4, 5, 7, 9}
交集（Intersection）：尋找共同點
交集則是找出兩個籃子裡都有的東西。以上面的例子來說：
A 交集 B = {7, 9}
差集（Except/Minus）：獨一無二的你
差集是指從一個籃子裡拿出另一個籃子裡有的東西，剩下的就是差集。
A 差集 B = {1, 2, 4}
B 差集 A = {3, 5}
SQL 中的集合運算：玄貓的實戰經驗
SQL 提供了 UNION、INTERSECT 和 EXCEPT 這三個集合運算元，讓資料函式庫操作更加靈活。
UNION：合併結果集
UNION 運算元用於合併兩個或多個 SELECT 陳述式的結果集。玄貓提醒大家，使用 UNION 時要注意以下限制：
- 所有 SELECT 陳述式必須有相同數量的欄位。
- 對應欄位的資料型別必須相容。
以下是一個簡單的 UNION 範例：
SELECT 1 AS numeric_col, 'ABC' AS string_col
UNION
SELECT 2 AS numeric_col, 'XYZ' AS string_col;
結果：
+-------------+------------+
| NUMERIC_COL | STRING_COL |
|-------------+------------|
| 1 | ABC |
| 2 | XYZ |
+-------------+------------+
如果 SELECT 陳述式的欄位數量不一致，Snowflake 會丟擲錯誤：
SELECT 1 AS numeric_col, 'ABC' AS string_col
UNION
SELECT 2 AS numeric_col, 'XYZ' AS string_col, 99 AS extra_col;
錯誤訊息：
SQL compilation error:
invalid number of result columns for set operator ...
如果欄位資料型別不相容，也會出現錯誤：
SELECT 1 AS numeric_col, 'ABC' AS string_col
UNION
SELECT 'XYZ' AS numeric_col, 2 AS string_col;
錯誤訊息：
Numeric value 'ABC' is not recognized
UNION 預設會移除重複的資料列。如果想要保留重複資料列，可以使用 UNION ALL：
SELECT integer_val
FROM (VALUES (1), (2), (4), (7), (9)) AS set_a (integer_val)
UNION ALL
SELECT integer_val
FROM (VALUES (3), (5), (7), (9)) AS set_b (integer_val);
結果：
+-------------+
| INTEGER_VAL |
|-------------|
| 1 |
| 2 |
| 4 |
| 7 |
| 9 |
| 3 |
| 5 |
| 7 |
| 9 |
+-------------+
練習題：玄貓帶你動動腦
練習 3-3
假設我們有寵物主人（Pet_Owner）和寵物（Pet）兩個資料表。請寫一個查詢，傳回每個寵物主人的名字，以及他們所擁有的寵物名字（如果有的話）。結果必須包含 Pet_Owner 資料表中的所有資料列。
Pet_Owner
+----------+------------+
| OWNER_ID | OWNER_NAME |
+----------+------------+
| 1 | John |
| 2 | Cindy |
| 3 | Laura |
| 4 | Mark |
+----------+------------+
Pet
+--------+----------+----------+
| PET_ID | OWNER_ID | PET_NAME |
+--------+----------+----------+
| 101 | 1 | Fluffy |
| 102 | 3 | Spot |
| 103 | 4 | Rover |
+--------+----------+----------+
練習 3-4
延續之前的練習，假設寵物可以有零個、一個或兩個主人。
Pet_Owner
+----------+------------+
| OWNER_ID | OWNER_NAME |
+----------+------------+
| 1 | John |
| 2 | Cindy |
| 3 | Laura |
| 4 | Mark |
+----------+------------+
Pet
+--------+-----------+-----------+----------+
| PET_ID | OWNER_ID1 | OWNER_ID2 | PET_NAME |
+--------+-----------+-----------+----------+
| 101 | 1 | NULL | Fluffy |
| 102 | 3 | 2 | Spot |
| 103 | 4 | 1 | Rover |
| 104 | NULL | NULL | Rosco |
+--------+-----------+-----------+----------+
請傳回每隻寵物的名字，以及主人 #1 和主人 #2 的名字。結果集應該有四列（每隻寵物一列）。有些主人的名字可能會是 NULL。
SQL集合運算：Intersect、Except的實戰解析
身為一個資料函式庫愛好者，集合運算元在SQL中扮演著重要的角色。它們能幫助我們處理多個查詢結果，找出交集、差集等，進而解決複雜的資料分析問題。今天，玄貓就來探討INTERSECT和EXCEPT這兩個集合運算元，並分享一些實戰經驗。
Union之外的選擇：Intersect的妙用
在資料函式庫的世界中，UNION 運算元大家肯定不陌生，它可以將兩個或多個查詢的結果合併成一個結果集。但如果我們想要找出兩個結果集的共同部分呢？這時候，INTERSECT 運算元就派上用場了。
讓我們先看一個簡單的例子。假設我們有兩個數字集合：
集合 A: {1, 2, 4, 7, 9} 集合 B: {3, 5, 7, 9}
如果我們想要找出 A 和 B 的交集，可以使用以下的 SQL 語法：
SELECT integer_val
FROM (VALUES (1), (2), (4), (7), (9)) AS set_a (integer_val)
INTERSECT
SELECT integer_val
FROM (VALUES (3), (5), (7), (9)) AS set_b (integer_val);
這段程式碼會回傳：
| INTEGER_VAL |
|-------------|
| 7 |
| 9 |
內容解密
- SELECT integer_val: 選擇名為 integer_val 的欄位。
- FROM (VALUES (1), (2), (4), (7), (9)) AS set_a (integer_val): 建立一個名為 set_a 的臨時表，其中包含整數值 1, 2, 4, 7 和 9。
- INTERSECT: 此運算元會傳回兩個 SELECT 陳述式共有的相異值。
- FROM (VALUES (3), (5), (7), (9)) AS set_b (integer_val): 建立一個名為 set_b 的臨時表，其中包含整數值 3, 5, 7 和 9。
結果顯示，7 和 9 是 A 和 B 共同擁有的元素。
案例分析：找出忠實客戶
為了更深入理解 INTERSECT 的應用，讓玄貓來分享一個實際的案例。假設我們有一個 Orders 資料表，記錄了客戶的訂單資訊。我們想要找出在 1992 年和 1993 年都下過訂單，與訂單總金額超過 350,000 元的客戶。
以下是 SQL 查詢的範例：
SELECT DISTINCT o_custkey
FROM orders
WHERE o_totalprice > 350000
AND DATE_PART(year, o_orderdate) = 1992
INTERSECT
SELECT DISTINCT o_custkey
FROM orders
WHERE o_totalprice > 350000
AND DATE_PART(year, o_orderdate) = 1993;
這段程式碼會回傳：
| O_CUSTKEY |
|-----------|
| 100510 |
內容解密
- SELECT DISTINCT o_custkey: 選擇 orders 表格中不同的 o_custkey（客戶鍵）值。
- FROM orders: 指定要從 orders 表格中選取資料。
- WHERE o_totalprice > 350000: 篩選出 o_totalprice（訂單總價）大於 350000 的訂單。
- AND DATE_PART(year, o_orderdate) = 1992: 進一步篩選出 o_orderdate（訂單日期）年份為 1992 的訂單。
- INTERSECT: SQL 運算元，它會傳回兩個 SELECT 陳述式共有的相異值。
- FROM orders WHERE o_totalprice > 350000 AND DATE_PART(year, o_orderdate) = 1993: 第二個 SELECT 陳述式，它會從 orders 表格中選取 o_totalprice 大於 350000 與 o_orderdate 年份為 1993 的不同 o_custkey 值。
結果顯示，只有一個客戶（100510）在 1992 年和 1993 年都下了大額訂單。這個資訊對於行銷團隊來說非常寶貴，他們可以針對這些忠實客戶制定更精準的行銷策略。
找出獨特之處：Except 的應用
與 INTERSECT 相反，EXCEPT 運算元可以找出存在於第一個結果集，但不存在於第二個結果集的資料。簡單來說，它就是求差集。
讓我們再次使用之前的數字集合 A 和 B：
集合 A: {1, 2, 4, 7, 9} 集合 B: {3, 5, 7, 9}
如果我們想要找出存在於 A 但不存在於 B 的元素，可以使用以下的 SQL 語法：
SELECT integer_val
FROM (VALUES (1), (2), (4), (7), (9)) AS set_a (integer_val)
EXCEPT
SELECT integer_val
FROM (VALUES (3), (5), (7), (9)) AS set_b (integer_val);
這段程式碼會回傳：
| INTEGER_VAL |
|-------------|
| 1 |
| 2 |
| 4 |
內容解密
- SELECT integer_val: 選擇名為 integer_val 的欄位。
- FROM (VALUES (1), (2), (4), (7), (9)) AS set_a (integer_val): 建立一個名為 set_a 的臨時表，其中包含整數值 1, 2, 4, 7 和 9。
- EXCEPT: 此運算元會傳回出現在第一個 SELECT 陳述式中，但未出現在第二個 SELECT 陳述式中的相異值。
- FROM (VALUES (3), (5), (7), (9)) AS set_b (integer_val): 建立一個名為 set_b 的臨時表，其中包含整數值 3, 5, 7 和 9。
結果顯示，1、2 和 4 是 A 獨有的元素。
如果我們交換 A 和 B 的順序，結果就會不同：
SELECT integer_val
FROM (VALUES (3), (5), (7), (9)) AS set_b (integer_val)
EXCEPT
SELECT integer_val
FROM (VALUES (1), (2), (4), (7), (9)) AS set_a (integer_val);
這段程式碼會回傳：
| INTEGER_VAL |
|-------------|
| 3 |
| 5 |
內容解密
- SELECT integer_val: 選擇名為 integer_val 的欄位。
- FROM (VALUES (3), (5), (7), (9)) AS set_b (integer_val): 建立一個名為 set_b 的臨時表，其中包含整數值 3, 5, 7 和 9。
- EXCEPT: 此運算元會傳回出現在第一個 SELECT 陳述式中，但未出現在第二個 SELECT 陳述式中的相異值。
- FROM (VALUES (1), (2), (4), (7), (9)) AS set_a (integer_val): 建立一個名為 set_a 的臨時表，其中包含整數值 1, 2, 4, 7 和 9。
結果顯示，3 和 5 是 B 獨有的元素。
玄貓提醒一下，UNION 和 INTERSECT 的結果與集合的順序無關，但 EXCEPT 的結果會因為集合的順序而有所不同。
找出年度新客戶：Except 的實戰
讓我們回到 Orders 資料表。這次，我們想要找出在 1992 年下了大額訂單，但在 1993 年沒有下大額訂單的客戶。
以下是 SQL 查詢的範例：
SELECT DISTINCT o_custkey
FROM orders
WHERE o_totalprice > 350000
AND DATE_PART(year, o_orderdate) = 1992
EXCEPT
SELECT DISTINCT o_custkey
FROM orders
WHERE o_totalprice > 350000
AND DATE_PART(year, o_orderdate) = 1993;
這段程式碼會回傳 454 位客戶。
內容解密
- SELECT DISTINCT o_custkey: 選擇 orders 表格中不同的 o_custkey（客戶鍵）值。
- FROM orders: 指定要從 orders 表格中選取資料。
- WHERE o_totalprice > 350000: 篩選出 o_totalprice（訂單總價）大於 350000 的訂單。
- AND DATE_PART(year, o_orderdate) = 1992: 進一步篩選出 o_orderdate（訂單日期）年份為 1992 的訂單。
- EXCEPT: SQL 運算元，它會傳回出現在第一個 SELECT 陳述式中，但未出現在第二個 SELECT 陳述式中的相異值。
- FROM orders WHERE o_totalprice > 350000 AND DATE_PART(year, o_orderdate) = 1993: 第二個 SELECT 陳述式，它會從 orders 表格中選取 o_totalprice 大於 350000 與 o_orderdate 年份為 1993 的不同 o_custkey 值。
如果我們交換 1992 年和 1993 年的順序，查詢會回傳 412 位客戶。這些客戶在 1993 年下了大額訂單，但在 1992 年沒有。
集合運算規則：排序與優先順序
在使用集合運算元時，有幾個規則需要注意：
- 排序： 如果想要對結果進行排序，只能在整個查詢的最後使用一個
ORDER BY子句。而與，ORDER BY子句中使用的欄位名稱必須來自第一個查詢。 - 優先順序： 如果查詢中包含多個集合運算元，需要考慮運算元的優先順序。
為了避免混淆，玄貓建議在每個查詢中使用相同的欄位別名，並在 ORDER BY 子句中使用別名。
找出資料交集：SQL集合運算技巧
在資料函式庫操作中，集合運算元扮演著重要的角色，它們可以幫助我們找出多個資料集之間的交集、聯集或差集。本文將探討 SQL 中的 UNION、INTERSECT 和 EXCEPT 運算元，並提供實際範例，讓你更瞭解如何運用這些工具來分析資料。
集合運算元的基本概念
集合運算元主要用於處理兩個或多個具有相同欄位結構的資料集。它們可以幫助我們找出資料集之間的關聯性，例如：
UNION：聯集，將兩個資料集合併，去除重複的資料。INTERSECT：交集，找出兩個資料集中共有的資料。EXCEPT：差集，找出第一個資料集中有，但第二個資料集中沒有的資料。
範例解析：找出數值集合的差異
假設我們有兩個數值集合 A 和 B，我們可以使用 EXCEPT 運算元找出 A 中有但 B 中沒有的數值，以及 B 中有但 A 中沒有的數值。
以下 SQL 語法示範如何找出兩個數值集合的差異：
SELECT integer_val
FROM (VALUES (1), (2), (4), (7), (9)) AS set_a (integer_val)
EXCEPT
SELECT integer_val
FROM (VALUES (3), (5), (7), (9)) AS set_b (integer_val)
UNION
SELECT integer_val
FROM (VALUES (3), (5), (7), (9)) AS set_b (integer_val)
EXCEPT
SELECT integer_val
FROM (VALUES (1), (2), (4), (7), (9)) AS set_a (integer_val);
內容解密：
VALUES子句： 用於建立臨時的資料集，例如(VALUES (1), (2), (4), (7), (9))建立一個包含數值 1, 2, 4, 7, 9 的資料集。AS子句： 為資料集指定一個別名，例如AS set_a (integer_val)將資料集命名為set_a，並指定欄位名稱為integer_val。EXCEPT運算元： 找出第一個資料集中有，但第二個資料集中沒有的資料。UNION運算元： 將兩個資料集合併，去除重複的資料。
玄貓小提示：
當使用多個集合運算元時，請務必使用括號明確指定運算順序，以確保結果符合預期。
實戰演練：找出以特定字母開頭的區域與國家
假設我們有兩個資料表 Region 和 Nation，我們可以使用 UNION 運算元找出所有以字母 A 開頭的區網域名稱和國家名稱。
以下 SQL 語法示範如何實作：
SELECT r_name
FROM Region
WHERE r_name LIKE 'A%'
UNION
SELECT n_name
FROM Nation
WHERE n_name LIKE 'A%';
內容解密：
LIKE運算元： 用於模糊比對，例如LIKE 'A%'表示找出所有以字母 A 開頭的字串。%符號： 代表任意字元。
玄貓建議：
在實際應用中，可以根據需求調整 WHERE 子句的條件，找出符合特定條件的資料。
Snowflake 資料型別：VARCHAR 的妙用
在 Snowflake 中，VARCHAR 是一種非常實用的資料型別，它可以儲存可變長度的字串，最多可達 16MB。無論是儲存電影名稱、使用者評論或任何其他文字資料，VARCHAR 都能勝任。
favorite_movie VARCHAR(100)
上述語法定義了一個名為 favorite_movie 的欄位，它可以儲存最多 100 個字元的字串。
玄貓提醒：
雖然 Snowflake 支援多種字串資料型別，但建議在建立新資料表時，統一使用 VARCHAR，以保持一致性。
字串處理小技巧
在 SQL 中，我們可以使用單引號 (') 作為字串的定界符。如果字串中包含單引號，可以使用兩個單引號 ('') 來表示。
SELECT 'you haven''t reached the end yet' AS output_string;
此外，我們還可以使用兩個錢字元號 ($$) 作為定界符，這樣可以避免轉義字元的問題。
SELECT $$string with 4 single quotes ' ' ' '$$ AS output_string;
資料操作：新增、修改與刪除
除了查詢資料外，SQL 還提供了新增 (INSERT)、修改 (UPDATE) 和刪除 (DELETE) 資料的語法。這些語法可以幫助我們維護資料函式庫中的資料。
玄貓溫馨提示：
在執行 UPDATE 和 DELETE 語法時，請務必謹慎，以免誤操作導致資料遺失。
