ARTS Share9 Oracle中的Union、Union All、Intersect、Minus

ゝ一纸荒年。 2022-01-30 06:57 234阅读 0赞

这篇文章说一个关于`ORACLE`中的`UNION`、`UNION ALL`、`INTERSECT`、`MINUS`的解释和用法：

先创建一张`TABLE`，用来做实验：

建表语句如下所示：

create table student
    (
    id int primary key,
    name nvarchar2(50) not null,
    score number not null
    );
    
    insert into student values(1,'zhangsan',78);
    insert into student values(2,'lisi',76);
    insert into student values(3,'wangwu',89);
    insert into student values(4,'zhaoliu',90);
    insert into student values(5,'xiaohua',73);
    insert into student values(6,'xiaoming',61);
    insert into student values(7,'xiaoli',99);
    insert into student values(8,'wangmazi',56);
    insert into student values(9,'huqingniu',93);
    insert into student values(10,'zhangwuji',90);
    
    commit;

查看插入表中的所有数据：

SELECT * FROM STUDENT;

结果如下图所示：

![在这里插入图片描述][20190518151710663.png]

(1)使用`UNION`对两个结果集进行连接，`SQL`如下所示，结果如下图所示

SELECT * FROM STUDENT T
    WHERE T.ID<4
    UNION 
    SELECT * FROM STUDENT T
    WHERE T.ID>2 AND T.ID<6;

![在这里插入图片描述][2019051815163329.png]

通过上图我们可以看出来，使用`UNION`返回了5条数据。

（2）使用`UNION ALL`对两个结果集连接，`SQL`如下所示，结果如下图所示：

SELECT * FROM STUDENT T
    WHERE T.ID<4
    UNION ALL
    SELECT * FROM STUDENT T
    WHERE T.ID>2 AND T.ID<6;

![在这里插入图片描述][20190518151644378.png]  
通过上图我们可以看出来，使用`UNION ALL`返回了6条数据，与（1）中的结果相比，多了一条`ID`为3的数据。

为什么会这样，我们看一下我们的`SQL`，我们的语句是由两部分查询结果集进行组合的，所以分别单独看一下每一部分的`SQL`：

第一部分:`SELECT * FROM STUDENT T WHERE T.ID<4` 这句`SQL`会返回`ID`小于4的记录，单独执行应该返回3条数据，`ID`为1,2,3的记录  
第二部分：`SELECT * FROM STUDENT T WHERE T.ID>2 AND T.ID<6`这句`SQL`会返回`ID`大于2小于6的记录，也就是3条记录，`ID`为3,4,5的记录

当我们使用`UNION`的时候，返回的记录`ID`分别为1,2,3,4,5，并不是上面分析的两部分直接组合，而是将两部分中相同的记录进行了去重，只留下重复记录中的一条数据。  
当我们使用`UNION ALL`的时候，返回的记录`ID`分别是1,2,3,3,4,5，是两部分的记录集直接组合，并没有对重复的记录进行去重。

所以通过上面这个例子，我们可以知道：`UNION`会对记录集中重复记录去重，`UNION ALL`不会对记录集中的重复记录去重，所以在写`SQL`文的时候，需要考虑到底是需要去重还是不去重，去重选择前者，不去重选择后者，当然如果选择前者的话，那么`SQL`性能会低于后者，因为后者只是对记录的展示，而前者需要对查询的记录集中的重复记录进行去重，多了一个步骤，所以会影响`SQL`的性能。

（3）对于（1）和（2）中的`SQL`连接顺序调整一下，原来是先筛选`ID`小于4的记录集连接筛选`ID`大于2小于6的记录集，现在我们颠倒一下，`SQL`如下图所示：

SELECT * FROM STUDENT T
    WHERE T.ID>2 AND T.ID<6
    UNION 
    SELECT * FROM STUDENT T
    WHERE T.ID<4;

结果如图所示：

![在这里插入图片描述][20190518151729897.png]

SELECT * FROM STUDENT T
    WHERE T.ID>2 AND T.ID<6
    UNION ALL
    SELECT * FROM STUDENT T
    WHERE T.ID<4;

