集合運算查詢

在資料庫執行多個資料表查詢時，除了使用Join的合併查詢外，也可以使用集合運算:聯集Union、交集Intersect、差集Except來執行兩個資料表的合併。

下面會用這兩個資料表來當作集合範例

student資料表:

Teacher資料表:

其中兩個資料表中，有些人分別是學生又同時是老師(Mike和新西亞)。

集合運算查詢分為三種:

1.聯集 UNION:

將2個資料表的紀錄垂直結合再一起，有重複的資料只會顯示其中一筆，UNION 與 JOIN 不同的地方在於，JOIN 是作橫向結合 (合併多個資料表的各欄位)；而 UNION 則是作垂直結合 (合併多個資料表中的紀錄)。

SQL範例:

現在要將[student]與[Teacher]兩個資料表使用聯集運算取出全部學生及老師的名字，SQL如下:

select 學生 as 姓名  from student 
union
select 老師 from Teacher

全部學生及老師都有被顯示出來，不過在[student]與[Teacher]裡都有的新西亞以及Mike學生和老師，在查詢結果中只會出現一筆，而不會重複出現相同的姓名。

而UNION查詢的資料集欄位名稱，通常會依據第一個Select所查詢的欄位來當名稱，不過上述SQL語法使用as姓名來代替欄位名稱 學生 了。結果如下圖:

Union all

union all與union的差異在於，union會將重複的自動踢掉，而union all將會保留重複的。

也就是說假如想要 新西亞 和 Mike 在[student]與[Teacher]的兩個資料庫內的資料都要出現，那就需要使用 Union all ，顯示結果就會重複出現 新西亞 與 Mike。如下圖所示:

select 學生 as 姓名  from student 
union all
select 老師 from Teacher

2.交集 INTERSECT:

將兩個資料表相同的紀錄取出來，且有重複的資料只會顯示其中一筆。

SQL範例:

現在要將[student]與[Teacher]兩個資料表使用交集運算取出兩個資料表是老師又同時是學生的姓名，SQL如下:

select 學生 as 姓名  from student 
intersect
select 老師 from Teacher

結果顯示兩個資料表都有的相同姓名的Mike及新西亞，且也都各只顯示一筆。結果如下圖:

3.差集 EXCEPT:

只取出第一個select指令但是不存在第二個select指令的紀錄。

SQL範例

現在將[student]與[Teacher]兩個資料表使用差集運算取出student資料表的學生姓名，但不能同時也是老師的學生姓名(只取出純學生，不包含同時也是老師的學生XD)，有點像left join。SQL如下:

select 學生 as 姓名  from student 
except
select 老師 from Teacher

顯示結果只出現student的學生姓名，不過顯示結果的姓名不會有在Teacher資料表內的姓名，所以在兩個資料表都有的Mike和新西亞就不會出現(Bye~)。結果如下圖:

另外差集 EXCEPT及只適用於 SQL Server，在Oracle的交集則是使用 MINUS，使用方法與EXCEP一樣; 而MySQL不支援EXCEPT及MINUS，則要使用left join。詳細介紹可以看這個網頁。