SQL 基础7 |
您所在的位置:网站首页 › sql集合运算缺点 › SQL 基础7 |
集合运算操作符可以将两个或多个查询返回的行组合起来,即集合属于纵向连接运算
一、常用的集合运算符 UNION ALL 返回各个查询检索出的所有的行,不过滤掉重复记录 UNION 返回各个查询检索出的过滤掉重复记录的所有行,即并集 INTERSECT 返回两个查询检索出的共有行,即交集 MINUS 返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的行,即差集
二、集合运算的原则 1.所有选择列表的表达式数目必须相同 2.对于结果集中各列,或个别子查询中的任意列的子集必须具有相同的数据类型,或是可以隐式转化为相同的数据类型,否则需显示转换 3.各个查询中对应的结果集列出现的顺序必须相同 4.生成的结果集中的列名来自UNION语句中第一个单独的查询
三、演示各个集合运算符
--为集合运算生成环境,生成有相同结构的emp表,且命名为emp2 SQL> conn scott/tiger; Connected. SQL> select * from emp;
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 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.
SQL> create table emp2 tablespace tbs1 as select * from emp where empno in (7369,7654,7839,7876);
Table created.
SQL> insert into emp2 (empno,ename,sal) select 8001,'ROBINSON',3500 from dual;
1 row created.
SQL> insert into emp2 (empno,ename,sal) select 8002,'HENRY',3700 from dual;
1 row created.
SQL> insert into emp2 (empno,ename,sal) select 8004,'JOHNSON',4000 from dual;
1 row created.
SQL> select * from emp2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7839 KING PRESIDENT 17-NOV-81 5000 10 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 8001 ROBINSON 3500 8002 HENRY 3700 8004 JOHNSON 4000
--1.UNION 过滤了重复记录 SQL> select empno,ename,job,hiredate,sal from emp 2 union 3 select empno,ename,job,hiredate,sal from emp2;
EMPNO ENAME JOB HIREDATE SAL ---------- ---------- --------- --------- ---------- 7369 SMITH CLERK 17-DEC-80 800 7499 ALLEN SALESMAN 20-FEB-81 1600 7521 WARD SALESMAN 22-FEB-81 1250 7566 JONES MANAGER 02-APR-81 2975 7654 MARTIN SALESMAN 28-SEP-81 1250 7698 BLAKE MANAGER 01-MAY-81 2850 7782 CLARK MANAGER 09-JUN-81 2450 7788 SCOTT ANALYST 19-APR-87 3000 7839 KING PRESIDENT 17-NOV-81 5000 7844 TURNER SALESMAN 08-SEP-81 1500 7876 ADAMS CLERK 23-MAY-87 1100
EMPNO ENAME JOB HIREDATE SAL ---------- ---------- --------- --------- ---------- 7900 JAMES CLERK 03-DEC-81 950 7902 FORD ANALYST 03-DEC-81 3000 7934 MILLER CLERK 23-JAN-82 1300 8001 ROBINSON 3500 8002 HENRY 3700 8004 JOHNSON 4000 17 rows selected.
--2.UNION ALL 并集,不去重复记录 SQL> select empno,ename,job,hiredate,sal from emp 2 union all 3 select empno,ename,job,hiredate,sal from emp2;
EMPNO ENAME JOB HIREDATE SAL ---------- ---------- --------- --------- ---------- 7369 SMITH CLERK 17-DEC-80 800 7499 ALLEN SALESMAN 20-FEB-81 1600 7521 WARD SALESMAN 22-FEB-81 1250 7566 JONES MANAGER 02-APR-81 2975 7654 MARTIN SALESMAN 28-SEP-81 1250 7698 BLAKE MANAGER 01-MAY-81 2850 7782 CLARK MANAGER 09-JUN-81 2450 7788 SCOTT ANALYST 19-APR-87 3000 7839 KING PRESIDENT 17-NOV-81 5000 7844 TURNER SALESMAN 08-SEP-81 1500 7876 ADAMS CLERK 23-MAY-87 1100
EMPNO ENAME JOB HIREDATE SAL ---------- ---------- --------- --------- ---------- 7900 JAMES CLERK 03-DEC-81 950 7902 FORD ANALYST 03-DEC-81 3000 7934 MILLER CLERK 23-JAN-82 1300 7369 SMITH CLERK 17-DEC-80 800 7654 MARTIN SALESMAN 28-SEP-81 1250 7839 KING PRESIDENT 17-NOV-81 5000 7876 ADAMS CLERK 23-MAY-87 1100 8001 ROBINSON 3500 8002 HENRY 3700 8004 JOHNSON 4000
21 rows selected.
--3.INTERSECT 交集,返回两个结果集中共有了部分 SQL> select empno,ename,job,hiredate,sal from emp 2 intersect 3 select empno,ename,job,hiredate,sal from emp2;
EMPNO ENAME JOB HIREDATE SAL ---------- ---------- --------- --------- ---------- 7369 SMITH CLERK 17-DEC-80 800 7654 MARTIN SALESMAN 28-SEP-81 1250 7839 KING PRESIDENT 17-NOV-81 5000 7876 ADAMS CLERK 23-MAY-87 1100
--4.MINUS 补集,前一个结果集减后一个结果集后的结果 SQL> select empno as "EmployeeNo" ,ename "EmployeeName",job "Job" ,hiredate as "HireDate",sal "Sal" from emp 2 minus 3 select empno,ename,job,hiredate,sal from emp2 4 order by "Sal";
EmployeeNo EmployeeNa Job HireDate Sal ---------- ---------- --------- --------- ---------- 7900 JAMES CLERK 03-DEC-81 950 7521 WARD SALESMAN 22-FEB-81 1250 7934 MILLER CLERK 23-JAN-82 1300 7844 TURNER SALESMAN 08-SEP-81 1500 7499 ALLEN SALESMAN 20-FEB-81 1600 7782 CLARK MANAGER 09-JUN-81 2450 7698 BLAKE MANAGER 01-MAY-81 2850 7566 JONES MANAGER 02-APR-81 2975 7788 SCOTT ANALYST 19-APR-87 3000 7902 FORD ANALYST 03-DEC-81 3000
10 rows selected. |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |