UNION,UNION ALL,MINUS,INTERSECT用法

1.首先创建2张表并插入数据

create table mytest(id int,name varchar2(30));
insert into mytest values(1,'a');
insert into mytest values(2,'b');
insert into mytest values(3,'c');
insert into mytest values(4,'d');
insert into mytest values(5,'e');
insert into mytest values(6,NULL);
insert into mytest values(8,null);
commit;

create table mytest_1(id int,name varchar2(30));
insert into mytest_1 values(1,'a');
insert into mytest_1 values(2,'b');
insert into mytest_1 values(3,'c');
insert into mytest_1 values(6,'f');
insert into mytest_1 values(7,'g');
insert into mytest_1 values(8,null);
commit;

2.union使用

--表联合运算 返回的结果集去掉重复值合并后的结果
-- union 被选择的列数和列的数据类型必须一致
-- 不忽略空值
-- 输出默认以第一列升序排序
SQL> select id,name from mytest
  2  union 
  3  select id,name from mytest_1;

        ID NAME
---------- ------------------------------
         1 a
         2 b
         3 c
         4 d
         5 e
         6 f
         6
         7 g
         8

9 rows selected.

3.union all使用

--返回的不会去掉重复也不会排序
SQL> set pagesize 20
SQL> select id,name from mytest
  2  union all 
  3  select id,name from mytest_1 order by id;

        ID NAME
---------- ------------------------------
         1 a
         1 a
         2 b
         2 b
         3 c
         3 c
         4 d
         5 e
         6 f
         6
         7 g
         8
         8

13 rows selected.

4.minus使用

--相减 返回在第一个表中而不在第二个表中的行
SQL> select id,name from mytest
  2  minus 
  3  select id,name from mytest_1;

        ID NAME
---------- ------------------------------
         4 d
         5 e
         6

5.intersect使用

--相交 列数和数据类型必须一样 列名可以不同
SQL> select id,name from mytest
  2  intersect 
  3  select id,name from mytest_1;

        ID NAME
---------- ------------------------------
         1 a
         2 b
         3 c
         8