UNION、INTERSECT 和 EXCEPT - Amazon Redshift

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

UNION、INTERSECT 和 EXCEPT

UNION、INTERSECT 和 EXCEPT 集合運算子可用來比較和合併兩種不同查詢表達式的結果。例如,如果您想知道哪些網站使用者同時是買方和賣家,但其使用者名稱儲存在不同的資料欄或資料表中,您可以找出這兩種類型使用者的交集。如果您想知道哪些網站使用者是買方,但不是賣家,您可以使用 EXCEPT 運算子找出兩份使用者清單之間的差異。如果您想要建構所有使用者的清單,但不考慮角色,您可以使用 UNION 運算子。

Syntax (語法)

query { UNION [ ALL ] | INTERSECT | EXCEPT | MINUS } query

參數

query

此查詢表達式會以其選取清單形式,對應至接在 UNION、INTERSECT 或 EXCEPT 運算子後面的另一個查詢表達式。兩個表達式必須包含採用相容資料類型的相同輸出資料欄數,否則就無法比較和合併這兩個結果集。集合操作不允許在不同類別的資料類型之間進行隱含轉換;如需詳細資訊,請參閱 類型相容性與轉換

您可以建構包含無限查詢表達式數目的查詢,並將它們與 UNION、INTERSECT 和 EXCEPT 運算子的任意組合連結。例如,假設資料表 T1、T2 和 T3 包含相容的資料欄集,則以下查詢結構有效:

select * from t1 union select * from t2 except select * from t3 order by c1;
UNION

此集合操作會從兩個查詢表達式傳回資料列,無論資料列衍生自其中一個或兩個表達式。

INTERSECT

此集合操作會傳回衍生自兩個查詢表達式的資料列。未由兩個表達式傳回的資料列則會遭到捨棄。

EXCEPT | MINUS

此集合操作會傳回衍生自兩個查詢表達式之一的資料列。若要限定結果,資料列必須存在第一個結果資料表中,但不能存在第二個資料表中。MINUS 和 EXCEPT 是一模一樣的同義詞。

ALL

ALL 關鍵字會保留 UNION 所產生的任何重複資料列。未使用 ALL 關鍵字時的預設行為是捨棄這些重複項目。不支援 INTERSECT ALL、EXCEPT ALL 和 MINUS ALL。

集合運算子的評估順序

UNION 和 EXCEPT 集合運算子為左關聯。若未指定括號來影響優先順序,則會從左到右評估這些集合運算子的組合。例如,在下列查詢中,T1 和 T2 的 UNION 會先評估,然後在 UNION 結果上執行 EXCEPT 操作:

select * from t1 union select * from t2 except select * from t3 order by c1;

在相同查詢中使用運算子組合時,INTERSECT 運算子的優先順序高於 UNION 和 EXCEPT 運算子。例如,下列查詢會先評估 T2 和 T3 的交集,再將結果與 T1 進行聯集:

select * from t1 union select * from t2 intersect select * from t3 order by c1;

加入括號就可以強制執行不同的評估順序。在下列案例中,T1 和 T2 的聯集結果會與 T3 交集,而查詢可能會產生不同的結果。

(select * from t1 union select * from t2) intersect (select * from t3) order by c1;

使用須知

  • 集合操作查詢的結果中傳回的資料欄名稱,是來自第一個查詢表達式的資料表中的資料欄名稱 (或別名)。這些資料欄名稱可能會造成誤導,因為資料欄中的值是從任一邊集合運算子的資料表衍生,所以建議您為結果集提供有意義的別名。

  • 先於集合運算子的查詢表達式不應包含 ORDER BY 子句。只有在包含集合運算子的查詢結尾使用 ORDER BY 子句時,該子句才會產生有意義的排序結果。在此情況下,ORDER BY 子句會套用至所有集合操作的最終結果。最外層的查詢也可包含標準 LIMIT 和 OFFSET 子句。

  • 不支援將 LIMIT 和 OFFSET 子句當做限制集合操作的中繼結果所傳回資料列數的方式。例如,下列查詢會傳回錯誤:

    (select listid from listing limit 10) intersect select listid from sales; ERROR: LIMIT may not be used within input to set operations.
  • 當集合運算子查詢傳回小數結果時,對應的結果資料欄就會提升,以傳回相同的精確度和小數位數。例如,在以下查詢中,T1.REVENUE 是 DECIMAL(10,2) 資料欄,而 T2.REVENUE 是 DECIMAL(8,4) 資料欄,小數結果會提升為 DECIMAL(12,4):

    select t1.revenue union select t2.revenue;

    小數位數為 4,因為這是兩個資料欄的小數位數上限。精確度為 12,因為 T1.REVENUE 要求小數點左邊有 8 位數 (12 - 4 = 8)。此類型提升可確保 UNION 兩邊的所有值都能納入結果中。若是 64 位元值,最高結果精確度為 19,而結果小數位數上限為 18。若是 128 位元值,最高結果精確度為 38,而結果小數位數上限為 37。

    如果產生的資料類型超過 Amazon Redshift 精確度和小數位數限制,查詢會傳回錯誤。

  • 在集合操作中,若每個對應資料欄配對的這兩個資料值為等於兩者皆為 NULL,則這兩個資料列會視為相同。例如,若資料表 T1 和 T2 都包含一個資料欄和一個資料列,而該資料列在兩個資料表中都是 NULL,則對這些資料表執行 INTERSECT 操作就會傳回該資料列。