ORACLE 运算

集合运算
集合运算
   集合运算组合两个或多个部分查询的结果到一个结果中。包含集合运算的查询称为复合查询。

Operator		Returns

UNION(联合)		由每个查询选择的所有不同的行(无重复值)
UNION ALL		由每个查询选择的所有的行，包括所有重复的行
INTERSECT(交叉)		由两个查询选择的所有不同的行
MINUS			由第一个查询选择的所有不同的行


   所有的集合运算与等号的优先级相同，如果SQL语句包含多个集合运算并且没有圆括号明确地指定另一个顺序，Oracle服务器将以从左到右的顺序计算。你应该使用圆括号来明确地指定带另外的集合运算的INTERSECT (相交) 运算查询中的赋值顺序。

    注：在幻灯片中，图中的亮色代表查询结果。

    INTERSECT (相交) 和MINUS (相减) 运算不是ANSI SQL-99兼容的，他们是Oracle特定的。


在下面要用到的表：
:: EMPLOYEES: 提供所有在职雇员当前的详细资料
:: JOB_HISTORY:当一个雇员改变工作时，记录他的以前的工作的开始日期和结束日期、
   departmentID和job ID的详细资料

SQL> desc employees;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)


QL> desc job_history
名称                   是否为空?    类型
---------------------- -------- -------------
EMPLOYEE_ID            NOT NULL NUMBER(6)
START_DATE             NOT NULL DATE
END_DATE               NOT NULL DATE
JOB_ID                 NOT NULL VARCHAR2(10)
DEPARTMENT_ID                   NUMBER(4)



[b]联合运算[/b]

联合(UNION) 运算从两个查询返回除去重复值后的结果

UNION(联合)运算
UNION运算返回所有由任一查询选择的行。用UNION运算从多表返回所有行，但除去任何重复的行。

原则./././././
:: 被选择的列数和列的数据类型必须是与所有用在查询中的SELECT语句一致。列的名字不必相同。
:: 联合运算在所有被选择的列上进行。
:: 在做重复检查的时候不忽略空(NULL)值。././././
:: IN运算有比UNION运算高的优先级。
:: 在默认情况下，输出以SELECT子句的第一列的升序排序。./././././



[b]使用联合集合运算[/b]
    
   显示当前和以前所有雇员的工作岗位。每个雇员仅显示一次

SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;

使用联合集合(UNION SET)运算
       联合运算消除重复记录，如果有相同的记录同时出现在EMPLOYEES和JOB_HISTORY表中，该记录只显示一次，观察幻灯片的输出显示雇员号为200的雇员的雇员号虽然显示了两次，但每一行的JOB_ID是不同的。

考虑下面的例子：
SELECT employee_id, job_id, department_id
FROM employees
UNION
SELECT employee_id, job_id, department_id
FROM job_history;


EMPLOYEE_ID	JOB_ID		DEPARTMENT_ID
100
200	 	AC_ACCOUNT	90
200		AD_ASST		10
200		AD_ASST		90

...
600

使用联合集合(UNION SET)运算 (续)
    在前面的输出中，雇员200出现了3次，为什么？注意雇员200的DEPARTMENT_ID值，一行是90，另一行是10，第三行是90，因为这些job_id和department_id的唯一组合，对于每行的雇员200是唯一的，因此他们是不重复的。
   
    观察输出以SELECT子句的第一列的升序排序，即以EMPLOYEE_ID排序。


[b]全联合运算[/b]

    全联合(UNION ALL) 运算从两个查询返回包括所有重复值的结果


全联合(UNION ALL)运算
用全联合运算从多个查询中返回所有行。

原则	./././././././././
:: 和联合不同，重复的行不被过滤，并且默认情况下输出不排序。
:: 不能使用DISTINCT关键字。

   注：除了上面的两点，UNION ALL的原则与UNION相同。


[b]使用全联合运算[/b]

    显示当前和以前所有雇员所在的部门

SELECT employee_id, job_id, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM job_history
ORDER BY employee_id;


EMPLOYEE_ID	JOB_ID		DEPARTMENT_ID
100
200		AD_ASST		10
200		AD_ASST		90
200	 	AC_ACCOUNT	90

...
600

[b]
全联合(UNION ALL)运算 (续)[/b]
    在例子中，有30行被选择，两个表组合到共30行中，全联合运算不会消除重复的行，在幻灯片中重复的行被突出显示，联合返回任一查询所选择的所有不重复的行，而全联合返回任一查询所选择的所有行，包括所有重复。

现在用联合子句来写：
SELECT employee_id, job_id,department_id 
FROM employees 
UNION 
SELECT employee_id, job_id,department_id 
FROM job_history 
ORDER BY employee_id;

