- union 和union all
Union因为要进行重复值扫描,所以效率低。如果合并没有刻意要删除重复行,那么就使用Union All
两个要联合的SQL语句 字段个数必须一样,而且字段类型要“相容”(一致);
如果我们需要将两个select语句的结果作为一个整体显示出来,我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果集合并在一起显示出来。
- union和union all的区别:
union会自动压缩多个结果集合中的重复结果(去重),而union all则将所有的结果全部显示出来,不管是不是重复。
Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;
Union All:对两个结果集进行并集操作,包括重复行,不进行排序;
Intersect:对两个结果集进行交集操作,不包括重复行,同时进行默认规则的排序;
Minus:对两个结果集进行差操作,不包括重复行,同时进行默认规则的排序。
可以在最后一个结果集中指定Order by子句改变排序方式。
1、union 自动将两个结果集中重复的数据进行合并只展示一条(两个或是多个结果集去重合并)
selectempno,ename,job,sal,deptno from emp where deptno>=20
union
select empno,ename,job,sal,deptno from empwhere deptno<30
order by sal;
EMPNOENAME JOB SAL DEPTNO
--------------- --------- --------- ------
7369 SMITH CLERK 800.00 20
7900 JAMES CLERK 950.00 30
7876 ADAMS CLERK 1100.00 20
7521 WARD SALESMAN 1250.00 30
7654 MARTIN SALESMAN 1250.00 30
7934 MILLER CLERK 1300.00 10
8888 测试数据 旺旺 1300.00 10
7844 TURNER SALESMAN 1500.00 30
7499 ALLEN SALESMAN 1760.00 30
7782 CLARK MANAGER 2450.00 10
7698 BLAKE MANAGER 2850.00 30
7566 JONES MANAGER 2975.00 20
7788 SCOTT ANALYST 3000.00 20
7902 FORD ANALYST 3000.00 20
7839 KING PRESIDENT 5000.00 10
2、union all 只会将两个结果集进行合并展示,但不会去重复(两个或多个结果集不去重合并)
selectempno,ename,job,sal,deptno from emp where deptno>=20
unionall
selectempno,ename,job,sal,deptno from emp where deptno<30
orderby sal;
EMPNOENAME JOB SAL DEPTNO
--------------- --------- --------- ------
7369 SMITH CLERK 800.00 20
7369 SMITH CLERK 800.00 20
7900 JAMES CLERK 950.00 30
7876 ADAMS CLERK 1100.00 20
7876 ADAMS CLERK 1100.00 20
7521 WARD SALESMAN 1250.00 30
7654 MARTIN SALESMAN 1250.00 30
7934 MILLER CLERK 1300.00 10
8888 测试数据 旺旺 1300.00 10
7844 TURNER SALESMAN 1500.00 30
7499 ALLEN SALESMAN 1760.00 30
7782 CLARK MANAGER 2450.00 10
7698 BLAKE MANAGER 2850.00 30
7566 JONES MANAGER 2975.00 20
7566 JONES MANAGER 2975.00 20
7902 FORD ANALYST 3000.00 20
7788 SCOTT ANALYST 3000.00 20
7902 FORD ANALYST 3000.00 20
7788 SCOTT ANALYST 3000.00 20
7839 KING PRESIDENT 5000.00 10
3、intersect 将两个或多个结果集进行交集处理(交集处理,去重复)
SQL>select job,deptno from emp;
JOB DEPTNO
---------------
CLERK 20
SALESMAN 30
SALESMAN 30
MANAGER 20
SALESMAN 30
MANAGER 30
MANAGER 10
ANALYST 20
PRESIDENT 10
SALESMAN 30
CLERK 20
CLERK 30
ANALYST 20
CLERK 10
旺旺 10
15rows selected
SQL>select distinct job,deptno from emp;
JOB DEPTNO
---------------
MANAGER 20
PRESIDENT 10
CLERK 10
SALESMAN 30
旺旺 10
ANALYST 20
MANAGER 30
MANAGER 10
CLERK 30
CLERK 20
10rows selected
SQL>select job,deptno from emp intersect select job,deptno from emp;
JOB DEPTNO
---------------
ANALYST 20
CLERK 10
CLERK 20
CLERK 30
MANAGER 10
MANAGER 20
MANAGER 30
PRESIDENT 10
SALESMAN 30
旺旺 10
10rows selected
SQL>select job,deptno from emp intersect select job,deptno from emp order by deptno;
JOB DEPTNO
---------------
CLERK 10
MANAGER 10
PRESIDENT 10
旺旺 10
ANALYST 20
CLERK 20
MANAGER 20
CLERK 30
MANAGER 30
SALESMAN 30
10rows selected
4、minus 差集处理(差集处理且要去重)
SQL>select * from dept;
DEPTNODNAME LOC
-------------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>select * from mydept;
DEPTNODNAME LOC
-------------------- -------------
10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
SQL>select * from dept minus select * from mydept;
DEPTNODNAME LOC
-------------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
SQL>select * from mydept minus select * from dept;
DEPTNODNAME LOC
-------------------- -------------
总结:minus进行差集去重处理时select 语句表的前后顺序不同,则查询出来的结果也不一样。