集合运算符之全集、交集、补集【weber出品必属精品】
2014-08-25 10:19 yaoweber 阅读(822) 评论(0) 编辑 收藏 举报-
集合的概念
与数学中的全集、交集、补集的概念是一样的
-
常用的集合运算符
集合运算符的作用：把两个查询构造为一个联合查询
1. 全集：求连个查询的全集
union all：将两个查询的所有数据全部列出，不进行排序，不去掉重复的部分
SQL> create table t1 as select * from emp where deptno in (10,20); Table created. SQL> create table t2 as select * from emp where deptno in (20,30); Table created. SQL> select * from t1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 8 rows selected. SQL> select * from t2; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 11 rows selected. SQL> select * from t1 2 union all 3 select * from t2; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 19 rows selected.
union：将两个查询的所有数据进行显示，但是重复的部分只显示一次，而且要按照第一个查询的第一列进行升序排序
SQL> select * from t1 2 union 3 select * from t2; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected.
union all与union的性能哪个更高？
union all性能更高：因为union all 不进行排序，也不去重
2. 交集：INTERSECTSQL> select * from t1 2 intersect 3 select * from t2; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20
3. 补集：MINUS
查询select * from e2的补集：SQL> select * from t1 2 minus 3 select * from t2; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7839 KING PRESIDENT 17-NOV-81 5000 10 7934 MILLER CLERK 7782 23-JAN-82 1300 10
-
SET运算符规则
1. 每个查询列表中的表达式的个数和数据类型必须相匹配
select后面的列的个数必须要一致：
select empno,ename,deptno,sal from e1 union all select empno,ename,deptno from e2; select empno,ename,deptno,sal from e1 * 第 1 行出现错误: ORA-01789: 查询块具有不正确的结果列数 select empno,ename,deptno,sal from e1 union all select empno,ename,deptno,null from e2 null是可以的 数据类型必须一致： select empno,ename,deptno,sal from e1 union all select empno,ename,deptno,'abc' from e2 第 1 行出现错误: ORA-01790: 表达式必须具有与对应表达式相同的数据类型
2. 可以使用括号来改变执行的顺序
select * from e1 intersect select * from e2 union select * from e2; ENAME EMPNO SAL DEPTNO ------ ----- ----- ------ ADAMS 7876 1100 20 ALLEN 7499 1600 30 BLAKE 7698 2850 30 FORD 7902 3000 20 JAMES 7900 950 30 JONES 7566 2975 20 MARTIN 7654 1250 30 SCOTT 7788 4000 20 SMITH 7369 800 20 TURNER 7844 1500 30 WARD 7521 1250 30 已选择11行。 select * from e1 intersect (select * from e2 union select * from e2); ENAME EMPNO SAL DEPTNO ------ ----- ----- ------ ADAMS 7876 1100 20 FORD 7902 3000 20 JONES 7566 2975 20 SCOTT 7788 4000 20 SMITH 7369 800 20
3. ORDER BY 子句的使用:
除了union all之外，其他的集合运算符都要按照第一个查询的第一列，进行升序
只可以在语句的最后出现：
select empno,ename,sal,deptno from e2 union select empno,ename,sal,deptno from e2 order by ename desc; EMPNO ENAME SAL DEPTNO ----- ------ ----- ------ 7521 WARD 1250 30 7844 TURNER 1500 30 7369 SMITH 800 20 7788 SCOTT 4000 20 7654 MARTIN 1250 30 7566 JONES 2975 20 7900 JAMES 950 30 7902 FORD 3000 20 7698 BLAKE 2850 30 7499 ALLEN 1600 30 7876 ADAMS 1100 20
可以使用第一个查询语句的列名、别名、或位置（号）
select empno,ename name1,sal,deptno from e2 union select empno,ename name2,sal,deptno from e2 order by name1 desc; EMPNO NAME1 SAL DEPTNO ----- ---------- ----- ------ 7521 WARD 1250 30 7844 TURNER 1500 30 7369 SMITH 800 20 7788 SCOTT 4000 20 7654 MARTIN 1250 30 7566 JONES 2975 20 7900 JAMES 950 30 7902 FORD 3000 20 7698 BLAKE 2850 30 7499 ALLEN 1600 30 7876 ADAMS 1100 20 已选择11行。 select empno,ename name1,sal,deptno from e2 union select empno,ename name2,sal,deptno from e2 order by name2 desc; order by name2 desc * 第 4 行出现错误: ORA-00904: "NAME2": 标识符无效 select empno,ename,sal,deptno from e2 union select empno,to_char(sal) salary, null,deptno from e2 order by 2; EMPNO ENAME SAL DEPTNO ----- ------ ----- ------ 7876 1100 20 7521 1250 30 7654 1250 30 7844 1500 30 7499 1600 30 7698 2850 30 7566 2975 20 7902 3000 20 7788 4000 20 7369 800 20 7900 950 30 7876 ADAMS 1100 20 7499 ALLEN 1600 30 7698 BLAKE 2850 30 7902 FORD 3000 20 7900 JAMES 950 30 7566 JONES 2975 20 7654 MARTIN 1250 30 7788 SCOTT 4000 20 7369 SMITH 800 20 7844 TURNER 1500 30 7521 WARD 1250 30
第一个查询语句的列名出现在结果中
select empno,to_char(sal) salary, null,deptno from e2 union select empno,ename,sal,deptno from e2 order by 2 / EMPNO SALARY NULL DEPTNO ----- ---------------------------------------- ---------- ------ 7876 1100 20 7521 1250 30 7654 1250 30 7844 1500 30 7499 1600 30 7698 2850 30 7566 2975 20 7902 3000 20 7788 4000 20 7369 800 20 7900 950 30 7876 ADAMS 1100 20 7499 ALLEN 1600 30 7698 BLAKE 2850 30 7902 FORD 3000 20 7900 JAMES 950 30 7566 JONES 2975 20 7654 MARTIN 1250 30 7788 SCOTT 4000 20 7369 SMITH 800 20 7844 TURNER 1500 30 7521 WARD 1250 30
查询语句的匹配：个数和数据类型的匹配
select empno,ename,sal,deptno from e1 union select 1,to_char(sal),null,10 from e2; EMPNO ENAME SAL DEPTNO ----- ------ ----- ------ 1 1100 10 1 1250 10 1 1500 10 1 1600 10 1 2850 10 1 2975 10 1 3000 10 1 4000 10 1 800 10 1 950 10 7369 SMITH 800 20 7566 JONES 2975 20 7782 CLARK 2450 10 7788 SCOTT 4000 20 7839 KING 5000 10 7876 ADAMS 1100 20 7902 FORD 3000 20 7934 MILLER 1300 10
