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
- 【學習】SQL基礎-008-集合運算子SQL
- 微課|玩轉Python輕鬆過二級（2.2.4節）：位運算子與集合運算子Python
- PL/SQL 運算子SQL
- 前端入門9-JavaScript語法之運算子前端JavaScript
- JS-11 運算子之布林運算子JS
- javascript中&&運算子和||運算子的使用JavaScript
- 第三課.運算子與表示式
- php運算子運用之型別運算子該如何使用PHP型別
- 位運算子之---按位取反運算子(簡單易懂)
- MySQL筆記 —SQL運算子打椰MySql筆記
- 運算子 運算子
- 課堂筆記 - C++ 位運算子筆記C++
- OCP 複習筆記之PL/SQL (1)筆記SQL
- OCP 複習筆記之PL/SQL (3)筆記SQL
- OCP 複習筆記之PL/SQL (2)筆記SQL
- OCP 複習筆記之PL/SQL (4)筆記SQL
- OCP 複習筆記之PL/SQL (5)筆記SQL
- ORACLE_OCP之SQL_子查詢OracleSQL
- python學習之運算子Python
- python之運算子過載Python
- [java基礎]之 運算子Java
- php運算子 比較運算子 邏輯運算子 三元運算子PHP
- 運算子-賦值運算子賦值
- MySQL的四種運算子（算術運算子、比較運算子、邏輯運算子和位運算子）MySql
- 算術運算子++、--的使用
- 8.Golang中的運算子-算術運算子、關係運算子、邏輯運算子、賦值運算子Golang賦值
- KingbaseES 自定義運算子使用示例
- php型別運算子的使用PHP型別
- Java 基礎 之 算數運算子Java
- 開心檔之C++ 運算子C++
- JavaScript 2.3 js之||、&&、|、&、三目運算子JavaScriptJS
- 【python隨筆】之【運算子過載】Python
- 運算子的關係，什麼叫一元運算子，二元運算子，三元運算子，運算子優先順序，以及運算子的
- Solidity語言學習筆記————9、左值運算子Solid筆記
- 運算子
- [譯]C++ 協程：理解 co_await 運算子C++AI
- 計算機實驗室之樹莓派：課程 9 螢幕04計算機樹莓派
- 算術運算子裡的特殊運算子