17. 使用MySQL之組合查詢

hisun9發表於2024-11-04
MySql

1. 組合查詢

多數SQL查詢都只包含從一個或多個表中返回資料的單條SELECT語句。MySQL也允許執行多個查詢（多條SELECT語句），並將結果作為單個查詢結果集返回。這些組合查詢通常稱為並（union）或複合查詢（compound query）。

有兩種基本情況，其中需要使用組合查詢：

  • 在單個查詢中從不同的表返回類似結構的資料；

  • 對單個表執行多個查詢，按單個查詢返回資料。

補充：

  • 組合查詢和多個WHERE條件：

    多數情況下，組合相同表的兩個查詢完成的工作與具有多個WHERE子句條件的單條查詢完成的工作相同。換句話說，任何具有多個WHERE子句的SELECT語句都可以作為一個組合查詢給出，在下文中可以看到這一點。

    這兩種技術在不同的查詢中效能也不同。因此，應該試一下這兩種技術，以確定對特定的查詢哪一種效能更好。

2. 建立組合查詢

可用UNION運算子來組合數條SQL查詢。利用UNION，可給出多條SELECT語句，將它們的結果組合成單個結果集。

2.1 使用UNION

UNION的使用很簡單。所需做的只是給出每條SELECT語句，在各條語句之間放上關鍵字UNION。

舉一個例子：

假如需要價格小於等於5的所有物品的一個列表，而且還想包括供應商1001和1002生產的所有物品（不考慮價格）。

當然可以利用WHERE子句來完成此工作，不過這次我們將使用UNION。

正如所述，建立UNION涉及編寫多條SELECT語句。首先來看單條語句：

  • 第一條SELECT檢索價格不高於5的所有物品。

    select vend_id, prod_id, prod_price
from products
where prod_price <= 5;

    輸出如下：

    img

  • 第二條SELECT使用IN找出供應商1001和1002生產的所有物品。

    select vend_id, prod_id, prod_price
from products
where vend_id in (1001, 1002);

    輸出如下：

    img

為了組合這兩條語句，按如下進行：

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);

輸出如下：

img

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

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

select vend_id, prod_id, prod_price
from products
where prod_price <= 5
	or vend_id in (1001, 1002);

輸出如下：

img

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

2.2 UNION規則

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

  • UNION必須由兩條或兩條以上的SELECT語句組成，語句之間用關鍵字UNION分隔（因此，如果組合4條SELECT語句，將要使用3個UNION關鍵字）。

  • UNION中的每個查詢必須包含相同的列、表示式或聚集函式（不過各個列不需要以相同的次序列出）。

  • 列資料型別必須相容：型別不必完全相同，但必須是DBMS可以隱含地轉換的型別（例如，不同的數值型別或不同的日期型別）。

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

2.3 包含或取消重複的行

請返回到2.1節，考察一下所用的樣例SELECT語句。我們注意到，在分別執行時，第一條SELECT語句返回4行，第二條SELECT語句返回5行。但在用UNION組合兩條SELECT語句後，只返回了8行而不是9行。

UNION從查詢結果集中自動去除了重複的行（換句話說，它的行為與單條SELECT語句中使用多個WHERE子句條件一樣）。

因為供應商1002生產的一種物品的價格也低於5，所以兩條SELECT語句都返回該行。在使用UNION時，重複的行被自動取消。

這是UNION的預設行為，但是如果需要，可以改變它。事實上，如果想返回所有匹配行，可使用UNION ALL而不是UNION。

比如：

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);

輸出如下：

img

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

補充：

UNION與WHERE：

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

2.4 對組合查詢結果排序

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

下面的例子排序前面UNION返回的結果：

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;

輸出如下：

img

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

補充：

組合不同的表：

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

相關文章