首页 > 代码库 > 集合运算符之全集、交集、补集【weber出品必属精品】
集合运算符之全集、交集、补集【weber出品必属精品】
集合的概念
与数学中的全集、交集、补集的概念是一样的
常用的集合运算符
集合运算符的作用：把两个查询构造为一个联合查询
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 108 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 2011 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 2019 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 1014 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 e1union allselect empno,ename,deptno from e2;select empno,ename,deptno,sal from e1*第 1 行出现错误:ORA-01789: 查询块具有不正确的结果列数select empno,ename,deptno,sal from e1union allselect empno,ename,deptno,null from e2null是可以的数据类型必须一致：select empno,ename,deptno,sal from e1union allselect empno,ename,deptno,‘abc‘ from e2第 1 行出现错误:ORA-01790: 表达式必须具有与对应表达式相同的数据类型
2. 可以使用括号来改变执行的顺序
select * from e1intersectselect * from e2unionselect * from e2;ENAME EMPNO SAL DEPTNO------ ----- ----- ------ADAMS 7876 1100 20ALLEN 7499 1600 30BLAKE 7698 2850 30FORD 7902 3000 20JAMES 7900 950 30JONES 7566 2975 20MARTIN 7654 1250 30SCOTT 7788 4000 20SMITH 7369 800 20TURNER 7844 1500 30WARD 7521 1250 30已选择11行。select * from e1intersect(select * from e2unionselect * from e2);ENAME EMPNO SAL DEPTNO------ ----- ----- ------ADAMS 7876 1100 20FORD 7902 3000 20JONES 7566 2975 20SCOTT 7788 4000 20SMITH 7369 800 20
3. ORDER BY 子句的使用:
除了union all之外，其他的集合运算符都要按照第一个查询的第一列，进行升序
只可以在语句的最后出现：
select empno,ename,sal,deptno from e2unionselect empno,ename,sal,deptno from e2order 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 e2unionselect empno,ename name2,sal,deptno from e2order 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 e2unionselect empno,ename name2,sal,deptno from e2order by name2 desc;order by name2 desc *第 4 行出现错误:ORA-00904: "NAME2": 标识符无效select empno,ename,sal,deptno from e2unionselect empno,to_char(sal) salary, null,deptno from e2order 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 e2unionselect empno,ename,sal,deptno from e2order 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 e1unionselect 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
集合运算符之全集、交集、补集【weber出品必属精品】
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。