oracle基础积累-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;

以上,感谢.