# SQL Union運算符

#### 1. SQL UNION運算符簡介

`UNION`運算符將兩個或多個SELECT語句的結果集合併到一個結果集中。 以下語句說明了如何使用`UNION`運算符組合兩個查詢的結果集：

``````SELECT
column1, column2
FROM
table1
UNION [ALL]
SELECT
column3, column4
FROM
table2;``````

`SELECT`語句返回的列必須具有相同或可轉換的數據類型，大小和相同的順序。

#### 2. SQL UNION示例

``````SELECT
id
FROM
A;``````

``````SELECT
id
FROM
A;``````

``````mysql> SELECT
id
FROM
B;
+----+
| id |
+----+
|  2 |
|  3 |
+----+
2 rows in set``````

``````SELECT
id
FROM
a
UNION
SELECT
id
FROM
b;``````

``````+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set``````

#### 3. SQL UNION ALL示例

``````SELECT
id
FROM
a
UNION ALL
SELECT
id
FROM
b;``````

``````+----+
| id |
+----+
|  1 |
|  2 |
|  2 |
|  3 |
+----+
4 rows in set``````

4. SQL UNION帶有ORDER BY示例

``````SELECT
id
FROM
a
UNION
SELECT
id
FROM
b
ORDER BY id DESC;``````

``````+----+
| id |
+----+
|  3 |
|  2 |
|  1 |
+----+
3 rows in set``````

• 首先，分別執行每個`SELECT`語句。
• 其次，組合結果集並刪除重複行以創建組合結果集。
• 第三，按`ORDER BY`子句中指定的列對組合結果集進行排序。

``````SELECT
first_name,
last_name
FROM
employees
UNION
SELECT
first_name,
last_name
FROM
dependents
ORDER BY
last_name;``````

``````+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Grace      | Chen      |
| Dan        | Chen      |
| Daniel     | Chen      |
| Matthew    | Chen      |
| John       | Chen      |
| Valli      | Chen      |
| Diana      | Chen      |
| Helen      | Chen      |
| Nancy      | Chen      |
| Karl       | Chen      |
| Matthew    | Han       |
.....
| Guy        | Zhang     |
| Karen      | Zhang     |
| Ed         | Zhao      |
| Britney    | Zhao      |
| Jennifer   | Zhao      |
| Susan      | Zhou      |
| Uma        | Zhou      |
| Bob        | Zhou      |
| Lucille    | Zhou      |
| Michael    | Zhou      |
| Pat        | Zhou      |
+------------+-----------+
70 rows in set``````

0 條評論，你可以發表評論，我們會進行改進