SQL - 使用集合運算子

集合運算子用於連線兩個（或多個）SELECT 語句的結果。Oracle 11g 中可用的集合運算子有 UNION、UNION ALL、INTERSECT 和 MINUS。

UNION 集合運算子返回兩個 SELECT 語句的組合結果。從本質上講，它會從結果中刪除重複項，即每個重複結果只列出一行。要解決此問題，請使用 UNION ALL 集合運算子，它保留最終結果中的重複項。INTERSECT 僅列出兩個 SELECT 查詢中共同存在的記錄；MINUS 集合運算子從輸出中刪除第二個查詢的結果，如果這些結果也存在於第一個查詢的結果中。INTERSECT 和 MINUS 集合運算產生不重複的結果。

所有集合運算子之間具有相同的優先順序。相反，在查詢執行期間，Oracle 從左到右或從上到下開始評估。如果顯式使用括號，則順序可能不同，因為括號將優先於懸掛運算子。

要點 -

所有參與的 SELECT 語句必須選擇相同數量的列。顯示中使用的列名取自第一個查詢。
列列表的資料型別必須與 Oracle 相容/隱式可轉換。如果元件查詢中對應的列屬於不同的資料型別組，則 Oracle 不會執行隱式型別轉換。例如，如果第一個元件查詢中的一列的資料型別為 DATE，而第二個元件查詢中對應的列的資料型別為 CHAR，則 Oracle 不會執行隱式轉換，而是會引發 ORA-01790 錯誤。
必須使用位置排序來排序結果集。使用集合運算子不允許對單個結果集進行排序。ORDER BY 可以出現在查詢的末尾。例如，
UNION 和 INTERSECT 運算子是可交換的，即查詢的順序並不重要；它不會更改最終結果。
從效能方面來看，UNION ALL 比 UNION 表現更好，因為不會浪費資源來過濾重複項和排序結果集。
集合運算子可以是子查詢的一部分。
集合運算子不能用於包含 TABLE 集合表示式的 SELECT 語句中。
LONG、BLOB、CLOB、BFILE、VARRAY 或巢狀表不允許在集合運算子中使用。更新子句不允許與集合運算子一起使用。

UNION

當使用 UNION 運算子連線多個 SELECT 查詢時，Oracle 會顯示所有組合的 SELECT 查詢的組合結果，在刪除所有重複項並按排序順序（預設情況下為升序）顯示後，不會忽略 NULL 值。

考慮以下使用 UNION 運算子連線的五個查詢。最終的組合結果集包含所有 SQL 的值。請注意資料去重和排序。

SELECT 1 NUM FROM DUAL
UNION
SELECT 5 FROM DUAL 
UNION
SELECT 3 FROM DUAL
UNION
SELECT 6 FROM DUAL
UNION
SELECT 3 FROM DUAL;

NUM
-------
1
3
5
6

需要注意的是，SELECT 查詢中選擇的列必須是相容的資料型別。當違反規則時，Oracle 會丟擲一個錯誤訊息。

SELECT TO_DATE('12-OCT-03') FROM DUAL
UNION
SELECT '13-OCT-03' FROM DUAL;

SELECT TO_DATE('12-OCT-03') FROM DUAL
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

UNION ALL

UNION 和 UNION ALL 的功能相似，但略有不同。但 UNION ALL 會給出結果集，而不會刪除重複項和排序資料。例如，在上面的查詢中，UNION 被替換為 UNION ALL 以檢視效果。

考慮 UNION 部分中演示的查詢。注意輸出的不同之處，該輸出是在沒有排序和去重的情況下生成的。

SELECT 1 NUM FROM DUAL
UNION ALL
SELECT 5 FROM DUAL 
UNION ALL
SELECT 3 FROM DUAL
UNION ALL
SELECT 6 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL;

NUM
-------
1
5
3
6
3

INTERSECT

使用 INTERSECT 運算子，Oracle 會顯示兩個 SELECT 語句中共同存在的行，不包含重複項，並且資料按排序順序（預設情況下為升序）排列。

例如，以下 SELECT 查詢檢索部門 10 和 20 中共同存在的薪資。根據 ISO SQL 標準，INTERSECT 在集合運算子的評估優先順序中高於其他運算子，但 Oracle 尚未納入此功能。

SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 10
INTRESECT
SELECT SALARY 
FROM employees
WHERE DEPARTMENT_ID = 20

SALARY
---------
1500
1200
2000

MINUS

Minus 運算子顯示第一個查詢中存在但在第二個查詢中不存在的行，不包含重複項，並且資料預設按升序排列。

SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 10
MINUS
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 20;

JOB_ID
-------------        
HR
FIN
ADMIN

匹配 SELECT 語句

可能存在以下情況：複合 SELECT 語句可能具有不同的選擇列計數和資料型別。因此，為了顯式匹配列列表，會在缺失的位置插入 NULL 列，以便匹配每個 SELECT 語句中選擇列的計數和資料型別。對於數字列，也可以用零代替以匹配查詢中選擇的列的型別。

在下面的查詢中，員工姓名（varchar2）和位置 ID（數字）的資料型別不匹配。因此，由於相容性問題，執行以下查詢將引發錯誤。

SELECT DEPARTMENT_ID "Dept", first_name "Employee"
FROM employees
UNION
SELECT DEPARTMENT_ID, LOCATION_ID
FROM departments;

ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

顯式地，可以透過用 NULL 替換位置 ID 和員工姓名來匹配列。

SELECT DEPARTMENT_ID "Dept", first_name "Employee", NULL "Location"
FROM employees
UNION
SELECT DEPARTMENT_ID, NULL "Employee", LOCATION_ID
FROM departments;

在集合運算中使用 ORDER BY 子句

ORDER BY 子句只能出現在包含複合 SELECT 語句的查詢的末尾。這意味著單個 SELECT 語句不能具有 ORDER BY 子句。此外，排序只能基於出現在第一個 SELECT 查詢中的列。因此，建議使用列位置對複合查詢進行排序。

下面的複合查詢統一來自兩個部門的結果，並按 SALARY 列排序。

SELECT employee_id, first_name, salary
FROM employees
WHERE department_id=10
UNION
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id=20
ORDER BY 3;

列印頁面