oracle基础积累-union和union all区别

最新推荐文章于 2025-09-15 08:55:34 发布

原创最新推荐文章于 2025-09-15 08:55:34 发布 · 423 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#union #union all #union和union all

L01-数据库专栏收录该内容

41 篇文章

订阅专栏

本文详细解析了Oracle数据库中Union与Union All的区别与用法。Union操作返回两个查询结果的并集，去除重复记录并按默认规则排序；而Union All则保留所有记录，包括重复项，且不进行排序。通过具体示例，展示了如何使用这两个操作符来整合来自不同表的数据。

场景:
基础知识查询.
环境:
Oracle Database 11g; PL/SQL Developer.
1.数据
1.1 表一 SENSOR_COLLECT_DATA_A

1.2 表二 SENSOR_COLLECT_DATA_B

2. union
union 对两个结果集进行并集操作,不包括重复记录,按照默认规则的进行排序.

 select *
  from sensor_collect_data_a  
union
select *
  from sensor_collect_data_b

3. union all
union all 对两个结果集进行并集操作,包括重复记录,不进行排序.

 select *
  from sensor_collect_data_a
union all
select *
  from sensor_collect_data_b

4附建表语句
4.1 建表语句一

create table SENSOR_COLLECT_DATA_A
(
  sensor_id NUMBER(16) not null,
  region    VARCHAR2(16) not null,
  s1        NUMBER(6,3),
  s2        NUMBER(6,3),
  s3        NUMBER(6,3)
);
comment on table SENSOR_COLLECT_DATA_A
  is '传感器采集数据';
comment on column SENSOR_COLLECT_DATA_A.sensor_id
  is '数据id实体唯一标识';
comment on column SENSOR_COLLECT_DATA_A.region
  is '传感器安装区域';
comment on column SENSOR_COLLECT_DATA_A.s1
  is '传感器采集的值1';
comment on column SENSOR_COLLECT_DATA_A.s2
  is '传感器采集的值2';
comment on column SENSOR_COLLECT_DATA_A.s3
  is '传感器采集的值3';
insert into SENSOR_COLLECT_DATA_A (sensor_id, region, s1, s2, s3)
values (20191618, '2019', 2.22, 3.22, 3.33);
insert into SENSOR_COLLECT_DATA_A (sensor_id, region, s1, s2, s3)
values (20191619, '2019', 3.22, 3.33, 3.55);
insert into SENSOR_COLLECT_DATA_A (sensor_id, region, s1, s2, s3)
values (20191620, '2019', 1.22, 2.22, 3.22);
commit;

4.2 建表语句二

create table SENSOR_COLLECT_DATA_B
(
  sensor_id NUMBER(16) not null,
  region    VARCHAR2(16) not null,
  s1        NUMBER(6,3),
  s2        NUMBER(6,3),
  s3        NUMBER(6,3)
);
comment on table SENSOR_COLLECT_DATA_B
  is '传感器采集数据';
comment on column SENSOR_COLLECT_DATA_B.sensor_id
  is '数据id实体唯一标识';
comment on column SENSOR_COLLECT_DATA_B.region
  is '传感器安装区域';
comment on column SENSOR_COLLECT_DATA_B.s1
  is '传感器采集的值1';
comment on column SENSOR_COLLECT_DATA_B.s2
  is '传感器采集的值2';
comment on column SENSOR_COLLECT_DATA_B.s3
  is '传感器采集的值3';
insert into SENSOR_COLLECT_DATA_B (sensor_id, region, s1, s2, s3)
values (20191617, '2019', 1.22, 2.22, 3.22);
insert into SENSOR_COLLECT_DATA_B (sensor_id, region, s1, s2, s3)
values (20191616, '2019', 1.33, 2.33, 3.33);
insert into SENSOR_COLLECT_DATA_B (sensor_id, region, s1, s2, s3)
values (20191618, '2019', 2.22, 3.22, 3.33);
commit;

以上,感谢.