集合运算符

注意事项：
1：在select列表中的列名和表达式在数量上必须匹配
2：第二个查询中的每一列的数据类型必须和第一个查询其对应的列的数据类型匹配
３、ORDER BY子句不能在某个单独的查询中应用，只能用在整个查询的最后，用来对整个集合运算的结果集进行排序。
４、列名源自第一个输入查询。
5、除了union all之外系统会自动删除重复的记录

HR@rhel6>  select department_id,first_name from employees where department_id in (10,20,30,40);


DEPARTMENT_ID FIRST_NAME
------------- --------------------
  10 Jennifer
  20 Michael
  20 Pat
  30 Den
  30 Alexander
  30 Shelli
  30 Sigal
  30 Guy
  30 Karen
  40 Susan


10 rows selected.


HR@rhel6>  select department_id,first_name from employees where department_id in (10,20);


DEPARTMENT_ID FIRST_NAME
------------- --------------------
  10 Jennifer
  20 Michael
  20 Pat

union 
SQL> select department_id,first_name from employees where department_id in (10,20,30,40)
  2   union
  3   select department_id,first_name from employees where department_id in (10,20);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
           10 Jennifer
           20 Michael
           20 Pat
           30 Alexander
           30 Den
           30 Guy
           30 Karen
           30 Shelli
           30 Sigal
           40 Susan
10 rows selected
 

union all
SQL>  select department_id,first_name from employees where department_id in (10,20,30,40)
  2  union all
  3  select department_id,first_name from employees where department_id in (10,20);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
           10 Jennifer
           20 Michael
           20 Pat
           30 Den
           30 Alexander
           30 Shelli
           30 Sigal
           30 Guy
           30 Karen
           40 Susan
           10 Jennifer
           20 Michael
           20 Pat
13 rows selected

intersect

SQL> select department_id,first_name from employees where department_id in (10,20,30,40)
  2  intersect
  3   select department_id,first_name from employees where department_id in (10,20);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
           10 Jennifer
           20 Michael
           20 Pat

minus

SQL> select department_id,first_name from employees where department_id in (10,20,30,40)
  2  minus
  3  select department_id,first_name from employees where department_id in (10,20);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
           30 Alexander
           30 Den
           30 Guy
           30 Karen
           30 Shelli
           30 Sigal
           40 Susan
7 rows selected

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30166973/viewspace-1622172/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30166973/viewspace-1622172/