上面的查询返回29行，这是因为下面的行被除去了(因为它是一个重复行)：
176	SA_REP		80


[b]
相交运算
[/b]
   相交(INTERSECT) 运算返回多个查询中所有相同的行

相交运算
      用相交运算返回多个查询中所有的公共行。

原则		./././.列数和数据类型
:: 在查询中被 SELECT 语句选择的列数和数据类型必须与在查询中所使用的所有的 SELTCT 语句中的   一样，但列的名字不必一样。
:: 颠倒相交的表的排序不改变结果。
:: 相交不忽略空值。

[b]使用相交运算[/b]

      显示雇员表的employee_ID 和job_ID，这些雇员当前所做的工作是以前他们做过一端时间，后来有变化，现在又在做的工作。

SQL> SELECT employee_id, job_id
  2  FROM employees
  3  INTERSECT
  4  SELECT employee_id, job_id
  5  FROM job_history;

EMPLOYEE_ID JOB_ID
----------- ----------
        176 SA_REP
        200 AD_ASST


相交运算 (续)
    在幻灯片的例子中，查询仅返回在两个表的被选择的列中有相同值的记录。

    如果你从EMPLOYEES表添加DEPARTMENT_ID列到SELECT语句中，并且从JOB_HISTORY表添加DEPARTMENT_ID列到SELECT语句中，然后运行该查询，将返回什么？其结果可能是不同的，因为在加入的另一个列中，其值可能是重复的，也可能不重复。

例子
SELECT employee_id, job_id, department_id
FROM employees
INTERSECT
SELECT employee_id, job_id, department_id
FROM job_history;

EMPLOYEE_ID JOB_ID     DEPARTMENT_ID
----------- ---------- -------------
        176 SA_REP                80

雇员200不再是结果的一部分，因为EMPLOYEES.DEPARTMENT_ID值不同于 JOB_HISTORY.DEPARTMENT_ID值。


[b]相减运算 [/b]

	相减(MINUS) 运算返回在第一个查询中而不在第二个查询中的行

相减运算
    用相减运算返回由第一个查询返回的行，那些行不出现在第二个查询中 (第一个SELECT语句减第二个SELECT语句)。

原则	./././././././
:: 在查询中被SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT语句中的一样    ，但列的名字不必一样。
:: 对于MINUS运算，在WHERE子句中所有的列都必须在SELECT子句中。

相减运算
   
   显示那些从来没有改变过他们的工作的雇员显示那些从来没有改变过他们的工作的雇员ID

SELECT employee_id,job_id
FROM employees
MINUS
SELECT employee_id,job_id
FROM job_history;
//由存在于EMPLOYEES表中但不存在于JOB_HISTORY表中的行所表示(结果只是employees表的减少,没有第二个表的记录.)

相减运算 (续)
     上述例子中，JOB_HISTORY表的employee_id列和job_id列被从EMPLOYEES表的那些列中减去。结果集显示相减后剩余的雇员，他们由存在于EMPLOYEES表中但不存在于JOB_HISTORY表中的行所表示。这些行是那些从未改变过工作的雇员的记录

雇员200未出现，因为他换过工作。(即,在第一个,第二个表中都出现了)


[b]集合运算的原则[/b]	/././././././

:: 在两个SELECT列表中的表达式必须在数目上和数据类型上相匹配
:: 可以用圆括号改变执行的顺序

:: ORDER BY子句：	/./././././
	–只能出现在语句的最后
	–从第一个SELECT语句接收列名、别名，或者位置记号


集合运算的原则

:: 在两个查询的选择列表中的表达式在数目上和数据类型上必须匹配。使用UNION、UNION ALL、INTERSECT和MINUS SET运算的查询，在它们的WHERE子句中必须有与它们的SELECT列表相同的列数和列数据类型。

例如：
SELECT employee_id, department_id
FROM employees
WHERE (employee_id, department_id)
IN (SELECT employee_id, department_id
    FROM employees
    UNION
    SELECT employee_id, department_id
    FROM job_history
);

:: ORDER BY子句：
	- 只可以出现在每个语句的末尾
	- 将接受列、别名或位置记号

:: 列名或别名，如果用在ORDER BY子句中，必须来自第一个SELECT列表。
:: 集合运算可以用在子查询中。



[b]Oracle 服务器和集合运算[/b]

:: 除了UNION ALL，重复行自动被清除
:: 在结果中的列名是第一个查询中出现的列名	/./././
:: 除了UNION ALL(无排序)，默认情况下按升序顺序输出	/././

