sql中UNION和UNION ALL的区别

SELECT T102.CI_ID AS RES_ID,
T102.CITYPE_ID AS RESTYPE_ID,
T102.ASSET_NUMB AS RES_CODE,
'' AS RES_E_NAME,
T102.CI_NAME AS RES_NAME,
'' AS APPNAME_NAME,
'' AS ORIGINAL_ID,
T102.ASSET_BRAND AS BRAND_ID,
T102.ASSET_BRAND_NAME AS BRAND_NAME,
T102.SERIES AS SERIES_ID,
T102.SERIES_NAME AS SERIES_NAME,
T102.MODEL AS SPECMODEL_ID,
T102.MODEL_NAME AS SPECMODEL_NAME,
T102.NETWORK AS SECZONE_ID,
T102.INSTALSITE_NAME AS COMPUTERROOM_NAME,
T102.INSTALSITE AS COMPUTERROOM_ID,
T102.CABINET_NO AS CABINET_ID,
T102.CABINET_NO_NAME AS CABINET_NAME,
'' AS RES_GRADE,
'' AS IPV4_DESC,
'' AS BG_ID,
T102.RUN_CORP_CODE AS BC_ID,
T102.REMARKS AS RES_DESC,
T102.ENABLED_STATUS,
T102.DELETED_FLAG,
'' AS OSTYPE_ID,
'' AS OS_VERSION_NO,
T102.CREATED_AT,
T102.UPDATED_AT,
T102.DELETED_AT
FROM CI_T10203 T102
WHERE T102.DELETED_FLAG = 'N' --磁带库
UNION ALL
SELECT T102.CI_ID AS RES_ID,
T102.CITYPE_ID AS RESTYPE_ID,
T102.ASSET_NUMB AS RES_CODE,
'' AS RES_E_NAME,
T102.CI_NAME AS RES_NAME,
'' AS APPNAME_NAME,
'' AS ORIGINAL_ID,
T102.ASSET_BRAND AS BRAND_ID,
T102.ASSET_BRAND_NAME AS BRAND_NAME,
T102.SERIES AS SERIES_ID,
T102.SERIES_NAME AS SERIES_NAME,
T102.MODEL AS SPECMODEL_ID,
T102.MODEL_NAME AS SPECMODEL_NAME,
T102.NETWORK AS SECZONE_ID,
T102.INSTALSITE_NAME AS COMPUTERROOM_NAME,
T102.INSTALSITE AS COMPUTERROOM_ID,
T102.CABINET_NO AS CABINET_ID,
T102.CABINET_NO_NAME AS CABINET_NAME,
'' AS RES_GRADE,
'' AS IPV4_DESC,
'' AS BG_ID,
T102.RUN_CORP_CODE AS BC_ID,
T102.REMARKS AS RES_DESC,
T102.ENABLED_STATUS,
T102.DELETED_FLAG, '' AS OSTYPE_ID,
'' AS OS_VERSION_NO,
T102.CREATED_AT,
T102.UPDATED_AT,
T102.DELETED_AT FROM CI_T10204 T102
WHERE T102.DELETED_FLAG = 'N'

drop table if exists student;
create table student
(
id int primary key,
name varchar2(50) not null,
score number not null
);
insert into student values(1,'Aaron',78);
insert into student values(2,'Bill',76);
insert into student values(3,'Cindy',89);
insert into student values(4,'Damon',90);
insert into student values(5,'Ella',73);
insert into student values(6,'Frado',61);
insert into student values(7,'Gill',99);
insert into student values(8,'Hellen',56);
insert into student values(9,'Ivan',93);
insert into student values(10,'Jay',90);
commit;

select * from student where id < 4
union
select * from student where id > 2 and id < 6

--查询结果为

1    Aaron    78
2    Bill     76
3    Cindy    89
4    Damon    90
5    Ella     73

select * from student where id < 4
union all
select * from student where id > 2 and id < 6
--查询结果为

1    Aaron    78
2    Bill    76
3    Cindy    89
3    Cindy    89
4    Damon    90
5    Ella    73