【SQL】集合运算 The Set Operators

目录

定义

联合运算

UNION

UNION ALL

交集运算

INTERSECT

差集运算

MINUS

---

定义

集合运算符包括UNION、UNION ALL、INTERSECT、MINUS

所有集合运算符的优先级都相等

如果一条SQL语句中包含多个set运算符，那oracle将从左到右进行求值（除非括号里指定了其他顺序）

 

联合运算

测试数据准备 tb_set01 tb_set02

SQL> create table tb_set01 as select EMPNO,ENAME,JOB,SAL,DEPTNO from emp where 1=2;   Table created.   SQL> insert into tb_set01 select EMPNO,ENAME,JOB,SAL,DEPTNO from emp where rownum<=5;   5 rows created.   SQL> insert into tb_set01 values(null,null,null,null,null);   1 row created.

SQL> create table tb_set02(no,name,work,price,class) as select EMPNO,ENAME,JOB,SAL,DEPTNO from emp where 1=2;   Table created.   SQL> insert into tb_set02 select * from tb_set01 where rownum<=2;   2 rows created.   SQL> insert into tb_set02 select empno+10,ename||'new',job,sal+5000,deptno from tb_set01 where empno is not null and rownum<=3;   3 rows created.   SQL> insert into tb_set02 values(null,null,null,null,null);   1 row created.   SQL> insert into tb_set02 values(null,null,null,null,null);   1 row created.

 

查看测试表数据

SQL> select * from tb_set01;   EMPNO ENAME    JOB         SAL  DEPTNO ----- -------- --------- ----- -------  7369 SMITH    CLERK       800      20  7499 ALLEN    SALESMAN   1600      30  7521 WARD     SALESMAN   1250      30  7566 JONES    MANAGER    2975      20  7654 MARTIN   SALESMAN   1250      30     6 rows selected.   SQL> select * from tb_set02;           NO NAME       WORK           PRICE      CLASS ---------- ---------- --------- ---------- ----------       7369 SMITH      CLERK            800         20       7499 ALLEN      SALESMAN        1600         30       7379 SMITHnew   CLERK           5800         20       7509 ALLENnew   SALESMAN        6600         30       7531 WARDnew    SALESMAN        6250         30       7 rows selected.

 

注意tb_set01有两个空行 tb_set02有一个空行

UNION

从表中返回所有的行，但是除去任何重复的行

SQL> select empno,ename,sal from tb_set01 union select  no,name,price from tb_set02;   EMPNO ENAME      SAL ----- -------- -----  7369 SMITH      800  7379 SMITHnew  5800  7499 ALLEN     1600  7509 ALLENnew  6600  7521 WARD      1250  7531 WARDnew   6250  7566 JONES     2975  7654 MARTIN    1250     9 rows selected.

可以看出union运算不忽略null

两个集合取并集，重复记录保留一份

按照第一个select字段升序显示

两个查询中字段数要相同，类型和名称可以不同

UNION ALL

从表中返回所有的行，保留重复的行

SQL> select empno,ename,sal from tb_set01 union all select no,name,price from tb_set02;   EMPNO ENAME      SAL ----- -------- -----  7369 SMITH      800  7499 ALLEN     1600  7521 WARD      1250  7566 JONES     2975  7654 MARTIN    1250    7369 SMITH      800  7499 ALLEN     1600  7379 SMITHnew  5800  7509 ALLENnew  6600  7531 WARDnew   6250       13 rows selected.

与UNION不同的是结果集不会排序

 

交集运算

INTERSECT

返回多个查询中重复的行，包括null

排列方式和UNION一样按照select的第一个字段显示

SQL> select empno,ename,sal from tb_set01 intersect select no,name,price from tb_set02;   EMPNO ENAME      SAL ----- -------- -----  7369 SMITH      800  7499 ALLEN     1600

SQL> select count(*) from (select empno,ename,sal from tb_set01 intersect select no,name,price from tb_set02);     COUNT(*) ----------          3

 

差集运算

MINUS

返回第一个查询里在第二个查询中没有的行

SQL> select empno,ename,sal from tb_set01 minus select no,name,price from tb_set02;   EMPNO ENAME      SAL ----- -------- -----  7521 WARD      1250  7566 JONES     2975  7654 MARTIN    1250   SQL> select no,name,price from tb_set02 minus select empno,ename,sal from tb_set01;           NO NAME            PRICE ---------- ---------- ----------       7379 SMITHnew         5800       7509 ALLENnew         6600       7531 WARDnew          6250

需要注意前后顺序