--=============================================
-- SQL 基礎--> 集合運算(UNION 與UNION ALL)
集合運算操作符可以将兩個或多個查詢傳回的行組合起來，即集合屬于縱向連接配接運算
一、常用的集合運算符
UNION ALL 傳回各個查詢檢索出的所有的行，不過濾掉重複記錄
UNION 傳回各個查詢檢索出的過濾掉重複記錄的所有行，即并集
INTERSECT 傳回兩個查詢檢索出的共有行，即交集
MINUS 傳回将第二個查詢檢索出的行從第一個查詢檢索出的行中減去之後剩餘的行，即差集
二、集合運算的原則
1.所有選擇清單的表達式數目必須相同
2.對于結果集中各列，或個别子查詢中的任意列的子集必須具有相同的資料類型，或是可以隐式轉化為相同的資料類型，否則需顯示轉換
3.各個查詢中對應的結果集列出現的順序必須相同
4.生成的結果集中的列名來自UNION語句中第一個單獨的查詢
三、示範各個集合運算符
--為集合運算生成環境，生成有相同結構的emp表，且命名為emp2
SQL> conn scott/tiger;
Connected.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> create table emp2 tablespace tbs1 as select * from emp where empno in (7369,7654,7839,7876);
Table created.
SQL> insert into emp2 (empno,ename,sal) select 8001,'ROBINSON',3500 from dual;
1 row created.
SQL> insert into emp2 (empno,ename,sal) select 8002,'HENRY',3700 from dual;
SQL> insert into emp2 (empno,ename,sal) select 8004,'JOHNSON',4000 from dual;
SQL> select * from emp2;
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
8001 ROBINSON 3500
8002 HENRY 3700
8004 JOHNSON 4000
--1.UNION 過濾了重複記錄
SQL> select empno,ename,job,hiredate,sal from emp
2 union
3 select empno,ename,job,hiredate,sal from emp2;
EMPNO ENAME JOB HIREDATE SAL
---------- ---------- --------- --------- ----------
7369 SMITH CLERK 17-DEC-80 800
7499 ALLEN SALESMAN 20-FEB-81 1600
7521 WARD SALESMAN 22-FEB-81 1250
7566 JONES MANAGER 02-APR-81 2975
7654 MARTIN SALESMAN 28-SEP-81 1250
7698 BLAKE MANAGER 01-MAY-81 2850
7782 CLARK MANAGER 09-JUN-81 2450
7788 SCOTT ANALYST 19-APR-87 3000
7839 KING PRESIDENT 17-NOV-81 5000
7844 TURNER SALESMAN 08-SEP-81 1500
7876 ADAMS CLERK 23-MAY-87 1100
7900 JAMES CLERK 03-DEC-81 950
7902 FORD ANALYST 03-DEC-81 3000
7934 MILLER CLERK 23-JAN-82 1300
8001 ROBINSON 3500
8002 HENRY 3700
8004 JOHNSON 4000
17 rows selected.
--2.UNION ALL 并集，不去重複記錄
2 union all
7934 MILLER CLERK 23-JAN-82 1300
21 rows selected.
--3.INTERSECT 交集,傳回兩個結果集中共有了部分
2 intersect
--4.MINUS 補集,前一個結果集減後一個結果集後的結果
SQL> select empno as "EmployeeNo" ,ename "EmployeeName",job "Job" ,hiredate as "HireDate",sal "Sal" from emp
2 minus
3 select empno,ename,job,hiredate,sal from emp2
4 order by "Sal";
EmployeeNo EmployeeNa Job HireDate Sal
7934 MILLER CLERK 23-JAN-82 1300
10 rows selected.
四、更多
<a href="http://blog.csdn.net/robinson_0612/archive/2010/04/29/5542983.aspx">Oracle 資料庫執行個體啟動關閉過程</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/04/27/5534832.aspx">Oracle 10g SGA 的自動化管理</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/04/26/5529260.aspx">使用OEM,SQL*Plus,iSQL*Plus 管理Oracle執行個體</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/04/26/5529239.aspx">Oracle執行個體和Oracle資料庫(Oracle體系結構)</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/04/16/5495170.aspx">SQL 基礎-->常用函數</a>
<a href="http://blog.csdn.net/robinson_0612/archive/2010/04/15/5487992.aspx">SQL基礎-->過濾和排序</a>
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)