07.SQL 基础--> 集合运算(UNION 与UNION ALL)

1.所有选择列表的表达式数目必须相同
2.对于结果集中各列，或个别子查询中的任意列的子集必须具有相同的数据类型，或是可以隐式转化为相同的数据类型，否则需显示转换
3.各个查询中对应的结果集列出现的顺序必须相同
4.生成的结果集中的列名来自UNION语句中第一个单独的查询

三、演示各个集合运算符

--为集合运算生成环境，生成有相同结构的emp表，且命名为emp2

idle> select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE	     SAL       COMM	DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 1980-12-17	     800		    20
      7499 ALLEN      SALESMAN	      7698 1981-02-20	    1600	300	    30
      7521 WARD       SALESMAN	      7698 1981-02-22	    1250	500	    30
      7566 JONES      MANAGER	      7839 1981-04-02	    2975		    20
      7654 MARTIN     SALESMAN	      7698 1981-09-28	    1250       1400	    30
      7698 BLAKE      MANAGER	      7839 1981-05-01	    2850		    30
      7782 CLARK      MANAGER	      7839 1981-06-09	    2450		    10
      7788 SCOTT      ANALYST	      7566 1987-04-19	    3000		    20
      7839 KING       PRESIDENT 	   1981-11-17	    5000		    10
      7844 TURNER     SALESMAN	      7698 1981-09-08	    1500	  0	    30
      7876 ADAMS      CLERK	      7788 1987-05-23	    1100		    20
      7900 JAMES      CLERK	      7698 1981-12-03	     950		    30
      7902 FORD       ANALYST	      7566 1981-12-03	    3000		    20
      7934 MILLER     CLERK	      7782 1982-01-23	    1300		    10

14 rows selected.

idle>  create table emp2 tablespace tbs1 as select * from emp where empno in (7369,7654,7839,7876);

Table created.

idle> insert into emp2 (empno,ename,sal) select 8001,‘ROBINSON‘,3500 from dual;

1 row created.

idle> insert into emp2 (empno,ename,sal) select 8002,‘HENRY‘,3700 from dual;

1 row created.

idle> insert into emp2 (empno,ename,sal) select 8004,‘JOHNSON‘,4000 from dual;

1 row created.

idle>  select * from emp2;

     EMPNO ENAME      JOB	       MGR HIREDATE	     SAL       COMM	DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 1980-12-17	     800		    20
      7654 MARTIN     SALESMAN	      7698 1981-09-28	    1250       1400	    30
      7839 KING       PRESIDENT 	   1981-11-17	    5000		    10
      7876 ADAMS      CLERK	      7788 1987-05-23	    1100		    20
      8001 ROBINSON					    3500
      8002 HENRY					    3700
      8004 JOHNSON					    4000

7 rows selected.

--1.UNION 过滤了重复记录

idle> select empno,ename,job,hiredate,sal from emp
union
select empno,ename,job,hiredate,sal from emp2;
  2    3  
     EMPNO ENAME      JOB	HIREDATE	  SAL
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK	1980-12-17	  800
      7499 ALLEN      SALESMAN	1981-02-20	 1600
      7521 WARD       SALESMAN	1981-02-22	 1250
      7566 JONES      MANAGER	1981-04-02	 2975
      7654 MARTIN     SALESMAN	1981-09-28	 1250
      7698 BLAKE      MANAGER	1981-05-01	 2850
      7782 CLARK      MANAGER	1981-06-09	 2450
      7788 SCOTT      ANALYST	1987-04-19	 3000
      7839 KING       PRESIDENT 1981-11-17	 5000
      7844 TURNER     SALESMAN	1981-09-08	 1500
      7876 ADAMS      CLERK	1987-05-23	 1100
      7900 JAMES      CLERK	1981-12-03	  950
      7902 FORD       ANALYST	1981-12-03	 3000
      7934 MILLER     CLERK	1982-01-23	 1300
      8001 ROBINSON				 3500
      8002 HENRY				 3700
      8004 JOHNSON				 4000

17 rows selected.

--2.UNION ALL 并集，不去重复记录

idle> select empno,ename,job,hiredate,sal from emp
union all
select empno,ename,job,hiredate,sal from emp2;
  2    3  
     EMPNO ENAME      JOB	HIREDATE	  SAL
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK	1980-12-17	  800
      7499 ALLEN      SALESMAN	1981-02-20	 1600
      7521 WARD       SALESMAN	1981-02-22	 1250
      7566 JONES      MANAGER	1981-04-02	 2975
      7654 MARTIN     SALESMAN	1981-09-28	 1250
      7698 BLAKE      MANAGER	1981-05-01	 2850
      7782 CLARK      MANAGER	1981-06-09	 2450
      7788 SCOTT      ANALYST	1987-04-19	 3000
      7839 KING       PRESIDENT 1981-11-17	 5000
      7844 TURNER     SALESMAN	1981-09-08	 1500
      7876 ADAMS      CLERK	1987-05-23	 1100
      7900 JAMES      CLERK	1981-12-03	  950
      7902 FORD       ANALYST	1981-12-03	 3000
      7934 MILLER     CLERK	1982-01-23	 1300
      7369 SMITH      CLERK	1980-12-17	  800
      7654 MARTIN     SALESMAN	1981-09-28	 1250
      7839 KING       PRESIDENT 1981-11-17	 5000
      7876 ADAMS      CLERK	1987-05-23	 1100
      8001 ROBINSON				 3500
      8002 HENRY				 3700
      8004 JOHNSON				 4000

21 rows selected.

--3.INTERSECT 交集,返回两个结果集中共有了部分

idle> select empno,ename,job,hiredate,sal from emp
intersect
select empno,ename,job,hiredate,sal from emp2;
  2    3  
     EMPNO ENAME      JOB	HIREDATE	  SAL
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK	1980-12-17	  800
      7654 MARTIN     SALESMAN	1981-09-28	 1250
      7839 KING       PRESIDENT 1981-11-17	 5000
      7876 ADAMS      CLERK	1987-05-23	 1100

--4.MINUS 补集,前一个结果集减后一个结果集后的结果

idle> select empno as "EmployeeNo" ,ename  "EmployeeName",job  "Job" ,hiredate as "HireDate",sal "Sal" from emp
minus
select empno,ename,job,hiredate,sal from emp2
order by "Sal";
  2    3    4  
EmployeeNo EmployeeNa Job	HireDate	  Sal
---------- ---------- --------- ---------- ----------
      7900 JAMES      CLERK	1981-12-03	  950
      7521 WARD       SALESMAN	1981-02-22	 1250
      7934 MILLER     CLERK	1982-01-23	 1300
      7844 TURNER     SALESMAN	1981-09-08	 1500
      7499 ALLEN      SALESMAN	1981-02-20	 1600
      7782 CLARK      MANAGER	1981-06-09	 2450
      7698 BLAKE      MANAGER	1981-05-01	 2850
      7566 JONES      MANAGER	1981-04-02	 2975
      7788 SCOTT      ANALYST	1987-04-19	 3000
      7902 FORD       ANALYST	1981-12-03	 3000

10 rows selected.

07.SQL 基础--> 集合运算(UNION 与UNION ALL)

标签：manager   name   表达式   部分   交集   analyst   size   顺序   tables