sql中UNION和UNION ALL的區別

$>_發表於2018-07-23
SQL

寫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.真出現重複資料了再檢查一下。

相關文章