OCP課程9：SQL之使用集合運算子
1、集合運算子概述
![clipboard[1]](http://img.blog.itpub.net/blog/attachment/201511/25/28536251_14484141776zwP.png "clipboard[1]")
- 除了union all，重複行會被自動刪除
- 除了union all，自動以第一列升序排序
- 集合操作結果的列名來自於第一個查詢
2、union運算子
把兩個查詢的結果加起來並去掉重複的記錄
例子：查詢員工在公司擔任過的所有職位
SQL> select employee_id,job_id from employees
2 union
3 select employee_id,job_id from job_history;
EMPLOYEE_ID JOB_ID
----------- ----------
206 AC_ACCOUNT
115 rows selected.
這裡返回115行，說明有部分是重複的，也就是有的員工同一個職位做過2次，被去掉了，同時結果預設對第一列升序排序，由於排序會使用PGA裡面的排序區或者臨時把空間，如果生產環境中有大量的集合操作，要注意臨時表空間的增長情況。
3、union all運算子
把兩個查詢的結果加起來並保留重複的記錄
例子：查詢員工在公司呆過的所有部門
SQL> select employee_id,job_id,department_id from employees
2 union all
3 select employee_id,job_id,department_id from job_history
4 order by employee_id;
EMPLOYEE_ID JOB_ID DEPARTMENT_ID
----------- ---------- -------------
206 AC_ACCOUNT 110
117 rows selected.
union all預設不會對結果進行排序，所有如果明確兩個查詢結果沒有重複的，可以使用union all，避免排序操作及臨時表空間的使用，這也是效能調優方法之一。
4、intersect運算子
把兩個查詢的結果取相同的部分，會去重及排序
例子：查詢當前職位和以前從事過的職位相同的員工編號及職位
SQL> select employee_id,job_id from employees
2 intersect
3 select employee_id,job_id from job_history;
EMPLOYEE_ID JOB_ID
----------- ----------
176 SA_REP
200 AD_ASST
使用去重的功能還可以透過集合操作取出唯一的記錄，類似distinct
例子：查詢人員表裡面有多少唯一的部門
SQL> select department_id from employees
2 intersect
3 select department_id from employees;
DEPARTMENT_ID
-------------
10
12 rows selected.
SQL> select department_id from employees
2 union
3 select department_id from employees;
DEPARTMENT_ID
-------------
10
12 rows selected.
5、minus運算子
從一個查詢的結果裡面除去另一個查詢的結果，會去重及排序
例子：查詢沒有改變過職位的人員
SQL> select employee_id from employees
2 minus
3 select employee_id from job_history;
EMPLOYEE_ID
-----------
206
100 rows selected.
6、集合運算子注意事項
- 每個集合select子句的欄位，個數和型別必須匹配
- 集合的預設執行順序是從上到下，可以使用括號去改變
- order by子句只能放在集合的最後，排序的欄位或者欄位別名只能來自於第一個select子句，或者直接使用位置進行排序
- 除了union all，其他集合運算子都會對結果進行去重和升序排序
- 最終結果的欄位來自於第一個查詢
例子：select子句欄位個數不匹配會報錯，可以使用null補齊
SQL> select employee_id,job_id,department_id from employees
2 union
3 select employee_id,job_id from job_history;
select employee_id,job_id,department_id from employees
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns
SQL> select employee_id,job_id,department_id from employees
2 union
3 select employee_id,job_id,null from job_history;
EMPLOYEE_ID JOB_ID DEPARTMENT_ID
----------- ---------- -------------
100 AD_PRES 90
例子：select子句欄位型別不匹配會報錯
SQL> select employee_id,job_id,department_id from employees
2 union
3 select employee_id,job_id,sysdate from job_history;
select employee_id,job_id,department_id from employees
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
可以使用轉換函式進行轉換，保證型別一致
SQL> select department_id,to_number(null) location,hire_date from employees
2 union
3 select department_id,location_id,to_date(null) from departments;
DEPARTMENT_ID LOCATION HIRE_DATE
------------- ---------- ------------
10 1700
如果是數字型別，也可以直接使用數字進行填充
SQL> select employee_id,job_id,salary from employees
2 union
3 select employee_id,job_id,0 from job_history;
EMPLOYEE_ID JOB_ID SALARY
----------- ---------- ----------
100 AD_PRES 24000
101 AC_ACCOUNT 0
例子：使用括號改變執行順序
SQL> select employee_id,job_id,department_id from employees
2 union
3 select employee_id,job_id,department_id from job_history
4 minus
5 select employee_id,job_id,department_id from job_history
6 union all
7 select employee_id,job_id,department_id from employees;
EMPLOYEE_ID JOB_ID DEPARTMENT_ID
----------- ---------- -------------
197 SH_CLERK 50
213 rows selected.
SQL> (
2 select employee_id,job_id,department_id from employees
3 union
4 select employee_id,job_id,department_id from job_history
5 )
6 minus
7 (
8 select employee_id,job_id,department_id from job_history
9 union all
10 select employee_id,job_id,department_id from employees
11 );
no rows selected
例子：order by子句不在最後會報錯
SQL> (
2 select employee_id,job_id,department_id from employees
3 union
4 select employee_id,job_id,department_id from job_history
5 order by 1
6 )
7 minus
8 (
9 select employee_id,job_id,department_id from job_history
10 union all
11 select employee_id,job_id,department_id from employees
12 );
order by 1
*
ERROR at line 5:
ORA-00907: missing right parenthesis
例子：控制集合最後結果的順序
SQL> select 'sing' as "my dream",3 a_dummy from dual
2 union
3 select 'I''d like to teach',1 a_dummy from dual
4 union
5 select 'the world to',2 a_dummy from dual;
my dream A_DUMMY
----------------- ----------
I'd like to teach 1
sing 3
the world to 2
SQL> select 'sing' as "my dream",3 a_dummy from dual
2 union
3 select 'I''d like to teach',1 a_dummy from dual
4 union
5 select 'the world to',2 a_dummy from dual
6 order by a_dummy;
my dream A_DUMMY
----------------- ----------
I'd like to teach 1
the world to 2
sing 3
SQL> column a_dummy noprint
SQL> select 'sing' as "my dream",3 a_dummy from dual
2 union
3 select 'I''d like to teach',1 a_dummy from dual
4 union
5 select 'the world to',2 a_dummy from dual
6 order by a_dummy;
my dream
-----------------
I'd like to teach
the world to
sing
例子：Oracle外連線的實現方式
SQL> select employee_id,last_name,department_name
2 from employees e,departments d
3 where e.department_id=d.department_id(+)
4 union
5 select employee_id,last_name,department_name
6 from employees e,departments d
7 where e.department_id(+)=d.department_id;
EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME
----------- ------------------------- ------------------------------
Treasury
123 rows selected.
7、相關習題
（1）View the Exhibit and examine the data in the DEPARTMENTS tables. Evaluate the following SQL statement: SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department_id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10 ;Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.)
A.ORDER BY 3;
B.ORDER BY 'b';
C.ORDER BY DEPT_ID;
D.ORDER BY DEPT_NAME;
答案：AC
B不正確的原因是因為'b'不是列的名字，也不是別名，而是一個具體的值,所以不能在order by 後面使用。
（2）View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. Which SET operator would you use in the blank space in the following SQL statement to list the departments where all the employees have managers? SELECT department_id FROM departments ____ SELECT department_id FROM employees WHERE manager_id IS NULL?
A.UNION
B.MINUS
C.INTERSECT
D.UNION ALL
答案：B
（3）View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables. Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them? SELECT location_id, city FROM locations ____ SELECT location_id, city FROM locations JOIN departments USING(location_id)?
A.UNION
B.MINUS
C.INTERSECT
D.UNION ALL
答案：C
（4）View the Exhibit and examine the data in the EMPLOYEES tables. Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees WHERE department_id= 50 ORDER BY department_id UNION SELECT employee_id, department_id FROM employees WHERE department_id=90 UNION SELECT employee_id, department_id FROM employees WHERE department_id=10；What would be the outcome of the above SQL statement？
A.The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
B.The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
C.The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
D.The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.
答案：D
（5）View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. The query should display the employee IDs of all the employees who have held the job SA_MAN at any time during their tenure. Choose the correct SET operator to fill in the blank space and complete the following query. SELECT employee_id FROM employees WHERE job_id = 'SA_MAN' ___________ SELECT employee_id FROM job_history WHERE job_id='SA_MAN'；
A.UNION
B.MINUS
C.INTERSECT
D.UNION ALL
答案：A
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28536251/viewspace-1846035/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle OCP(09)：使用集合運算子Oracle
- OCP課程8：SQL之使用子查詢SQL
- OCP課程6：SQL之使用組函式SQL函式
- OCP課程5：SQL之使用單行函式SQL函式
- OCP課程14：SQL之控制使用者訪問SQL
- OCP課程19：SQL之使用子查詢檢索資料SQL
- OCP課程56：管理II之SQL調優SQL
- OCP課程15：SQL之管理模式物件SQL模式物件
- OCP課程21：SQL之正規表示式SQL
- OCP課程12：SQL之建立其他模式物件SQL模式物件
- OCP課程13：SQL之使用資料字典檢視管理物件SQL物件
- OCP課程11：SQL之使用DDL語句建立和管理表SQL
- OCP課程3：SQL之使用SELECT語句檢索資料SQL
- OCP課程16：SQL之處理大資料SQL大資料
- OCP課程4：SQL之限制和排序資料SQL排序
- 【學習】SQL基礎-008-集合運算子SQL
- 微課|玩轉Python輕鬆過二級（2.2.4節）：位運算子與集合運算子Python
- OCP課程20：SQL之分層查詢SQL
- OCP課程7：SQL之多表查詢SQL
- OCP課程18：SQL之管理不同時區下的資料SQL
- OCP課程25：管理Ⅰ之使用DBCA建立資料庫資料庫
- 【SQL】SELECT語句中集合運算子 UNION/INTERSECT/MINUSSQL
- OCP課程48：管理II之使用RMAN執行恢復
- OCP課程51：管理II之使用閃回技術1
- OCP課程52：管理II之使用閃回技術2
- OCP課程53：管理II之使用閃回資料庫資料庫
- PL/SQL 運算子SQL
- SQL Server查詢計劃系列之——邏輯運算子與物理運算子SQLServer
- OCP課程27：管理Ⅰ之管理ASM例項ASM
- OCP課程39：管理Ⅰ之移動資料
- 前端入門9-JavaScript語法之運算子前端JavaScript
- OCP課程58：管理II之自動任務
- OCP課程24：管理Ⅰ之資料庫安裝資料庫
- OCP課程42：管理II之核心概念和工具
- OCP課程54：管理II之管理記憶體記憶體
- OCP課程45：管理II之備份設定
- javascript中&&運算子和||運算子的使用JavaScript
- OCP課程17：SQL之透過分組相關資料產生報告SQL