结果如图所示：  
![在这里插入图片描述][20190518151738857.png]  
通过（3）中的第一段`SQL`和结果图片，可以看到返回了5条记录，但是细心点可以发现它返回的记录进行了排序，因为我们`SQL`写的是先筛选 `ID`大于2小于6的，但是结果却和我们预想的不一样；第2段`SQL`，返回了6条记录，按照我们预想的一样，记录集的`ID`是3,4,5,1,2,3，`SQL`是怎么写的，结果集就按照其进行输出。

通过（3），我们应该可以发现，`UNION`会对记录集进行排序，`UNION ALL`不会对记录集进行排序。

（4）从（3）中知道了使用`UNION`的话，会对结果集进行排序，我们从（3）中第一段`SQL`结果集发现是按照`ID`进行升序排序的，那么为什么会按照`ID`进行排序，我们试图修改一下`SQL`，再看是否还是按照`ID`排序：

修改后的`SQL`文：

SELECT T.SCORE,T.ID,T.NAME FROM STUDENT T
    WHERE T.ID>2 AND T.ID<6
    UNION 
    SELECT T.SCORE,T.ID,T.NAME FROM STUDENT T
    WHERE T.ID<4;

对应的记录集如下图所示：  
![在这里插入图片描述][2019051815175271.png]  
通过这个记录集，可以发现，这个查询记录是按照`SCORE`进行排序的，那么我们就可以认为，`UNION`的排序，是按照查询列的字段名字顺序排序的，比如

SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLENAME T 
    UNION
    SELECT COLUMN1,COLUMN2,COLUMN3 FROM TABLENAME T

结果集就是按照`ORDER BY COLUMN1,COLUMN2,COLUMN3`进行排序的。

为了验证上面我们的说话，`UNION`会按照查询的列的字段名字升序排序，下面再看一个例子：

SELECT SCORE, ID, NAME
      FROM STUDENT
     WHERE ID > 2
    UNION
    SELECT SCORE, ID, NAME
      FROM STUDENT
     WHERE ID < 4

结果如图所示：  
![在这里插入图片描述][20190518151805456.png]

通过上图可以看出，的确是按照查询字段进行排序，当第一个排序字段值相同的时候，按照第二个字段的值进行排序，以此类推。

如果我们需要让查询记录集按照我们的要求进行排序，那么就需要在整段`SQL`的末尾写上`ORDER BY COLUMNS`，这样的话，记录集就是按照我们的要求进行排序的，测试如下：

SELECT SCORE, ID, NAME
      FROM STUDENT
     WHERE ID > 2
    UNION
    SELECT SCORE, ID, NAME
      FROM STUDENT
     WHERE ID < 4
     ORDER BY ID DESC

运行结果如图所示：  
![在这里插入图片描述][20190518151817463.png]

总结：

1.  `UNION`会对记录集中的重复记录进行去重，并且会按照查询的字段列进行默认升序排序
2.  `UNION ALL`不会对记录集中的重复记录进行去重，只会将查询记录组合显示出来，也不会进行排序
3.  使用`UNION`的记录集需要排序，可以在`SQL`的最末端，写`ORDER BY COLUMNS`，你需要排序的字段名称
4.  `Intersect`对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序
5.  `Minus`对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzQzMjU_size_16_color_FFFFFF_t_70]

[20190518151710663.png]: /images/20220130/e2d107bd8b664a0ebd009370562a5b18.png
[2019051815163329.png]: /images/20220130/7eda5e20db9c4b86b7551227a8fa3e9e.png
[20190518151644378.png]: /images/20220130/45bb315af9204a44b0889ddb07a9d086.png
[20190518151729897.png]: /images/20220130/6ee57ff805774cbd8e208493bdb0d002.png
[20190518151738857.png]: /images/20220130/2e3473e94c5c4ef48938a5677f8cc138.png
[2019051815175271.png]: /images/20220130/1ee14d2d535e40fdb533b8eea6146bcc.png
[20190518151805456.png]: /images/20220130/24b14e8428a1473d81fe34df02f98c0c.png
[20190518151817463.png]: /images/20220130/a0b99bae3e5646648d7a6f26b7ee9ecc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzQzMjU_size_16_color_FFFFFF_t_70]: /images/20220130/331a9e7eb5af40ad8542e7fa72bcf6ed.png