注意事项：
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博客

集合运算符

最新文章