【SQL】集合运算 The Set Operators

LittleZhao 2021-08-16

1251

定义

集合运算符包括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> 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

需要注意前后顺序

数据库

文章转载自LittleZhao，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。