SQL UNION 運算子
UNION 運算子用來將兩個(以上) SQL 查詢的結果合併起來,而由 UNION 查詢中各別 SQL 語句所產生的欄位。
UNION 查詢只會返回不同值的資料列,有如 SELECT DISTINCT 。 UNION 就是像是 OR (聯集),將結果紀錄存在於第一個查詢結果集或第二個查詢結果集中,就會被取出。
UNION 與 JOIN 不同的地方在於,JOIN 是作橫向結合 (合併多個資料表的各欄位);而 UNION 則是作垂直結合 (合併多個資料表中的紀錄)。
SQL UNION 查詢返回的資料集欄位名稱,通常會依據第一個 SELECT 查詢的欄位名稱
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
CustomerID |
CompanyName |
CustomerName |
Address |
City |
PostalCode |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitucion 2222 |
Mexico D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taqueria |
Antonio Moreno |
Mataderos 2312 |
Bergamo |
05023 |
Mexico |
SupplierID |
SupplierName |
ContactName |
Address |
City |
PostalCode |
Country |
1 |
Exotic Liquid |
Charlotte Cooper |
49 Gilbert St. |
London |
EC1 |
UK |
2 |
New Orleans Cajun Delights |
Shelley Burke |
P.O. Box 78934 |
Mexico D.F. |
70117 |
Mexico |
3 |
Grandma Kelly's Homestead |
Regina Murphy |
707 Oxford Rd. |
Caracas |
48104 |
Mexico |
結果:
City |
Berlin |
Mexico D.F. |
Bergamo |
London |
Caracas |
SQL UNION ALL 會列出所有的資料,不論是否重複
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;
結果:
City |
Berlin |
Mexico D.F. |
Bergamo |
London |
Mexico D.F. |
Caracas |
SQL UNION ALL 與 WHERE 條件
SELECT City, Country FROM Customers WHERE Country='Mexico'
UNION ALL
SELECT City, Country FROM Suppliers WHERE Country='Mexico' ORDER BY City;