Oracle服务器和集合运算
   当一个查询使用集合运算时，除了UNION ALL运算，Oracle服务器会自动消除重复的行。输出中的列名由第一个SELECT语句的列表确定。默认情况下，输出以SELECT子句的第一列的升序排序。

   在一个复合查询的各查询组成部分的选择列表中相应的表达式必须在数目和类型上匹配。如果查询的组成部分选择字符数据，返回值的数据类型被如下决定：
 :: 如果查询选择的数据类型的值为CHAR，那么，返回值的数据类型也为CHAR。
 :: 如果查询选择的两者之一或两者的数据类型值为VARCHAR2，那么，返回值的数据类型也是VARCHAR2。

  你可能需要提及，输出以第一个SELECT子句的第一列的升序被排序，然后是第二列，等等。



匹配SELECT语句	//凑数

   使用UNION运算，显示所有雇员的department ID、location、和受雇日期


SELECT department_id, TO_NUMBER(null) location, hire_date
FROM employees
UNION
SELECT department_id, location_id, TO_DATE(null)
FROM departments;

DEPARTMENT_ID   LOCATION HIRE_DATE
------------- ---------- ----------
           80            21-4月 -00
           90       1700
           90            17-6月 -87
           90            21-9月 -89
           90            13-1月 -93
          100       1700
          100            16-8月 -94
          100            17-8月 -94
          100            28-9月 -97
          100            30-9月 -97
          100            07-3月 -98

匹配SELECT语句
    由于在两个查询的SELECT列表中的表达式必须在数量上匹配，你可以使用虚拟列和转换函数数据类型来满足该规则。在幻灯片中使用了虚拟列，在第一个查询中的TO_NUMBER函数被用以匹配第二个查询中返回的LOCATION_ID列的数字数据类型，同样地，第二个查询中的TO_DATE函数被用于匹配 第一个查询返回的日期数据类型。

[b]匹配SELECT语句[/b]

:: 使用UNION运算，显示所有雇员的employee ID、job ID 和salary

SELECT employee_id, job_id,salary
FROM employees
UNION
SELECT employee_id, job_id,0
FROM job_history;

EMPLOYEE_ID JOB_ID         SALARY
----------- ---------- ----------
        192 SH_CLERK         4000
        193 SH_CLERK         3900
        194 SH_CLERK         3200
        195 SH_CLERK         2800
        196 SH_CLERK         3100
        197 SH_CLERK         3000
        198 SH_CLERK         2600
        199 SH_CLERK         2600
        200 AC_ACCOUNT          0
        200 AD_ASST             0
        200 AD_ASST          4400


匹配SELECT语句：例
      EMPLOYEES和JOB_HISTORY表中有一些列是公有的；例如，EMPLOYEE_ID, JOB_ID和DEPARTMENT_ID，但如果你想要用UNION运算查询他们以显示EMPLOYEE_ID、JOB_ID和SALARY时，知道薪水仅存在于EMPLOYEES表中吗？

      在幻灯片中的代码例子匹配EMPLOYEES表和JOB_HISTORY表中的EMPLOYEE_ID和JOB_ID列。一个0值被添加到JOB_HISTORY的SELECT语句中以匹配在EMPLOYEES的SELECT语句中的数字的SALARY列。
在前面的结果中，在输出中的每一行相对应于JOB_HISTORY表的一条记录，包括SALARY列值为0的列。



[b]控制行顺序[/b]
   
    用两个UNION运算产生一个英语句子

COLUMN a_dummy NOPRINT
SELECT 'sing' AS "My dream", 3 a_dummy
FROM dual
UNION
SELECT 'I''d like to teach', 1
FROM dual
UNION 
SELECT 'the world to', 2
FROM dual
ORDER BY 2;

ORDER BY 只能用列、别名或第一个查询的列位置。

控制行顺序
  在默认情况下，输出以第一列的升序排序，你可以用ORDER BY子句来改变顺序。

使用ORDER BY来改变行的顺序
  ORDER BY子句在复合查询中只能用一次。如果使用了，ORDER BY子句必须放在最后面的查询中。ORDER BY子句接受列名、别名或位置记号。如果没有ORDER BY子句，幻灯片中的代码例子将按第一列的字母顺序产生下面的输出：
			My dream
i'd like to teach
sing
the world to
		/././././
  注：考虑一个多次使用UNION SET运算的复合查询，在这种情况下，ORDER BY子句中只能用位置而不能显式表示。在幻灯片的例子中3个SELECT列表的第二个位置分别是3、1、2。另外，因为输出按照第一个SELECT列表，所以别名My dream定义在第一行；指示第二列不输出的a_dummy也在第一列。