Like Share Discussion Bookmark Smile

J.J. Huang   2020-07-08   MySQL   瀏覽次數:

MySQL - 第十七章 | 組合查詢

組合查詢

多數SQL查詢都只包含從一個或多個表中返回資料的單條SELECT語 句。MySQL也允許執行多個查詢(多條SELECT語句),並將結果作為單個 查詢結果集返回。這些組合查詢通常稱為並(union)或復合查詢
(compound query)。 有兩種基本情況,其中需要使用組合查詢:

  • 在單個查詢中從不同的表返回類似結構的資料;
  • 對單個表執行多個查詢,按單個查詢返回資料。

提示:組合查詢和多個WHERE條件 多數情況下,組合相同表的兩個查詢完成的工作與具有多個WHERE子句條件的單條查詢完成的工作相同。換句話說,任何具有多個WHERE子句的SELECT語句都可以作為一個組合查詢給出。 這兩種技術在不同的查詢中性能也不同。因此,應該試一下這 兩種技術,以確定對特定的查詢哪一種性能更好。

建立組合查詢

可用UNION操作符來組合數條SQL查詢。利用UNION,可給出多條SELECT語句,將它們的結果組合成單個結果集。

使用UNION

UNION的使用很簡單。所需做的只是給出每條SELECT語句,在各條語 句之間放上關鍵字UNION。
舉一個例子,假如需要價格小於等於5的所有物品的一個列表,而且 還想包括供應商1001和1002生產的所有物品(不考慮價格)。當然,可以利用WHERE子句來完成此工作,不過這次我們將使用UNION。

建立UNION涉及編寫多條SELECT語句。首先來看單條語句。

1
2
3
4
5
6
7
8
9
10
mysql> SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id in (1001, 1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
5 rows in set (0.00 sec)

分析:第一條SELECT檢索價格不高於5的所有物品。第二條SELECT使用IN找出供應商1001和1002生產的所有物品。

為了組合這兩條語句,按如下進行:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
-> UNION
-> SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id in (1001, 1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
8 rows in set (0.00 sec)

分析:這條語句由前面的兩條SELECT語句組成,語句中用UNION關鍵字分隔。UNION指示MySQL執行兩條SELECT語句,並把輸出組合成單個查詢結果集。

作為參考,這裡給出使用多條WHERE子句而不是使用UNION的相同查詢:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 OR vend_id in (1001, 1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
8 rows in set (0.00 sec)

在這個簡單的例子中,使用UNION可能比使用WHERE子句更為複雜。但對於更複雜的過濾條件,或者從多個表(而不是單個表)中檢索資料的情形,使用UNION可能會使處理更簡單。

UNION規則

正如所見,並是非常容易使用的。但在進行並時有幾條規則需要注意。

  • UNION必須由兩條或兩條以上的SELECT語句組成,語句之間用關鍵字UNION分隔(因此,如果組合4條SELECT語句,將要使用3個UNION關鍵字)。
  • UNION中的每個查詢必須包含相同的列、表達式或聚集函數(不過各個列不需要以相同的次序列出)。
  • 列資料類型必須兼容:類型不必完全相同,但必須是DBMS可以隱含地轉換的類型(例如,不同的數值類型或不同的日期類型)。

如果遵守了這些基本規則或限制,則可以將並用於任何資料檢索任務。

包含或取消重復的行

考察一下上方所用的範例SELECT語句。我們注意到,在分別執行時;

  • 第一條SELECT語句返回4行
  • 第二條SELECT語句返回5行。
  • 但在用UNION組合兩條SELECT語句後,只返回了8行而不是9行。

UNION從查詢結果集中自動去除了重復的行(換句話說,它的行為與單條SELECT語句中使用多個WHERE子句條件一樣)。因為供應商1002生產的一種物品的價格也低於5,所以兩條SELECT語句都返回該行。在使用UNION時,重復的行被自動取消。
這是UNION的默認行為,但是如果需要,可以改變它。事實上如果 想返回所有匹配行,可使用UNION ALL而不是UNION。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
-> UNION ALL
-> SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id in (1001, 1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
9 rows in set (0.00 sec)

分析:使用UNION ALL,MySQL不取消重復的行。因此這裡的例子返回9行,其中有一行出現兩次。

提示:UNION與WHERE 開始時說過,UNION幾乎總是完成與多個WHERE條件相同的工作。UNION ALL為UNION的一種形式,它完成WHERE子句完成不了的工作。如果確實需要每個條件的匹配行全部出現(包括重復行),則必須使用UNION ALL而不是WHERE。

對組合查詢結果排序

SELECT語句的輸出用ORDER BY子句排序。在用UNION組合查詢時,只能使用一條ORDER BY子句,它必須出現在最後一條SELECT語句之後。對於結果集,不存在用一種方式排序一部分,而又用另一種方式排序另一部分的情況,因此不允許使用多條ORDER BY子句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
-> UNION
-> SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id in (1001, 1002)
-> ORDER BY vend_id, prod_price;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | FC | 2.50 |
| 1003 | TNT1 | 2.50 |
| 1003 | SLING | 4.49 |
+---------+---------+------------+
8 rows in set (0.00 sec)

分析:這條UNION在最後一條SELECT語句後使用了ORDER BY子句。雖然ORDER BY子句似乎只是最後一條SELECT語句的組成部分,但實際上MySQL將用它來排序所有SELECT語句返回的所有結果。

說明:組合不同的表 為使表述比較簡單,例子中的組合查詢使用的均是相同的表。但是其中使用UNION的組合查詢可以應用不同的表。

結語

重點就是可以利用UNION,可把多條查詢的結果作為一條組合查詢返回,而且不管它們的結果中包含還是不包含重復。另外使用UNION可極大地簡化複雜的WHERE子句。

註:以上參考了
MySQL必知必会 MySQL Crash Course