集合运算符之全集、交集、补集【weber出品必属精品】

2024-03-11 20:25:55

集合的概念

与数学中的全集、交集、补集的概念是一样的

常用的集合运算符

集合运算符的作用：把两个查询构造为一个联合查询

1. 全集：求连个查询的全集

union all：将两个查询的所有数据全部列出，不进行排序，不去掉重复的部分

SQL> create table t1 as select * from emp where deptno in (,);

Table created.

SQL> create table t2 as select * from emp where deptno in (,);

Table created.

SQL> select * from t1;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

       SMITH      CLERK           -DEC-

       JONES      MANAGER           -APR-

       CLARK      MANAGER           -JUN-

       SCOTT      ANALYST           -APR-

       KING       PRESIDENT        -NOV-

       ADAMS      CLERK           -MAY-

       FORD       ANALYST           -DEC-

       MILLER     CLERK           -JAN-                   

 rows selected.

SQL> select * from t2;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

       SMITH      CLERK           -DEC-

       ALLEN      SALESMAN           -FEB-

       WARD       SALESMAN           -FEB-

       JONES      MANAGER           -APR-

       MARTIN     SALESMAN           -SEP-

       BLAKE      MANAGER           -MAY-

       SCOTT      ANALYST           -APR-

       TURNER     SALESMAN           -SEP-

       ADAMS      CLERK           -MAY-

       JAMES      CLERK           -DEC-

       FORD       ANALYST           -DEC-                   

 rows selected.

SQL> select * from t1

    union all

    select * from t2;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

       SMITH      CLERK           -DEC-

       JONES      MANAGER           -APR-

       CLARK      MANAGER           -JUN-

       SCOTT      ANALYST           -APR-

       KING       PRESIDENT        -NOV-

       ADAMS      CLERK           -MAY-

       FORD       ANALYST           -DEC-

       MILLER     CLERK           -JAN-

       SMITH      CLERK           -DEC-

       ALLEN      SALESMAN           -FEB-

       WARD       SALESMAN           -FEB-

       JONES      MANAGER           -APR-

       MARTIN     SALESMAN           -SEP-

       BLAKE      MANAGER           -MAY-

       SCOTT      ANALYST           -APR-

       TURNER     SALESMAN           -SEP-

       ADAMS      CLERK           -MAY-

       JAMES      CLERK           -DEC-

       FORD       ANALYST           -DEC-                   

 rows selected.

union：将两个查询的所有数据进行显示，但是重复的部分只显示一次，而且要按照第一个查询的第一列进行升序排序

SQL> select * from t1

    union

    select * from t2;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

       SMITH      CLERK           -DEC-

       ALLEN      SALESMAN           -FEB-

       WARD       SALESMAN           -FEB-

       JONES      MANAGER           -APR-

       MARTIN     SALESMAN           -SEP-

       BLAKE      MANAGER           -MAY-

       CLARK      MANAGER           -JUN-

       SCOTT      ANALYST           -APR-

       KING       PRESIDENT        -NOV-

       TURNER     SALESMAN           -SEP-

       ADAMS      CLERK           -MAY-

       JAMES      CLERK           -DEC-

       FORD       ANALYST           -DEC-

       MILLER     CLERK           -JAN-                   

 rows selected.

union all与union的性能哪个更高？

union all性能更高：因为union all 不进行排序，也不去重

2. 交集：INTERSECT

SQL> select * from t1

    intersect

    select * from t2;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

       SMITH      CLERK           -DEC-

       JONES      MANAGER           -APR-

       SCOTT      ANALYST           -APR-

       ADAMS      CLERK           -MAY-

       FORD       ANALYST           -DEC-

3. 补集：MINUS
查询select * from e2的补集：

SQL> select * from t1

    minus

    select * from t2;

     EMPNO ENAME      JOB           MGR HIREDATE        SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

       CLARK      MANAGER           -JUN-

       KING       PRESIDENT        -NOV-

       MILLER     CLERK           -JAN-

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

*

第  行出现错误:

ORA-: 查询块具有不正确的结果列数

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

第  行出现错误:

ORA-: 表达式必须具有与对应表达式相同的数据类型

2. 可以使用括号来改变执行的顺序

select * from e1

intersect

select * from e2

union

select * from e2;

ENAME  EMPNO   SAL DEPTNO

------ ----- ----- ------

ADAMS

ALLEN

BLAKE

FORD

JAMES

JONES

MARTIN

SCOTT

SMITH

TURNER

WARD           

已选择11行。

select * from e1

intersect

(select * from e2

union

select * from e2);

ENAME  EMPNO   SAL DEPTNO

------ ----- ----- ------

ADAMS

FORD

JONES

SCOTT

SMITH

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

----- ------ ----- ------

  WARD

  TURNER

  SMITH

  SCOTT

  MARTIN

  JONES

  JAMES

  FORD

  BLAKE

  ALLEN

  ADAMS

可以使用第一个查询语句的列名、别名、或位置（号）

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

----- ---------- ----- ------

  WARD

  TURNER

  SMITH

  SCOTT

  MARTIN

  JONES

  JAMES

  FORD

  BLAKE

  ALLEN

  ADAMS            

已选择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

         *

第  行出现错误:

ORA-: "NAME2": 标识符无效

select empno,ename,sal,deptno from e2

union

select empno,to_char(sal) salary, null,deptno from e2

order by ;

EMPNO ENAME    SAL DEPTNO

----- ------ ----- ------

  ADAMS

  ALLEN

  BLAKE

  FORD

  JAMES

  JONES

  MARTIN

  SCOTT

  SMITH

  TURNER

  WARD

第一个查询语句的列名出现在结果中

select empno,to_char(sal) salary, null,deptno from e2

union

select empno,ename,sal,deptno from e2

order by

/

EMPNO SALARY                                         NULL DEPTNO

----- ---------------------------------------- ---------- ------

  ADAMS

  ALLEN

  BLAKE

  FORD

  JAMES

  JONES

  MARTIN

  SCOTT

  SMITH

  TURNER

  WARD

查询语句的匹配：个数和数据类型的匹配

select empno,ename,sal,deptno from e1

union

select ,to_char(sal),null, from e2;

EMPNO ENAME    SAL DEPTNO

----- ------ ----- ------

  SMITH

  JONES

  CLARK

  SCOTT

  KING

  ADAMS

  FORD

  MILLER

码农公寓

相关文章