sql中UNION和UNION ALL的區別

寫sql時我們經常會遇到需要把從多張表查詢的集果集進行合併。這時就用到了union。使用union或union all 時一定要保證查詢的列的一致性 。不然sql會報錯。欄位不一致的話可以用單引號來占位。

例：

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'

下麵就來說明union和union all的區別

準備一張測試數據表。註意mysql中的varchar在oracle中是varchar2

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

由此可以看出union和union all的區別在於對重覆數據的處理。

　　union 在進行錶鏈接後會篩選掉重覆的記錄，所以在錶鏈接後會對所產生的集果集進行排序運算，刪除重覆的記錄再返回結果集。實際使用時大部分是不會產生重覆的記錄。

　　union all只是簡單的將兩個結果合併就返回。如果返回的結果集中有重覆的數據，那麼返回的結果集中就包含有重覆數據。

　　從性能上講union all 要比union快很多，它沒有排序去重的耗時。如果表數據量很大，並且可以確定合併的結果集中不會包含重覆數據的話。就使用union all.

　　我一般使用union all.真出現重覆數據了再檢查一下。